Upload business partner data as .CSV

maximilianong commented 3 months ago

Description

In the portal its already possible to add address and site information manually via the portal user interface.

The plan is to enhance the functionality of this area with an upload functionality via .CSV file.

undefined_2024-06-06_12-12-47

As user I enter the csv upload page via the same page where I can add my company data manually so that everything regarding my company data is managed in the same area.

undefined_2024-06-18_10-16-37

As user I click on the csv upload button and will be redirected to a separate page where I can upload my csv file so that I can see which files I already uploaded or not and I can download the csv template.
As user that is doing the first upload I can download a blank .csv template.
As user I can just upload .csv files (no other file formats should be allowed).
As user I can just upload data with a max. file size of 1mb.
As user I can just one file after the other - I cannot upload multiple data at once.
As a user, I want only authorized users to be able to upload files to ensure that only authorized individuals add files.
As a user, I want to be able to see a preview of my uploaded file to ensure that I have selected the correct file.
As a user, I want to see the progress of my file upload so that I can monitor the status of the upload.
As a user, I want to be assured that my files are transmitted securely and encrypted so that my data is protected.
As user I can see the status of the provided data in the cleaning process so that I know when the data will be available in the network (my company page shows each provided record and its status)
As a user, I want to receive clear error messages if an upload fails (detailed list of errors with row and column information) and have the option to retry the upload.

Dependencies

Portal (@jjeroch)

THIS NEEDS REFINEMENT :D Peer-Group is @jjeroch / @maximilianong with development by @SujitMBRDI / @manojava-gk

Additional information

[x] I'm willing to contribute to this feature with development resources

maximilianong commented 3 months ago

Updates from refinement with @jjeroch :

Scope got defined: non-sharing-member (companies can upload their own company sites and addresses)

[ ] Error handling information will be provided by @jjeroch
[ ] Timeline for endpoint readiness will be provided by @maximilianong / @SujitMBRDI

maximilianong commented 2 months ago

Standard 0074 needs to adjusted / extended with the API. We need to decide is this relevant for 24.12 or to move it to the first standard release in 2025.

jjeroch commented 2 months ago

@maximilianong we need test cases still. Since you are the owner (only one, despite committers, who can change the ticket description) could you please add the test cases of this Epic/Feature?

Unsharm commented 1 month ago

@maximilianong we need test cases still. Since you are the owner (only one, despite committers, who can change the ticket description) could you please add the test cases of this Epic/Feature?

Hello @jjeroch, Here are the detailed test cases for this feature:

Test Case 1: Valid CSV File Upload
Preconditions	Test Steps:	Expected result
Ensure the user is authorised to upload files. Ensure the application is up and running.	Select and Upload File: - Select the file valid_partner_data.csv. Upload the file. - Verify Upload Progress: Monitor the upload progress. Verify Upload Response: - Verify that the file is successfully uploaded and processed without any errors. - The system should return an HTTP status of 200 OK.	1. The file should be successfully uploaded and processed. 2. The user should see the status uploaded successfully.
Test Case 2: Empty CSV File Upload
Preconditions	Test Steps:	Expected Result
Ensure the user is authorized to upload files. Ensure the application is up and running.	Select and Upload File: - Select the file empty_partner_data.csv. - Upload the file. Verify Upload Progress: - Monitor the upload progress. Verify Upload Response: - Verify that the file is rejected. - The system should return an HTTP status of 400 Bad Request with an appropriate error message indicating that the file is empty.	1. The file should be rejected with an appropriate error message.
Test Case 3: Non-CSV File Upload
Preconditions	Test Steps:	Expected Result
Ensure the user is authorized to upload files. Ensure the application is up and running.	Select and Upload File: - Select the file non_csv_partner_data.xls. - Upload the file. Verify Upload Progress: - Monitor the upload progress. Verify Upload Response: - Verify that the file is rejected. - The system should return an HTTP status of 400 Bad Request with an appropriate error message indicating that the file format is not supported. Detailed Error Messages: - Verify that the system provides a detailed error message with row and column information if applicable.	1. The file should be rejected with an appropriate error message.
Test Case 4: Invalid Data CSV File Upload
Preconditions	Test Steps:	Expected Result
Ensure the user is authorized to upload files. Ensure the application is up and running.	Select and Upload File: - Select the file invalid_partner_data.csv. - Upload the file. Verify Upload Progress: - Monitor the upload progress. Verify Upload Response: - Verify that the file is rejected. - The system should return an HTTP status of 400 Bad Request with an appropriate error message indicating that the file contains invalid business partner data. Detailed Error Messages: - Verify that the system provides a detailed list of errors with row and column information.	1. The file should be rejected with a detailed error message.

