Method and system for generating and updating atlas of BOM table in prototype design and research and development

Abstract

The invention discloses a method and a system for generating and updating an atlas of a BOM table in prototype design and research and development, and the method comprises the following steps: regularly extracting BOM data of a main material and a sub-material from a source database, establishing a corresponding relation and a quantity detail table of the main material and the sub-material, and obtaining an atlas of the main material and the sub-material by adopting SQL query and recombination of a material hierarchical structure; converting the data according to the hierarchical relationship of the materials, generating a plurality of hierarchical conversion tables of the main materials and the sub-materials, and loading the tables into a target SQL Server database; database connection is established in Excel, a hierarchy conversion table is updated and inquired, and data of each hierarchy of BOM is extracted and put into a plurality of worktables to be displayed respectively; performing multi-row-to-multi-column reconstruction on the single-level BOM data respectively displayed in the plurality of worktables according to a hierarchical structure, and finally summarizing the single-level BOM data into the same multi-level BOM table; material information of each level is extracted from the multi-level BOM table at regular time, node relation data is automatically generated through conversion and job scheduling functions of Kettle, and the node relation data is updated to a Neo4j database at regular time; and according to the node relation information in the Neo4j graph database, using a D3 force oriented graph to realize the graphical display of the multi-level BOM table. According to the method, the automation degree and the visualization effect of BOM data processing are improved.

Description

Technical Field

[0001] The present invention relates to the field of data management and visual display, and in particular to a method for generating and updating a BOM table in prototype design and development. Background Art

[0002] In the process of prototype design and R&D, the bill of materials (BOM) is a key data management element. It lists in detail all the components and their quantities required for product manufacturing. Therefore, the management and update of the BOM is essential to ensure design accuracy and R&D efficiency.

[0003] However, traditional BOM management systems are mostly presented in table form, which makes it difficult to intuitively display product structure and component relationships, affecting the efficiency of design and R&D. The lack of graphical display function makes it difficult to intuitively display product structure and component relationships. BOM data updates rely on manual operations, the data update process is complicated, and there is a lack of automation support, which easily leads to data inconsistency. In a large-scale data environment, the performance and scalability of the BOM system may become a bottleneck.

[0004] In order to solve the problem that the BOM management system lacks graphical display function in the form of tables, patent document CN116450833A: A complex equipment knowledge graph construction system discloses the management service of the product logical structure tree. Each structure tree node corresponds to the BOM system of the product, involving the hierarchical relationship between parts and components. The connection between the components is displayed in the form of a graph database according to the instance BOM relationship, and dynamic interaction on the interface is supported, such as adjusting the display scale, clicking on the node icon, etc. An interactive interface is provided, where users can view detailed information of each node, such as system knowledge cards, models, design and manufacturing information, etc.

[0005] However, it still has problems and shortcomings, mainly manifested 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. Relying on static data structures, this may not be flexible enough in dealing with dynamically changing product design and development processes. Data silos and insufficient integration between information systems may lead to inconsistent data, affecting decision-making and operational efficiency. Processing complex queries and real-time data updates requires efficient algorithms, which are not fully reflected.

[0006] Therefore, there is an urgent need for a BOM table that can simultaneously achieve graphical display and automatic data update to improve the efficiency of prototype design and R&D. Summary of the invention

[0007] The technical problem to be solved by the present invention is how to simultaneously realize the graphical display of the BOM table and the automatic update of data, so as to improve the efficiency of prototype design and research and development.

[0008] In order to solve the above technical problems, the present invention provides the following technical solutions: a method for generating and updating a BOM table in prototype design and development, comprising the following steps:

[0009] S1: Extract the BOM data of main materials and sub-materials from the source database regularly, establish the correspondence between main materials and sub-materials and the quantity list, use SQL query and reorganize the material hierarchy structure, convert the data according to the material hierarchy, generate multiple hierarchy conversion tables of main materials and sub-materials, and automatically load these converted hierarchy conversion tables into the target SQL Server database regularly;

[0010] S2: By establishing a database connection in Excel, the hierarchical conversion table in the target SQL Server database is automatically updated and queried in Excel, and the data of each level of BOM is extracted from the hierarchical conversion table and put into multiple worksheets for display respectively. 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: The single-level BOM data displayed in the multiple worksheets are reconstructed by converting multiple rows into multiple columns according to the hierarchical structure, and finally summarized into the same worksheet. The summarized worksheet is called a multi-level BOM table, which displays complete multi-level BOM information;

