A method and system for generating and updating a BOM table atlas in a prototype design research and development

Abstract

This invention discloses a method and system for generating and updating a Bill of Materials (BOM) chart during prototype design and development. The method comprises the following steps: periodically extracting BOM data of main materials and sub-materials from a source database, establishing a correspondence and quantity details table between main materials and sub-materials, using SQL queries and material hierarchy restructuring to convert the data according to the material hierarchy, generating multiple hierarchical conversion tables of main materials and sub-materials, and loading these tables into a target SQL Server database; establishing a database connection in Excel, updating and querying the hierarchical conversion tables, and extracting BOM data at each level and displaying it in multiple worksheets; reconstructing the single-level BOM data displayed in the multiple worksheets according to the hierarchical structure by converting multiple rows to multiple columns, and finally summarizing them into a single multi-level BOM chart; periodically extracting material information at each level from the multi-level BOM chart, automatically generating node relationship data through Kettle's conversion and job scheduling functions, and periodically updating it to a Neo4j database; and using a D3 force-directed graph to graphically display the multi-level BOM chart based on the node relationship information in the Neo4j graph database. This invention improves the automation and visualization of BOM data processing.

Description

Technical Field

[0001] This invention relates to the fields of data management and visualization, and in particular to a method for generating and updating a BOM (Bill of Materials) chart in prototype design and development. Background Technology

[0002] In the process of prototype design and development, the Bill of Materials (BOM) is a key data management element. It details all the components required for product manufacturing and their quantities. Therefore, the management and updating of the BOM is crucial to ensuring design accuracy and development efficiency.

[0003] However, traditional BOM management systems mostly present information in tabular form, making it difficult to intuitively display product structure and component relationships, thus impacting design and development efficiency. They lack graphical visualization capabilities, hindering the visual representation of product structure and component relationships. BOM data updates rely on manual operations, resulting in complex processes, a lack of automation support, and a tendency for data inconsistencies. In large-scale data environments, the performance and scalability of the BOM system may become bottlenecks.

[0004] To address the lack of graphical representation in tabular-based BOM management systems, patent document CN116450833A, "A Complex Equipment Knowledge Graph Construction System," discloses a management service for a product logical structure tree. Each node in the structure tree corresponds to the product's BOM system, involving the hierarchical relationships between components and parts. Based on the instance BOM relationships, the system displays the connections between components in a graph database format, supporting dynamic interactions on the interface, such as adjusting the display scale and node icons. An interactive interface is provided, allowing users to view detailed information for each node, such as system knowledge cards, models, design, and manufacturing information.

[0005] However, it still has problems and shortcomings, mainly in the following aspects: Although graph databases provide a way to dynamically display hierarchical relationships, deficiencies in user interface and interaction design may limit their practicality. Reliance on static data structures may not be flexible enough in handling dynamically changing product design and development processes. Data silos and insufficient integration between information systems can lead to data inconsistencies, affecting decision-making and operational efficiency. The need for efficient algorithms to handle complex queries and real-time data updates is not fully realized.

[0006] Therefore, there is an urgent need for a BOM (Bill of Materials) that can simultaneously provide graphical display and automatic data updates in order to improve the efficiency of prototype design and development. Summary of the Invention

[0007] The technical problem to be solved by this invention is how to simultaneously realize the graphical display of the BOM and automatic data updates, thereby improving the efficiency of prototype design and development.

[0008] To solve the above-mentioned technical problems, the present invention provides the following technical solution: a method for generating and updating a BOM (Bill of Materials) chart in prototype design and development, comprising the following steps:

[0009] S1: Periodically extract BOM data of main materials and sub-materials from the source database, establish the correspondence between main materials and sub-materials and quantity details tables, use SQL queries and material hierarchy structure reorganization to transform the data according to the material hierarchy relationship, generate multiple hierarchical transformation tables of main materials and sub-materials, and automatically and periodically load these transformed hierarchical transformation tables into the target SQL Server database.

[0010] S2: By establishing a database connection in Excel, the hierarchical transformation table in the target SQL Server database is automatically updated and queried in Excel. The data of each level of BOM is extracted from the hierarchical transformation table and displayed in multiple worksheets. The data displayed in the worksheets are the data of each level of BOM, and each worksheet displays the BOM data of a single level.

[0011] S3: By reconstructing the single-level BOM data displayed in multiple worksheets according to the hierarchical structure by converting multiple rows into multiple columns, and finally summarizing them into the same worksheet, the summarized worksheet is called a multi-level BOM table, which displays complete multi-level BOM information.