evegufy commented 1 month ago

Committers Portal Product: @evegufy @oyo

MaximilianHauer commented 1 month ago

create the first two stories for the portal frontend

waiting that @nicoprow provides me a .csv templates that includes only the necessary information that should be uploaded.

SujitMBRDI commented 1 month ago

Hello @MaximilianHauer,

We have already created api to download .csv template, here is the swagger link for same.

I have also given small walkthrough to @manojava-gk, related to api functionalities on how is the behaviour for success and failure scenarios.

MaximilianHauer commented 1 month ago

@SujitMBRDI in this feature we focus only on uploading address/site information therefor from user-experience it does not make sense to have the need to provide information that is not part of address/site.

i proposed to really reduce the csv to the variables we need to fulfill the task and this is what i meant with "prove a .csv template that includes only the necessary information " ;) furternmore it should be clear what mandatory informations are and additional information

maximilianong commented 1 month ago

Hi @MaximilianHauer I believe the purpose of this feature is to allow users to easily provide their site and address data in a format closely aligned with the official BPDM data model via CSV, especially if they’re not ready to become a sharing member. This data should also undergo the golden record process. Therefore, I wouldn’t limit it to just the fields shown in the portal UI, as those might only be relevant for the portal, while BPDM and the golden record process include more fields.

However, we can consider reducing some fields, such as name parts, and simplifying the available options. That could be an optimization we can implement.

For a more detailed discussion we could also set up a meeting.

MaximilianHauer commented 1 month ago

As aligned in the meeting

BPDM will try to reduce the amount of columns in the .csv file to a reasonable amount
BPDM will verify if the externalID is a possible information that can be provided in the error message to provide a better user-experience
Portal will continue with the implementation as is.

SujitMBRDI commented 3 weeks ago

Changes from BPDM side

.csv template with reduced columns partner-upload-template.csv

External Id details added on error message.

{
  "timestamp": "2024-09-03T06:37:27.329736Z",
  "status": "BAD_REQUEST",
  "error": [
      "Row - 2, External ID - external-1 has error: Invalid address type. Only 'SiteMainAddress' and 'AdditionalAddress' are allowed.",
      "Row - 3 has error: Column 'externalId' is missing and can not be empty or blank.",
      "Row - 3, External ID -  has error: Invalid address type. Only 'SiteMainAddress' and 'AdditionalAddress' are allowed.",
      "Row - 4, External ID - external-2 has error: Invalid address type. Only 'SiteMainAddress' and 'AdditionalAddress' are allowed.",
      "Row - 5, External ID - external-2 has error: Column 'externalId' already exists.",
      "Row - 5, External ID - external-2 has error: Invalid address type. Only 'SiteMainAddress' and 'AdditionalAddress' are allowed.",
      "Row - 6, External ID - external-3 has error: Invalid address type. Only 'SiteMainAddress' and 'AdditionalAddress' are allowed.",
      "Row - 7, External ID - external-4 has error: Invalid address type. Only 'SiteMainAddress' and 'AdditionalAddress' are allowed.",
      "Row - 8, External ID - external-5 has error: Invalid address type. Only 'SiteMainAddress' and 'AdditionalAddress' are allowed."
  ],
  "path": "uri=/v6/input/partner-upload-process"
}

MaximilianHauer commented 3 weeks ago

meeting schedules to align with @nicoprow and @maximilianong how to proceed after the upload. current state: after the upload the status of the dataset stays as "sharingStateType": "Initial", und would need another get call to be updated. This does not make sense from Portal perspective but the data should be uploaded->validated -> approved/rejected. The acceptened entries should be then directly set to "sharingStateType": "Success",

eclipse-tractusx / sig-release