[0012] S4: extracting material information of each level from the multi-level BOM table regularly, automatically generating node relationship data through Kettle's conversion and job scheduling functions, and regularly updating it to the Neo4j database to build a relationship map between materials;

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

[0014] Preferably, the step S1 is performed using the open source ETL tool Kettle, and the step S2 is performed using the ADO technology and VBA timing function integrated in Excel.

[0015] Preferably, in step S3, the process of extracting and integrating from the 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 storing the main materials and sub-materials as the source data table, and using nested loops to traverse the data; the outer loop processes the source data table main-sub-material relationship correspondence table, and the inner loop processes the finished material information table, and uses conditional judgment to check the data matching. After the data is matched and calculated, the calculation result of the Cell method is written into a specific cell, and the Rows.Insert method is used to dynamically insert new rows and according to the matching results, the data of each level will continue to be processed and inserted into the updated target data table based on the previous level, and finally a complete multi-level BOM table is formed.

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

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

[0018] Use the SVG element as a visual container, defs and marker to define the arrow shape used to connect the ends of the line;

[0019] Using force-directed graph simulation, the specific process is: using Simulation to define the physical rules of nodes and connecting lines, through the joint action of the connecting line force, repulsive force, centering force and speed attenuation between the 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 specific process of data loading and binding is as follows: d3.json is used to asynchronously load external graph data in JSON format, Links and Nodes is used to bind data to SVG elements, create connecting lines and nodes, line elements represent the connection between nodes, circle elements represent each node, and the color is set through a mapping function to display different types of materials;

[0021] Use Drag to drag nodes, and Window Resize to automatically adjust the SVG size and force center when the window size changes, so as to keep the graphics fit the screen and realize interactive functions;

[0022] By using the Ticked function, a method for updating the positions of nodes and connecting lines is provided for the dynamic layout of the force-directed graph 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) is the gravitational force between nodes u and v, k link is the constant of the connecting line force, which is used to control the strength of the force. distance(u,v) is the actual distance between nodes u and v. lo is the ideal connecting line length, that is, the distance between nodes that we want to maintain. is the direction vector from node u to node v.

[0026] Preferably, the operating mechanism of the formula of the connecting line force between the two nodes u and v is: when the actual distance between nodes u and v is greater than the ideal length lo, the formula generates an attractive force to pull 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 to push the nodes away; 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 expected 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) is the repulsive force between nodes u and v, k repel is the repulsive force constant, q u and q v is the charge of nodes u and v, distance(u,v) is the actual distance between nodes u and v, is 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) is the centering force on node u, and its direction is from the current position of the node to the center of the graph, k center The centering force constant is used to adjust the size of the force. Increasing center can strengthen the tendency of nodes to gather toward the center. Center is the center position of the graph. This position is set to the geometric center of the graph, or to a reference point according to specific requirements. Position(u) is the current position of node u.

[0033] The present invention also provides a system for generating and updating a BOM table diagram in prototype design and development, which is characterized by comprising the following modules:

[0034] Kettle ETL job scheduling module: used to extract BOM data of main materials and sub-materials from the source database on a regular basis, establish the correspondence between main materials and sub-materials and the quantity list, use SQL query and reorganization of the material hierarchy structure to convert data according to the material hierarchy, generate multiple hierarchy conversion tables of main materials and sub-materials, and automatically load these converted hierarchy conversion tables into the target SQL Server database on a regular basis;

[0035] Excel automatic data update module: used to automatically update and query the hierarchical conversion table in the target SQL Server database in Excel by establishing a database connection in Excel, and extract the BOM data of each level from the hierarchical conversion table and put it into multiple worksheets for display respectively. 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;

[0036] Excel worksheet data processing and aggregation module: used to reconstruct the single-level BOM data displayed in multiple worksheets into multiple columns according to the hierarchical structure, and finally aggregate them into the same worksheet. The aggregated worksheet is called a multi-level BOM table, which displays complete multi-level BOM information;