[0012] S4: Periodically extract material information of each level from the multi-level BOM table, automatically generate node relationship data through Kettle's conversion and job scheduling functions, and periodically update it to the Neo4j database to build a relationship graph between materials;

[0013] S5: Based on the node relationship information in the Neo4j graph database, use the D3 force-directed graph to realize the graphical display of the multi-level BOM table.

[0014] Preferably, step S1 is executed using the open-source ETL tool Kettle, and step S2 is executed using ADO technology and VBA timer functionality integrated into Excel.

[0015] Preferably, in step S3, the process of extracting and integrating multiple worksheets into a multi-level BOM table specifically includes: combining the ADO technology and VBA timing function integrated in Excel, using the multiple worksheets containing the main materials and sub-materials as source data tables, and using nested loops to traverse the data; the outer loop processes the main-sub-material relationship correspondence table of the source data table, and the inner loop processes the finished product material information table; using conditional judgment to check the data matching situation; after data matching and calculation, the calculation result of the Cell method is written to a specific cell; the Rows.Insert method is used to dynamically insert new rows and, based on the matching results, the data at each level will continue to be processed and inserted into the target data table based on the previous level, ultimately forming a complete multi-level BOM table.

[0016] Preferably, in step S4, an index is also created for the key fields.

[0017] Preferably, in step S5, visualizing the multi-level BOM table includes the following process:

[0018] Use SVG elements as containers for visualization; defs and marker define the shape of the arrow used at the end of the connecting line.

[0019] The simulation using force-directed graphs is as follows: Using simulation, the physical rules of nodes and connecting lines are defined. Through the combined effects of the connecting line force, repulsive force, centering force, and speed reduction force between two nodes u and v, the nodes and connecting lines are automatically arranged in the graph to form an organic and visual BOM structure.

[0020] The data loading and binding process is as follows: d3.json is used to asynchronously load external JSON format graph data; Links and Nodes are used to bind data to SVG elements, creating connection lines and nodes; the line element represents the connection between nodes; the circle element represents each node; and the color is set through a mapping function to display different types of materials.

[0021] Using Drag, nodes can be dragged, and Window Resize is used to automatically adjust the SVG size and center of force when the window size changes, keeping the graphics fit the screen to enable interactive functionality;

[0022] Using the ticked function, a method is provided for updating the positions of nodes and connectors in the dynamic layout of force-directed graphs to achieve dynamic updates.

[0023] Preferably, the formula for the connecting line force between the two nodes u and v is as follows:

[0024]

[0025] Among them, F link (u,v) represents the gravitational force between nodes u and v, k link is a constant representing the force on the connecting line, used to control the strength of the force; distance(u,v) is the actual distance between nodes u and v; and lo is the ideal length of the connecting line, i.e., the distance between the nodes that is desired to be maintained. Let be the direction vector from node u to node v.

[0026] Preferably, the formula for the force between the two nodes u and v operates as follows: when the actual distance between nodes u and v is greater than the ideal length lo, the formula generates an attractive force that pulls the nodes closer; when the actual distance between nodes u and v is less than the ideal length lo, the formula generates a repulsive force that pushes the nodes further apart; when the actual distance between nodes u and v is equal to the ideal length lo, the force is zero, and the distance between the nodes is exactly the desired ideal length.

[0027] Preferably, the formula for the repulsive force between the two nodes u and v is as follows:

[0028]

[0029] Among them, F repel (u,v) represents the repulsive force between nodes u and v, k repel Let q be the repulsive force constant. u and q v Let u be the charge of nodes u and v, and distance(u,v) be the actual distance between nodes u and v. Let be the direction vector from node u to node v.

[0030] Preferably, the formula for the centering force between the two nodes u and v is as follows:

[0031] F center (u)=k center ×(center-position(u)),

[0032] Among them, F center (u) represents the centering force acting on node u, directed from the node's current position towards the center of the graph, k center The centering force constant is used to adjust the magnitude of the force. Increasing center can strengthen the tendency of nodes to cluster towards the center. center is the center position of the graph. This position is set as the geometric center of the graph, or it can be set as a reference point according to specific needs. position(u) is the current position of node u.

[0033] This invention also provides a system for generating and updating a BOM (Bill of Materials) chart in prototype design and development, characterized by comprising the following modules:

[0034] Kettle ETL Job Scheduling Module: Used to periodically extract BOM data of main materials and sub-materials from the source database, establish the correspondence between main materials and sub-materials and quantity details, use SQL queries and material hierarchy structure reorganization to transform the data according to the material hierarchy relationship, generate multiple hierarchical transformation tables of main materials and sub-materials, and automatically and periodically load these transformed hierarchical transformation tables into the target SQL Server database.

[0035] Excel Automated Data Update Module: This module is used to automatically update and query the hierarchical transformation table in the target SQL Server database by establishing a database connection in Excel. It also extracts BOM data from the hierarchical transformation table and displays it in multiple worksheets. The data displayed in each worksheet is the BOM data for each level, and each worksheet displays BOM data for a single level.

[0036] Excel worksheet data processing and summarization module: This module is used to reconstruct the single-level BOM data displayed in multiple worksheets according to the hierarchical structure by converting multiple rows into multiple columns, and finally summarizing them into a single worksheet. This summarized worksheet is called a multi-level BOM table, which displays complete multi-level BOM information.

[0037] The Kettle and Neo4j integration module is used to extract material information of each level from the multi-level BOM table at regular intervals. Through Kettle's conversion and job scheduling functions, it automatically generates node relationship data and updates it to the Neo4j database regularly to build a relationship graph between materials.

[0038] Neo4j Data Visualization Module: Used to graphically display the multi-level BOM table using D3 force-directed graphs based on the node relationship information in the Neo4j graph database.

[0039] The advantages of this invention are:

[0040] (1) It fills the gap in the existing technology, where traditional BOM management systems mostly use tables to display product structure and component relationships in a way that is difficult to intuitively show. This invention uses D3 force-directed diagrams to intuitively display BOM data in the form of nodes and connecting lines, avoiding the situation of cross-connection and closure.

[0041] (2) By using VBA programming and scheduled tasks, the automatic refresh and update of BOM table data is realized, ensuring data consistency and accuracy, and greatly improving the accuracy of data in the prototype development and design process.

[0042] In summary, this invention provides a method for generating and updating a Bill of Materials (BOM) chart during prototype design and development. The graphical visualization of the BOM chart can directly display the hierarchical relationship of materials in a visual form, greatly improving the understandability of the product structure. The automated data update process ensures the consistency of data used by various departments and regions, reducing data inconsistencies caused by human error or delayed updates, thus guaranteeing data consistency. Automation also reduces the reliance on manual input, lowering the possibility of human error and ensuring data accuracy. Attached Figure Description

[0043] Figure 1 A flowchart illustrating a method for generating and updating a Bill of Materials (BOM) table during prototype design and development, provided as an embodiment of the present invention;

[0044] Figure 2 This is a diagram illustrating how the system creates indexes for key fields in an embodiment of the present invention.

[0045] Figure 3 This is the BOM tree displayed graphically in an embodiment of the present invention. Detailed Implementation

[0046] To make the objectives, technical solutions, and advantages of the embodiments of the present invention clearer, the technical solutions of the embodiments of the present invention will be clearly and completely described below in conjunction with the embodiments of the present invention. Obviously, the described embodiments are only some embodiments of the present invention, not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of the present invention.

[0047] Example 1

[0048] See Figure 1 The diagram shown is a flowchart of a method for generating and updating a Bill of Materials (BOM) table during prototype design and development, provided in this embodiment. The method includes the following steps:

[0049] S1 (Kettle ETL Job Scheduling): Utilizing the open-source ETL tool Kettle, the system leverages Kettle's powerful data extraction, transformation, and loading capabilities. Through a connection to source data such as a PLM database, the system periodically extracts BOM data for main and sub-materials from the PLM database. It establishes the correspondence and quantity details between main and sub-materials in the PLM database. Using SQL queries and material hierarchy restructuring, the data is transformed according to the material hierarchy, generating multiple hierarchical transformation tables for main and sub-materials. Finally, through Kettle's job scheduling function, these transformed hierarchical transformation tables are automatically and periodically loaded into the target SQL Server database. This automated data update process significantly improves data processing efficiency, reduces errors that may occur due to manual operations, and ensures data consistency and timeliness.