[0037] Kettle and Neo4j integration module: used to extract material information of each level from the multi-level BOM table at regular intervals, automatically generate node relationship data through Kettle's conversion and job scheduling functions, and regularly update it to the Neo4j database to build a relationship map between materials;

[0038] Neo4j data visualization module: used to realize the graphical display of the multi-level BOM table using D3 force-directed graph according to the node relationship information in the Neo4j graph database.

[0039] The advantages of the present invention are:

[0040] (1) This invention fills the gap in the prior art that traditional BOM management systems are mostly displayed in table form, which makes it difficult to intuitively display product structure and component relationships. The present invention uses D3 force-directed graphs to intuitively display BOM data in the form of nodes and connecting lines, avoiding the situation of cross-linking and closure of connections;

[0041] (2) Through VBA programming and scheduled tasks, the BOM data can be automatically refreshed and updated to ensure data consistency and accuracy, greatly improving the accuracy of data during prototype development and design.

[0042] In summary, the present invention provides a method for generating and updating a BOM table graph in prototype design and development. The graphic visualization of the BOM table can directly display the hierarchical relationship of materials in a visual form, greatly improving the comprehensibility of the product structure. The automated data update process ensures the consistency of data used by various departments and regions, reduces data inconsistencies caused by human errors or delayed updates, and ensures data consistency. Automation also reduces the reliance on manual input, reduces the possibility of human errors, and ensures data accuracy. BRIEF DESCRIPTION OF THE DRAWINGS

[0043] Figure 1 A flowchart of a method for generating and updating a BOM table in a prototype design and development provided by an embodiment of the present invention;

[0044] Figure 2 A diagram showing the system creating an index for a key field in an embodiment of the present invention;

[0045] Figure 3 The BOM tree is displayed after being graphically visualized in an embodiment of the present invention. DETAILED DESCRIPTION

[0046] In order to make the purpose, technical solution and advantages of the embodiments of the present invention clearer, the technical solution in the embodiments of the present invention will be clearly and completely described in combination with the embodiments of the present invention. Obviously, the described embodiments are part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of the present invention.

[0047] Example 1

[0048] See also Figure 1 As shown, it is a flowchart of a method for generating and updating a BOM table in a prototype design and development provided in this embodiment, and the method comprises the following steps:

[0049] S1 (Kettle ETL job scheduling): Using the open source ETL tool Kettle, with its powerful data extraction, conversion and loading functions, the system connects to source data such as the PLM database, extracts the BOM data of the main material and sub-material from the PLM database on a regular basis, establishes the corresponding relationship and quantity list of the main material and sub-material in the PLM database, uses SQL query and the reorganization of the material hierarchy structure, converts the data according to the hierarchical relationship of the material, and generates multiple hierarchical conversion tables for the main material and sub-material. Finally, through Kettle's job scheduling function, these converted hierarchical conversion tables are automatically and regularly loaded into the target SQL Server database. This automated data update process greatly improves data processing efficiency, reduces errors that may be caused by manual operations, and ensures data consistency and timeliness.

[0050] S2 (Excel automatic data update): By establishing a database connection in Excel and combining the ADO (ActiveX Data Objects) technology integrated in Excel with the VBA timing function, the hierarchical conversion table in the target SQL Server database is automatically updated and queried, and the BOM data at each level is extracted from the hierarchical conversion table and displayed in multiple worksheets. The data displayed in the worksheets are the data at each level of the BOM, and each worksheet displays the BOM data at a single level. Users can avoid manual refresh operations, and data is automatically imported into different Excel worksheets to improve data processing efficiency. VBA timing tasks support custom update intervals to ensure efficient and timely data updates while reducing the occurrence of human errors.

[0051] S3 (Excel worksheet data processing and aggregation): Using VBA, Excel can automatically extract, process and integrate the data in the worksheet. The single-level BOM data displayed in multiple worksheets is reconstructed by converting multiple rows into multiple columns according to the hierarchical structure, and finally aggregated into the same worksheet. The aggregated worksheet is called a multi-level BOM table, which displays complete multi-level BOM information, further enhancing the operability and visualization of the data.