[0050] S2 (Excel Automated Data Update): By establishing a database connection in Excel and combining Excel's integrated ADO (ActiveX Data Objects) technology with VBA scheduling functionality, it automatically updates and queries the hierarchical transformation table in the target SQL Server database. It then extracts BOM (Bill of Materials) data from the transformation table and displays it in multiple worksheets. Each worksheet displays BOM data for a single level. Users are spared manual refresh operations; data is automatically imported into different Excel worksheets, improving data processing efficiency. The VBA scheduled task supports custom update intervals, ensuring efficient and timely data updates while reducing human error.

[0051] S3 (Excel Worksheet Data Processing and Summarization): Using VBA, Excel can automatically extract, process, and integrate data from the worksheets. By reconstructing the single-level BOM data displayed in multiple worksheets according to their hierarchical structure, converting multiple rows into multiple columns, and finally summarizing them into a single worksheet, this summarized worksheet is called a multi-level BOM table. It displays complete multi-level BOM information, further enhancing the operability and visualization of the data.

[0052] The process of extracting and integrating multiple worksheets into a multi-level BOM table specifically includes: using the multiple worksheets containing the main materials and sub-materials as source data tables, and traversing the data using nested loops; the outer loop processes the main-sub-material relationship table of the source data table, and the inner loop processes the finished product material information table; conditional judgments are used to check data matching; after data matching and calculation, the Cell method calculation results are written to specific cells; the Rows.Insert method is used to dynamically insert new rows, and based on the matching results, the data at each level is further processed and inserted into the target data table based on the previous level. Finally, a complete multi-level BOM table is formed.

[0053] S4 (Kettle and Neo4j Integration): Integrates the Neo4j graph database into Kettle, periodically extracting material information at each level from the multi-level BOM table. Through Kettle's transformation and job scheduling functions, it automatically generates node relationship data and periodically updates it to the Neo4j database, constructing a relationship graph between materials to support subsequent data analysis and visualization. The system not only generates node relationship data in the graph database but also creates indexes for key fields, such as... Figure 2 As shown, this significantly improves query efficiency and data access speed. A regular update mechanism ensures that data in the Neo4j graph database remains synchronized, supporting efficient graph queries and fast data retrieval.

[0054] S5 (Neo4j Data Visualization): Based on the node relationship information in the Neo4j graph database, D3.js is used to implement a graphical display of the multi-level BOM table. A force-directed graph is used to show the hierarchical relationships between materials, allowing users to more intuitively understand the material structure and relationships, enhancing the visibility and operability of data analysis. The display diagram is as follows: Figure 3 As shown. The specific operation process of step S5 is as follows:

[0055] S5-1: First, use SVG elements as a visual container, whose size automatically adapts to the browser window, and use defs and marker to define the shape of the arrow at the end of the connecting line to indicate directionality.

[0056] S5-2: Based on the node relationship information in the Neo4j graph database from step S4, a force-directed graph simulation is used. Specifically, the physical rules of nodes and connecting lines are defined using Simulation, and the Link force is used to maintain the length of the connecting line. The formula for the connecting line force between two nodes u and v is:

[0057]

[0058] Among them, F limk (u,v) represents the gravitational force between nodes u and v, k linkis a constant representing the force on the connecting line, used to control the strength of the force; distance(u,v) is the actual distance between nodes u and v; and lo is the ideal length of the connecting line, i.e., the distance between the nodes that is desired to be maintained. Let be the direction vector from node u to node v;

[0059] The above formula works as follows: when the actual distance between nodes u and v is greater than the ideal length lo, the formula generates an attractive force that pulls the nodes closer; when the actual distance between nodes u and v is less than the ideal length lo, the formula generates a repulsive force that pushes the nodes further apart; when the actual distance between nodes u and v is equal to the ideal length lo, the force is zero, and the distance between the nodes is exactly the desired ideal length.

[0060] Using Charge, i.e., the repulsive force between nodes, to prevent nodes from clustering too much, the formula for the repulsive force between two nodes u and v is:

[0061]

[0062] Among them, F repel (u,v) represents the repulsive force between nodes u and v, k repel Let q be the repulsive force constant. u and q v Let u be the charge of nodes u and v, and distance(u,v) be the actual distance between nodes u and v. Let be the direction vector from node u to node v;

[0063] Using the Center force, all nodes are pulled towards the center of the SVG to ensure the graphic is displayed in the center. The formula for the center force between two nodes u and v is:

[0064] F center (u)=k center ×(center-position(u)),

[0065] Among them, F center (u) represents the centering force acting on node u, directed from the node's current position towards the center of the graph. k center The centering force constant is used to adjust the magnitude of the force. Increasing `center` strengthens the tendency of nodes to cluster towards the center. `center` is the center position of the graph. This position can usually be set as the geometric center of the graph, or as a reference point according to specific needs. `position(u)` is the current position of node `u`.

[0066] S5-3: Asynchronously load external JSON format graph data (nodes and connecting lines) using d3.json, bind the data to SVG elements using Linksand Nodes, create connecting lines and nodes, use line elements to represent connections between nodes, use circle elements to represent individual nodes, set colors through a mapping function, and display different types of materials to achieve data loading and binding;

[0067] S5-4: Use Drag to make nodes draggable, increasing user interactivity; use Window Resize to automatically adjust the SVG size and center of force when the window size changes, keeping the graphics fit the screen to achieve interactive functionality.

[0068] S5-5: Using the Ticked function to provide a method for updating node and connector positions for dynamic layout of force-directed graphs.

[0069] Repeat steps S1-S5 above to achieve graphical visualization of the EXCEL multi-level BOM table.

[0070] In this embodiment, a suitable index is created for the data in a large-scale data environment to speed up the query. In a big data environment, various indexing methods such as inverted indexes and spatial indexes can be used to improve the performance of complex queries.

[0071] This embodiment automates the data update process, ensuring data consistency and accuracy and reducing errors caused by human operation. By adopting distributed databases and real-time synchronization technology, it enables efficient cross-departmental and cross-regional collaboration, ensuring data consistency and real-time performance.

[0072] This embodiment also introduces a graphical display effect, showing the bill of materials at various levels required for prototype development in the form of a graphical BOM tree. When a product is selected, the bill of materials of each node to which the product belongs will automatically expand without having to click on nodes one by one. There is no closed loop between nodes, thus realizing the visualization of multi-level BOM tables.

[0073] Example 2

[0074] Corresponding to Embodiment 1 of the present invention, Embodiment 2 of the present invention also provides a system for generating and updating a BOM (Bill of Materials) chart in prototype design and development, characterized in that it includes the following modules:

[0075] The Kettle ETL job scheduling module utilizes the open-source ETL tool Kettle. Leveraging Kettle's powerful data extraction, transformation, and loading capabilities, the system connects to source data such as a PLM database. It periodically extracts BOM data for main and sub-materials from the PLM database, establishing a correspondence and quantity details table between main and sub-materials. Using SQL queries and material hierarchy restructuring, the data is transformed according to the material hierarchy, generating multiple hierarchical transformation tables for main and sub-materials. Finally, through Kettle's job scheduling function, these transformed hierarchical tables are automatically and periodically loaded into the target SQL Server database. This automated data update process significantly improves data processing efficiency, reduces errors that may occur due to manual operations, and ensures data consistency and timeliness.

[0076] The Excel automated data update module establishes a database connection in Excel and utilizes Excel's integrated ADO (ActiveX Data Objects) technology and VBA scheduling functionality to automatically update and query the hierarchical transformation table in the target SQL Server database. It then extracts BOM (Bill of Materials) data from the transformation table and displays it across multiple worksheets. Each worksheet displays BOM data for a single level. Users are spared manual refresh operations, as data is automatically imported into different Excel worksheets, improving data processing efficiency. The VBA scheduled task supports custom update intervals, ensuring efficient and timely data updates while minimizing human error.

[0077] The Excel worksheet data processing and summarization module utilizes VBA to automatically extract, process, and integrate data from multiple worksheets. By reconstructing the single-level BOM data displayed in multiple worksheets according to their hierarchical structure, it converts rows into columns and ultimately summarizes the data into a single worksheet. This summarized worksheet, called the multi-level BOM table, displays complete multi-level BOM information, further enhancing data operability and visualization.

[0078] The process of extracting and integrating multiple worksheets into a multi-level BOM table specifically includes: using the multiple worksheets containing the main materials and sub-materials as source data tables, and traversing the data using nested loops; the outer loop processes the main-sub-material relationship table of the source data table, and the inner loop processes the finished product material information table; conditional judgments are used to check data matching; after data matching and calculation, the Cell method calculation results are written to specific cells; the Rows.Insert method is used to dynamically insert new rows, and based on the matching results, the data at each level is further processed and inserted into the target data table based on the previous level. Finally, a complete multi-level BOM table is formed.