[0052] The process of extracting and integrating the multiple worksheets into a multi-level BOM table specifically includes: using the multiple worksheets storing the main materials and sub-materials as the source data table, using nested loops to traverse the data; the outer loop processes the source data table main and sub-material relationship correspondence table, the inner loop processes the finished material information table, and uses conditional judgment to check the data matching. After data matching and calculation, the calculation results of the Cell method are written into specific cells, and the Rows.Insert method is used to dynamically insert new rows. According to the matching results, each level of data will continue to be processed and inserted into the updated target data table based on the previous level. Finally, a complete multi-level BOM table is formed.

[0053] S4 (Kettle and Neo4j integration): Integrate the Neo4j graph database in Kettle to extract material information at each level from the multi-level BOM table at regular intervals. Through Kettle's conversion and job scheduling functions, node relationship data is automatically generated and regularly updated to the Neo4j database to build a relationship map between materials to provide support for 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 in the figure, the query efficiency and data access speed are significantly improved. The regular update mechanism ensures that the data in the Neo4j graph database remains synchronized, supporting efficient graph query 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 realize the graphical display of the multi-level BOM table. The hierarchical relationship between materials is displayed through a force-directed graph, so that users can understand the material structure and association more intuitively, and enhance the visibility and operability of data analysis. The display diagram is as follows: Figure 3 The specific operation process of step S5 is as follows:

[0055] S5-1: First, use the SVG element as a visualization container, whose size automatically adapts to the browser window, and use defs and marker to define the arrow shape used for the end of the connecting line to indicate the directionality;

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

[0057]

[0058] Among them, F limk (u,v) is the gravitational force between nodes u and v, k linkis the constant of the connecting line force, which is used to control the strength of the force. distance(u,v) is the actual distance between nodes u and v. lo is the ideal connecting line length, that is, the distance between nodes that we want to maintain. is the direction vector from node u to node v;

[0059] The operating mechanism of the above formula is: when the actual distance between nodes u and v is greater than the ideal length lo, the formula generates an attractive force to pull 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 to push the nodes away; 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] Charge, i.e. the repulsive force between nodes, is used to avoid excessive clustering of nodes. The repulsive force formula between two nodes u and v is:

[0061]

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

[0063] Use Center, or the centering force, to pull all nodes toward the center of the SVG to ensure that the graphics are displayed in the center. The centering force formula between two nodes u and v is:

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

[0065] Among them, F center (u) is the centering force on node u, and its direction is from the current position of the node to the center of the graph. center The center force constant is used to adjust the force. Increasing center can strengthen the tendency of nodes to gather toward the center. center is the center position of the graph. This position can usually be set to the geometric center of the graph, or to a reference point according to specific needs. position(u) is the current position of node u.

[0066] S5-3: Use d3.json to asynchronously load external graph data (nodes and connecting lines) in JSON format, use Links and Nodes: to bind the data to SVG elements, create connecting lines and nodes, use line elements to represent the connection between nodes, use circle elements to represent each node, set the color 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 to increase user interactivity. Use Window Resize to automatically adjust the SVG size and force center when the window size changes to keep the graphics fit the screen to achieve interactive functions.

[0068] S5-5: Use the Ticked function to provide a method for updating the positions of nodes and connecting lines for the dynamic layout of the force-directed graph.

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

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

[0071] This embodiment realizes the automation of the data update process, ensures the consistency and accuracy of the data, and reduces the errors caused by human operations; by adopting a distributed database and real-time synchronization technology, efficient cross-departmental and cross-regional collaboration is achieved, ensuring the consistency and real-time nature of the data.

[0072] This embodiment also introduces a graphical display effect to display the material lists of each level required for the development of prototypes in the form of a graphical BOM tree. When a product is selected, the material lists of each node belonging to the product will automatically spread out. There is no need to click on the nodes one by one, and the nodes are not closed, thereby realizing the visualization of multi-level BOM tables.

[0073] Example 2

[0074] Corresponding to the first embodiment of the present invention, the second embodiment of the present invention further provides a system for generating and updating a BOM table in prototype design and development, which is characterized by comprising the following modules:

[0075] Kettle ETL job scheduling module: used to use the open source ETL tool Kettle. With the powerful data extraction, conversion and loading functions of the open source ETL tool Kettle, the system connects with source data such as the PLM database to extract the BOM data of the main material and sub-material from the PLM database on a regular basis, establish the corresponding relationship and quantity list of the main material and sub-material in the PLM database, use SQL query and reorganization of the material hierarchy structure, convert the data according to the hierarchical relationship of the material, and generate multiple hierarchical conversion tables for the main material and sub-material. Finally, through the job scheduling function of Kettle, these converted hierarchical conversion tables are automatically and regularly loaded into the target SQL Server database. This automated data update process greatly improves data processing efficiency, reduces errors that may be caused by manual operations, and ensures data consistency and timeliness.