[0079] The Kettle and Neo4j integration module integrates the Neo4j graph database into Kettle, periodically extracting material information from the multi-level BOM table. Through Kettle's transformation and job scheduling functions, it automatically generates node relationship data and periodically updates it to the Neo4j database, constructing a relationship graph between materials to support subsequent data analysis and visualization. The system not only generates node relationship data in the graph database but also creates indexes for key fields, such as... Figure 2 As shown, this significantly improves query efficiency and data access speed. A regular update mechanism ensures that data in the Neo4j graph database remains synchronized, supporting efficient graph queries and fast data retrieval.

[0080] The Neo4j data visualization module uses D3.js to graphically display multi-level BOM tables based on node relationship information in the Neo4j graph database. It uses force-directed graphs to show the hierarchical relationships between materials, allowing users to more intuitively understand material structure and relationships, enhancing the visibility and operability of data analysis. The display diagram is shown below. Figure 3 As shown. The Neo4j data visualization module includes the following units:

[0081] A visual container unit that uses SVG elements as a visual container, whose size automatically adapts to the browser window, and uses defs and markers to define the shape of the arrow at the end of the connecting line to indicate directionality;

[0082] The force-directed graph simulation unit is used to simulate force-directed graphs based on node relationship information from the Neo4j graph database obtained from the Kettle and Neo4j integration module. Specifically, it uses Simulation to define the physical rules of nodes and connecting lines, and uses Link, i.e., the force of the connecting line, to maintain the length of the connecting line. The formula for the connecting line force between two nodes u and v is:

[0083]

[0084] Among them, F link(u,v) represents the gravitational force between nodes u and v, k link is a constant representing the force on the connecting line, used to control the strength of the force; distance(u,v) is the actual distance between nodes u and v; and lo is the ideal length of the connecting line, i.e., the distance between the nodes that is desired to be maintained. Let be the direction vector from node u to node v;

[0085] The above formula works as follows: when the actual distance between nodes u and v is greater than the ideal length lo, the formula generates an attractive force that pulls the nodes closer; when the actual distance between nodes u and v is less than the ideal length lo, the formula generates a repulsive force that pushes the nodes further apart; when the actual distance between nodes u and v is equal to the ideal length lo, the force is zero, and the distance between the nodes is exactly the desired ideal length.

[0086] Using Charge, i.e., the repulsive force between nodes, to prevent nodes from clustering too much, the formula for the repulsive force between two nodes u and v is:

[0087]

[0088] Among them, F repel (u,v) represents the repulsive force between nodes u and v, k repel Let q be the repulsive force constant. u and q v Let u be the charge of nodes u and v, and distance(u,v) be the actual distance between nodes u and v. Let be the direction vector from node u to node v;

[0089] Using the Center force, all nodes are pulled towards the center of the SVG to ensure the graphic is displayed in the center. The formula for the center force between two nodes u and v is as follows:

[0090] F center (u)=k center ×(center-position(u)),

[0091] Among them, F center (u) represents the centering force acting on node u, directed from the node's current position towards the center of the graph, k center The centering force constant is used to adjust the magnitude of the force. Increasing center can strengthen the tendency of nodes to cluster towards the center. center is the center position of the graph. This position is set as the geometric center of the graph, or it can be set as a reference point according to specific needs. position(u) is the current position of node u.

[0092] Data Loading and Binding Unit: Used to asynchronously load external JSON format graph data (nodes and connecting lines) using d3.json, bind data to SVG elements using Links and Nodes, create connecting lines and nodes, use line elements to represent connections between nodes, use circle elements to represent individual nodes, set colors through a mapping function, and display different types of materials to achieve data loading and binding;

[0093] The graphics adaptation unit is used to make nodes draggable by using Drag, increasing user interactivity, and to automatically adjust the SVG size and center of force when the window size changes by using WindowResize, so as to keep the graphics adapted to the screen and realize interactive functions.

[0094] The update unit is used to provide a method for updating the positions of nodes and connectors for the dynamic layout of the force-directed graph using the Ticked function, so as to achieve dynamic updates.

[0095] This embodiment, through the operation and cooperation of the above five modules, achieves an intuitive display of multi-level BOM tables: a graphical BOM tree directly visually displays the hierarchical relationships of materials, greatly improving the comprehensibility of the product structure. This intuitive display method helps to quickly identify errors or areas for improvement in the design; it also automatically expands the display, meaning that after selecting a product, the related material structure automatically expands without manually clicking each node. This function reduces operational complexity and improves user efficiency; it also prevents node closure, keeping all nodes open during interaction, avoiding repetitive operations and enhancing the user experience.

[0096] The automated data update process in this embodiment ensures data consistency across departments and regions, reduces data inconsistencies caused by human error or delayed updates, and automates the process by reducing reliance on manual input, thus lowering the possibility of human error and ensuring data accuracy.

[0097] This embodiment employs distributed databases and real-time synchronization technology to achieve cross-departmental and cross-regional data sharing and collaborative work, ensuring data real-time performance and consistency, and improving information flow and collaboration efficiency among teams, especially in large-scale projects involving multiple locations and teams.

[0098] In summary, the graphical and automated features of this invention significantly reduce the time from design to production, while improving design accuracy and production efficiency; the systematic design allows for flexible responses to constantly changing business needs and technological upgrades, ensuring long-term effective support for enterprise development; by providing an efficient, intuitive, and easy-to-use system, it enhances user experience and improves the enterprise's market competitiveness; and it provides users with an efficient, accurate, and easy-to-manage BOM management solution.

[0099] The above embodiments are only used to illustrate the technical solutions of the present invention, and are not intended to limit it. Although the present invention has been described in detail with reference to the foregoing embodiments, those skilled in the art should understand that modifications can still be made to the technical solutions described in the foregoing embodiments, or equivalent substitutions can be made to some of the technical features. Such modifications or substitutions do not cause the essence of the corresponding technical solutions to deviate from the spirit and scope of the technical solutions of the embodiments of the present invention.

Claims

1. A method for generating and updating a Bill of Materials (BOM) chart during prototype design and development, characterized in that: Includes the following steps: S1: Periodically extract BOM data of main materials and sub-materials from the source database, establish the correspondence between main materials and sub-materials and quantity details tables, use SQL queries and material hierarchy structure reorganization to transform the data according to the material hierarchy relationship, generate multiple hierarchical transformation tables of main materials and sub-materials, and automatically and periodically load these transformed hierarchical transformation tables into the target SQL Server database. S2: By establishing a database connection in Excel, the hierarchical transformation table in the target SQL Server database is automatically updated and queried in Excel. The data of each level of BOM is extracted from the hierarchical transformation table and displayed in multiple worksheets. The data displayed in the worksheets are the data of each level of BOM, and each worksheet displays the BOM data of a single level. S3: By reconstructing the single-level BOM data displayed in multiple worksheets according to the hierarchical structure by converting multiple rows into multiple columns, and finally summarizing them into the same worksheet, the summarized worksheet is called a multi-level BOM table, which displays complete multi-level BOM information. S4: Periodically extract material information of each level from the multi-level BOM table, automatically generate node relationship data through Kettle's conversion and job scheduling functions, and periodically update it to the Neo4j database to build a relationship graph between materials; S5: Based on the node relationship information in the Neo4j graph database, use the D3 force-directed graph to realize the graphical display of the multi-level BOM table.

2. The method for generating and updating a BOM (Bill of Materials) chart in prototype design and development according to claim 1, characterized in that: Step S1 is executed using the open-source ETL tool Kettle, and step S2 is executed using ADO technology and VBA timer functionality integrated into Excel.

3. The method for generating and updating a BOM (Bill of Materials) chart in prototype design and development according to claim 1, characterized in that: In step S3, the process of extracting and integrating multiple worksheets into a multi-level BOM table specifically includes: using ADO technology and VBA timing functions integrated in Excel, the multiple worksheets containing the main materials and sub-materials are used as source data tables, and nested loops are used to traverse the data; the outer loop processes the main-sub-material relationship table of the source data table, and the inner loop processes the finished product material information table; conditional judgments are used to check the data matching; after data matching and calculation, the calculation results of the Cell method are written to specific cells, and the Rows.Insert method is used to dynamically insert new rows and, based on the matching results, the data at each level will continue to be processed and inserted into the target data table based on the previous level, ultimately forming a complete multi-level BOM table.

4. The method for generating and updating a BOM (Bill of Materials) chart in prototype design and development according to claim 1, characterized in that: In step S4, an index is also created for the key fields.