[0076] Excel automatic data update module: used to automatically update and query the hierarchical conversion table in the target SQL Server database by establishing a database connection in Excel and combining the ADO (ActiveX Data Objects) technology integrated in Excel with the VBA timing function, and extract the BOM data at each level from the hierarchical conversion table and display them in multiple worksheets. The data displayed in the worksheets are the BOM data at each level, and each worksheet displays the BOM data at a single level. Users can avoid manual refresh operations, and data is automatically imported into different Excel worksheets to improve data processing efficiency. VBA timing tasks support custom update intervals to ensure efficient and timely data updates while reducing the occurrence of human errors.

[0077] Excel worksheet data processing and aggregation module: used to use VBA, Excel can automatically extract, process and integrate the data in the worksheet. The single-level BOM data displayed in multiple worksheets is reconstructed by converting multiple rows into multiple columns according to the hierarchical structure, and finally aggregated into the same worksheet. The aggregated worksheet is called a multi-level BOM table, which displays complete multi-level BOM information, further enhancing the operability and visualization of the data.

[0078] The process of extracting and integrating the multiple worksheets into a multi-level BOM table specifically includes: using the multiple worksheets storing the main materials and sub-materials as the source data table, using nested loops to traverse the data; the outer loop processes the source data table main and sub-material relationship correspondence table, the inner loop processes the finished material information table, and uses conditional judgment to check the data matching. After data matching and calculation, the calculation results of the Cell method are written into specific cells, and the Rows.Insert method is used to dynamically insert new rows. According to the matching results, each level of data will continue to be processed and inserted into the updated target data table based on the previous level. Finally, a complete multi-level BOM table is formed.

[0079] Kettle and Neo4j integration module: used to integrate the Neo4j graph database in Kettle and extract material information of each level from the multi-level BOM table at regular intervals. Through Kettle's conversion and job scheduling functions, node relationship data is automatically generated and regularly updated to the Neo4j database to build a relationship map between materials to provide support for 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 in the figure, the query efficiency and data access speed are significantly improved. The regular update mechanism ensures that the data in the Neo4j graph database remains synchronized, supporting efficient graph query and fast data retrieval.

[0080] Neo4j data visualization module: It is used to use D3.js to realize the graphical display of the multi-level BOM table according to the node relationship information in the Neo4j graph database, and to display the hierarchical relationship between materials through a force-directed graph, so that users can understand the material structure and association more intuitively, and enhance the visibility and operability of data analysis. The display diagram is as follows: Figure 3 The Neo4j data visualization module includes the following units:

[0081] Visual container unit, used to use SVG elements as visual containers, whose size automatically adapts to the browser window, and uses defs and markers to define the arrow shape used for the end of the connecting line to indicate directionality;

[0082] The force-directed graph simulation unit is used to simulate the node relationship information in the Neo4j graph database obtained in the Kettle and Neo4j integration module using the force-directed graph. Specifically, the physical rules of nodes and connecting lines are defined using Simulation, and the length of the connecting line is maintained using Link, i.e., the connecting line force. The connecting line force formula between two nodes u and v is:

[0083]

[0084] Among them, F link(u,v) is the gravitational force between nodes u and v, k link is the constant of the connecting line force, which is used to control the strength of the force. distance(u,v) is the actual distance between nodes u and v. lo is the ideal connecting line length, that is, the distance between nodes that we want to maintain. is the direction vector from node u to node v;

[0085] The operating mechanism of the above formula is: when the actual distance between nodes u and v is greater than the ideal length lo, the formula generates an attractive force to pull 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 to push the nodes away; 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] Charge, i.e. the repulsive force between nodes, is used to avoid excessive clustering of nodes. The repulsive force formula between two nodes u and v is:

[0087]

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

[0089] Use Center, or the centering force, to pull all nodes toward the center of the SVG to ensure that the graphics are displayed in the center. The formula for the centering force between the two nodes u and v is as follows:

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