5. The method for generating and updating a BOM (Bill of Materials) chart in prototype design and development according to claim 1, characterized in that: In step S5, the visualization of the multi-level BOM table includes the following process: Use SVG elements as containers for visualization; defs and marker define the shape of the arrow used at the end of the connecting line. The simulation using force-directed graphs is as follows: Using simulation, the physical rules of nodes and connecting lines are defined. Through the combined effects of the connecting line force, repulsive force, centering force, and speed reduction force between two nodes u and v, the nodes and connecting lines are automatically arranged in the graph to form an organic and visual BOM structure. The data loading and binding process is as follows: d3.json is used to asynchronously load external JSON format graph data; Linksand Nodes is used to bind data to SVG elements, creating connection lines and nodes; the line element represents the connection between nodes; the circle element represents each node; and the color is set through a mapping function to display different types of materials. Using Drag, nodes can be dragged, and Window Resize is used to automatically adjust the SVG size and center of force when the window size changes, keeping the graphics fit the screen to enable interactive functionality; Using the ticked function, a method is provided for updating the positions of nodes and connectors in the dynamic layout of force-directed graphs to achieve dynamic updates.

6. The method for generating and updating a BOM (Bill of Materials) chart in prototype design and development according to claim 5, characterized in that: The formula for the connecting line force between the two nodes u and v is as follows: Among them, F link (u,v) represents the gravitational force between nodes u and v, k link is a constant representing the force on the connecting line, used to control the strength of the force; distance(u,v) is the actual distance between nodes u and v; and lo is the ideal length of the connecting line, i.e., the distance between the nodes that is desired to be maintained. Let be the direction vector from node u to node v.

7. The method for generating and updating a BOM (Bill of Materials) chart in prototype design and development according to claim 6, characterized in that: The formula for the force between the two nodes u and v operates as follows: when the actual distance between nodes u and v is greater than the ideal length lo, the formula generates an attractive force that pulls the nodes closer; when the actual distance between nodes u and v is less than the ideal length lo, the formula generates a repulsive force that pushes the nodes further apart; when the actual distance between nodes u and v is equal to the ideal length lo, the force is zero, and the distance between the nodes is exactly the desired ideal length.

8. The method for generating and updating a BOM (Bill of Materials) chart in prototype design and development according to claim 5, characterized in that: The formula for the repulsive force between the two nodes u and v is as follows: Among them, F repel (u,v) represents the repulsive force between nodes u and v, k repel Let q be the repulsive force constant. u and q v Let u be the charge of nodes u and v, and distance(u,v) be the actual distance between nodes u and v. Let be the direction vector from node u to node v.

9. The method for generating and updating a BOM (Bill of Materials) chart in prototype design and development according to claim 5, characterized in that: The formula for the centering force between the two nodes u and v is as follows: F center (u)=k center ×(center-position(u)), Among them, F center (u) represents the centering force acting on node u, directed from the node's current position towards the center of the graph, k center The centering force constant is used to adjust the magnitude of the force. Increasing center can strengthen the tendency of nodes to cluster towards the center. center is the center position of the graph. This position is set as the geometric center of the graph, or it can be set as a reference point according to specific needs. position(u) is the current position of node u.

10. A system for generating and updating a BOM (Bill of Materials) chart during prototype design and development, characterized in that: Includes the following modules: Kettle ETL Job Scheduling Module: Used to periodically extract BOM data of main materials and sub-materials from the source database, establish the correspondence between main materials and sub-materials and quantity details, use SQL queries and material hierarchy structure reorganization to transform the data according to the material hierarchy relationship, generate multiple hierarchical transformation tables of main materials and sub-materials, and automatically and periodically load these transformed hierarchical transformation tables into the target SQL Server database. Excel Automated Data Update Module: This module is used to automatically update and query the hierarchical transformation table in the target SQL Server database by establishing a database connection in Excel. It also extracts BOM data from the hierarchical transformation table and displays it in multiple worksheets. The data displayed in each worksheet is the BOM data for each level, and each worksheet displays BOM data for a single level. Excel worksheet data processing and summarization module: This module is used to reconstruct the single-level BOM data displayed in multiple worksheets according to the hierarchical structure by converting multiple rows into multiple columns, and finally summarizing them into a single worksheet. This summarized worksheet is called a multi-level BOM table, which displays complete multi-level BOM information. The Kettle and Neo4j integration module is used to extract material information of each level from the multi-level BOM table at regular intervals. Through Kettle's conversion and job scheduling functions, it automatically generates node relationship data and updates it to the Neo4j database regularly to build a relationship graph between materials. Neo4j Data Visualization Module: Used to graphically display the multi-level BOM table using D3 force-directed graphs based on the node relationship information in the Neo4j graph database.

Images