[0091] Among them, F center (u) is the centering force on node u, and its direction is from the current position of the node to the center of the graph, k center The centering force constant is used to adjust the size of the force. Increasing center can strengthen the tendency of nodes to gather toward the center. Center is the center position of the graph. This position is set to the geometric center of the graph, or to a reference point according to specific requirements. 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 links), use Links and Nodes: data binding to SVG elements, create links and nodes, use line elements to represent the connection between nodes, use circle elements to represent each node, set the color through a mapping function, and display different types of materials to achieve data loading and binding;

[0093] Graphics adaptation unit, used to make nodes draggable by using Drag to increase user interactivity, and to automatically adjust the SVG size and force center by using WindowResize when the window size changes, so as to keep the graphics fit the screen and realize interactive functions;

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

[0095] This embodiment realizes the intuitive display of multi-level BOM tables through the operation and mutual cooperation of the above five modules: through the graphical BOM tree, the hierarchical relationship of materials is directly displayed in a visual form, which greatly improves the comprehensibility of the product structure. This intuitive display method helps to quickly identify errors or improvements in the design; it can also automatically expand the reality, that is, after selecting a product, the related material structure will automatically expand without manually clicking each node. This function reduces the complexity of operation and improves the work efficiency of users; it can also prevent nodes from closing, and each node remains open during the interaction process, avoiding repeated operations and enhancing the user experience.

[0096] The automated data update process in this embodiment ensures the consistency of data used by various departments and regions, reduces data inconsistencies caused by human errors or delayed updates, and automation reduces reliance on manual input, reduces the possibility of human errors, and ensures data accuracy.

[0097] This embodiment uses a distributed database and real-time synchronization technology to achieve cross-departmental and cross-regional data sharing and collaborative work, ensure the real-time and consistency of data, and improve information flow and collaboration efficiency among teams, especially in large-scale projects involving multiple locations and multiple teams.

[0098] In summary, the graphical and automated features of the embodiments of the present invention greatly reduce the time from design to production, while improving the design accuracy and production efficiency; the systematic design allows for flexible response to changing business needs and technology upgrades, ensuring long-term and effective support for enterprise development; by providing an efficient, intuitive and easy-to-use system, the user experience is enhanced and the market competitiveness of the enterprise is improved; and an efficient, accurate and easy-to-manage BOM management solution is provided for users.

[0099] The above embodiments are only used to illustrate the technical solutions of the present invention, rather than to limit the same. Although the present invention has been described in detail with reference to the aforementioned embodiments, those skilled in the art should understand that the technical solutions described in the aforementioned embodiments may still be modified, or some of the technical features may be replaced by equivalents. However, these modifications or replacements do not deviate the essence of the corresponding technical solutions 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 BOM table in prototype design and development, characterized in that: The following steps are involved: S1: Extract the BOM data of main materials and sub-materials from the source database regularly, establish the correspondence between main materials and sub-materials and the quantity list, use SQL query and reorganize the material hierarchy structure, convert the data according to the material hierarchy, generate multiple hierarchy conversion tables of main materials and sub-materials, and automatically load these converted hierarchy conversion tables into the target SQL Server database regularly; S2: By establishing a database connection in Excel, the hierarchical conversion table in the target SQL Server database is automatically updated and queried in Excel, and the data of each level of BOM is extracted from the hierarchical conversion table and put into multiple worksheets for display respectively. 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: The single-level BOM data displayed in the multiple worksheets are reconstructed by converting multiple rows into multiple columns according to the hierarchical structure, and finally summarized into the same worksheet. The summarized worksheet is called a multi-level BOM table, which displays complete multi-level BOM information; S4: extracting material information of each level from the multi-level BOM table regularly, automatically generating node relationship data through Kettle's conversion and job scheduling functions, and regularly updating it to the Neo4j database to build a relationship map between materials; S5: Based on the node relationship information in the Neo4j graph database, a D3 force-directed graph is used to realize the graphical display of the multi-level BOM table.

2. The method for generating and updating a BOM table in prototype design and development according to claim 1, characterized in that: The step S1 is performed using the open source ETL tool Kettle, and the step S2 is performed using the ADO technology and VBA timing function integrated in Excel.

3. The method for generating and updating a BOM table in prototype design and development according to claim 1, characterized in that: In the step S3, the process of extracting and integrating the multiple worksheets into a multi-level BOM table specifically includes: combining the ADO technology integrated in Excel and the VBA timing function, using the multiple worksheets storing the main materials and sub-materials as the source data table, and using nested loops to traverse the data; the outer loop processes the source data table main-sub-material relationship correspondence table, the inner loop processes the finished material information table, and uses conditional judgment to check the data matching. After the data is matched and calculated, the calculation result of the Cell method is written into a specific cell, and the Rows.Insert method is used to dynamically insert new rows. According to the matching results, the data of each level will continue to be processed and inserted into the updated target data table based on the previous level, and finally a complete multi-level BOM table is formed.

4. The method for generating and updating a BOM table in prototype design and development according to claim 1, characterized in that: In step S4, an index is also created for the key field.

5. The method for generating and updating a BOM table in prototype design and development according to claim 1, characterized in that: In step S5, realizing the visualization of the multi-level BOM table includes the following processes: Use the SVG element as a visual container, defs and marker to define the arrow shape used to connect the ends of the line; Using force-directed graph simulation, the specific process is: using Simulation to define the physical rules of nodes and connecting lines, through the joint action of the connecting line force, repulsive force, centering force and speed attenuation between the 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 specific process of data loading and binding is as follows: d3.json is used to asynchronously load external graph data in JSON format, Links and Nodes is used to bind data to SVG elements, create connecting lines and nodes, line elements represent the connection between nodes, circle elements represent each node, and the color is set through a mapping function to display different types of materials; Use Drag to drag nodes, and Window Resize to automatically adjust the SVG size and force center when the window size changes, so as to keep the graphics fit the screen and realize interactive functions; By using the Ticked function, a method for updating the positions of nodes and connecting lines is provided for the dynamic layout of the force-directed graph to achieve dynamic updates.

6. The method for generating and updating a BOM table 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) is the gravitational force between nodes u and v, k link is the constant of the connecting line force, which is used to control the strength of the force. distance(u,v) is the actual distance between nodes u and v. lo is the ideal connecting line length, that is, the distance between nodes that we want to maintain. is the direction vector from node u to node v.

7. The method for generating and updating a BOM table in prototype design and development according to claim 6, characterized in that: The operating mechanism of the formula for the connecting line force between the two nodes u and v is: when the actual distance between nodes u and v is greater than the ideal length lo, the formula generates an attractive force to pull 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 to push the nodes away; 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 expected ideal length.

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

9. The method for generating and updating a BOM table 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) is the centering force on node u, and its direction is from the current position of the node to the center of the graph, k center The centering force constant is used to adjust the size of the force. Increasing center can strengthen the tendency of nodes to gather toward the center. Center is the center position of the graph. This position is set to the geometric center of the graph, or to a reference point according to specific requirements. Position(u) is the current position of node u.

10. A system for generating and updating a BOM table in prototype design and development, characterized in that: Includes the following modules: Kettle ETL job scheduling module: used to extract BOM data of main materials and sub-materials from the source database on a regular basis, establish the correspondence between main materials and sub-materials and the quantity list, use SQL query and reorganization of material hierarchy structure to convert data according to the hierarchical relationship of materials, generate multiple hierarchical conversion tables of main materials and sub-materials, and automatically load these converted hierarchical conversion tables into the target SQL Server database on a regular basis; Excel automatic data update module: used to automatically update and query the hierarchical conversion table in the target SQL Server database in Excel by establishing a database connection in Excel, and extract the BOM data of each level from the hierarchical conversion table and put it into multiple worksheets for display respectively. 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; Excel worksheet data processing and aggregation module: used to reconstruct the single-level BOM data displayed in multiple worksheets into multiple columns according to the hierarchical structure, and finally aggregate them into the same worksheet. The aggregated worksheet is called a multi-level BOM table, which displays complete multi-level BOM information; Kettle and Neo4j integration module: used to extract material information of each level from the multi-level BOM table at regular intervals, automatically generate node relationship data through Kettle's conversion and job scheduling functions, and regularly update it to the Neo4j database to build a relationship map between materials; Neo4j data visualization module: used to realize the graphical display of the multi-level BOM table using D3 force-directed graph according to the node relationship information in the Neo4j graph database.

Images