CN113850541A - Inventory-driven optical cable order distribution method based on SQL database - Google Patents
Inventory-driven optical cable order distribution method based on SQL database Download PDFInfo
- Publication number
- CN113850541A CN113850541A CN202110965889.7A CN202110965889A CN113850541A CN 113850541 A CN113850541 A CN 113850541A CN 202110965889 A CN202110965889 A CN 202110965889A CN 113850541 A CN113850541 A CN 113850541A
- Authority
- CN
- China
- Prior art keywords
- inventory
- optical cable
- order
- information
- factory
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Pending
Links
Images
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06Q—INFORMATION AND COMMUNICATION TECHNOLOGY [ICT] SPECIALLY ADAPTED FOR ADMINISTRATIVE, COMMERCIAL, FINANCIAL, MANAGERIAL OR SUPERVISORY PURPOSES; SYSTEMS OR METHODS SPECIALLY ADAPTED FOR ADMINISTRATIVE, COMMERCIAL, FINANCIAL, MANAGERIAL OR SUPERVISORY PURPOSES, NOT OTHERWISE PROVIDED FOR
- G06Q10/00—Administration; Management
- G06Q10/08—Logistics, e.g. warehousing, loading or distribution; Inventory or stock management
- G06Q10/087—Inventory or stock management, e.g. order filling, procurement or balancing against orders
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/242—Query formulation
- G06F16/2433—Query languages
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/245—Query processing
- G06F16/2455—Query execution
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Physics & Mathematics (AREA)
- Business, Economics & Management (AREA)
- General Physics & Mathematics (AREA)
- Computational Linguistics (AREA)
- Data Mining & Analysis (AREA)
- Databases & Information Systems (AREA)
- General Engineering & Computer Science (AREA)
- Economics (AREA)
- Development Economics (AREA)
- Operations Research (AREA)
- Accounting & Taxation (AREA)
- Mathematical Physics (AREA)
- Entrepreneurship & Innovation (AREA)
- Human Resources & Organizations (AREA)
- Marketing (AREA)
- Finance (AREA)
- Quality & Reliability (AREA)
- Strategic Management (AREA)
- Tourism & Hospitality (AREA)
- General Business, Economics & Management (AREA)
- General Factory Administration (AREA)
- Management, Administration, Business Operations System, And Electronic Commerce (AREA)
Abstract
The invention discloses an inventory-driven optical cable order distribution method based on an SQL database, which sorts orders according to priority; accessing an SQL database and calling inventory information; matching inventory information according to the order requirement by applying an inventory-driven optical cable order distribution algorithm, wherein the matching conditions comprise product information, single length of the optical cable, printing information and pairwise length matching; if the inventory matching is successful, the order is distributed to an inventory factory and matched with the corresponding optical cable disc number, otherwise, the production capacity of the factory is evaluated, and the factory is selected by using a roulette selection method; the SQL language is connected with an SQL database, and inventory information is updated in real time; the above operations are repeated until all the order allocations are completed. Through the mode, the intelligent order distribution scheme is given based on the real-time inventory data, the requirement of the optical cable manufacturing enterprise on the level of human experience in making a scheduling plan is reduced, the time for manual scheduling decision is shortened, the inventory flow is accelerated, the inventory backlog is reduced, and the production efficiency and the resource utilization rate of a factory are improved.
Description
Technical Field
The invention relates to the technical field of scheduling optimization and inventory management, in particular to an inventory-driven optical cable order distribution method based on an SQL database.
Background
The optical cable is a cable which is formed by processing optical fibers (optical transmission carriers) through a certain process and is used for realizing a communication line for optical signal transmission. The optical cable generally comprises a cable core, a reinforcing steel wire, a filler, a sheath and other parts, and further comprises a waterproof layer, a buffer layer, an insulated metal wire and other components according to requirements.
Because optical fiber communication has the advantages of large capacity, long transmission distance, stable transmission signal, small signal crosstalk, strong confidentiality and the like, optical fiber cables are needed to transmit signals in the scenes of communication systems, radio and television systems, electric power systems, railway systems, public security traffic police systems, bank systems and the like.
Along with the continuous development of economy, the demand of cities on optical cables is also increased, how to carry out intelligent scheduling on optical cable production workshops efficiently not only directly influences the production efficiency of the workshops, but also relates to the reasonable allocation of production resources and human resources.
The scheduling steps of an optical cable production workshop in the prior art are as follows: the method comprises the steps of firstly distributing the arrived sales orders to each factory, then making a reasonable and optimized production scheduling plan in each factory according to early-stage data (delivery period, raw material inventory and the like), and finally generating a manufacturing plan of each production workshop according to the production scheduling plan.
The order distribution to the factory is the first step of scheduling, and the reasonable order distribution scheme needs to comprehensively consider the influence factors of equipment, personnel, materials, inventory and the like.
At present, a large amount of finished product stocks can be accumulated in the production process of optical cable production enterprises, so that the use space of the stocks can be occupied, and the material waste can be caused. Therefore, how to go to inventory and ensure efficient circulation of inventory are important factors to be considered for order distribution.
The existing order allocation operation mainly comprises manual comparison and allocation according to experience and an inventory information table, and the above modes have many disadvantages: firstly, the personnel who need to make the distribution plan have rich experience and high personnel cost; the operator needs to spend a lot of time for inventory comparison, the query and arrangement of various information takes a long time, and careless mistakes can be caused; the data of the inventory information is dynamically changed, and the communication among all departments is not timely, so that the wrong information transmission can be caused.
Therefore, how to design an efficient inventory-driven optical cable order distribution method, realize automatic order distribution and inventory deduction, and improve production efficiency becomes a problem to be solved urgently.
Disclosure of Invention
The invention solves the technical problems in the prior art and provides an inventory-driven optical cable order distribution method based on an SQL database.
In order to solve the technical problems, the invention adopts a technical scheme that: an intelligent decision-making-assisted optical cable order distribution method is provided, which comprises the following steps:
step S1, manually setting order sequencing according to order priority, establishing an order information table, wherein the order information table contains all the demand information of the order, and performing order distribution according to the order information table sequence until the distribution of all the orders in the order information table is completed;
step S2, taking out orders in the order information table in sequence, accessing the SQL database, sending SQL sentences to the internal SQL database through connection, and acquiring optical cable inventory information of each factory;
step S3, manually setting demand information by using an order distribution algorithm driven by inventory, and matching the demand information with the existing inventory information;
step S4, if the order that can be matched to the stock is assigned to the factory where the stock is located by step S3; if no stock matches, then evaluating the production capacity of each factory, and selecting the factory for order by roulette;
and step S5, generating an order distribution table, carrying out stock deduction according to the matched stock information, and updating the stock information in the SQL database.
In step S1, an order information table is created to include an order code, an order quantity, a product specification, a lettering requirement, and a delivery date.
In the step S2, the product inventory involves a large amount of comparison information, and the types and sizes of data storage are increasing, which brings certain challenges to database management. SQL is an operation command set, is widely popular among the industry people due to rich functions, becomes a guarantee for improving the operation efficiency of the database, and is widely applied to server and client system structures.
The method comprises the following steps of sending SQL sentences to an internal SQL database, obtaining operation results of the SQL sentences, and analyzing the operation results into optical cable inventory information of each factory, wherein the operation results comprise: the system comprises a disc number, a cable number, a material code, a material name, a specification model, a stock length, a warehouse code, a warehouse name, scanning warehousing time and an order number.
In step S3, the inventory-driven order allocation algorithm is: firstly, because the demands of each batch of orders are different, demand information needs to be set manually, and screening is carried out according to the demand information:
the screening conditions include:
(1) product information matching:
the cable information in the inventory information is denoted as field FModel, for example: GYTS-12B1.3(1+5-1.8) B, the cable information contains 4 parts of information, which are respectively: model information (GYTS), specification information (12B 1.3), standard one information (1+5-1.8) and standard two information (B);
when optical cable information is compared, each kind of information is not required to be matched sometimes, so that the comparison information is set to be capable of being manually checked, namely which parts of information are matched can be selected, wherein the model information can not be limited to one type, and therefore the model type is set to be capable of being manually increased.
(2) Optical cable single length matching: different orders may have different requirements for a single length of cable, and therefore, the order may be set to allow for an upper and lower bound for the length of cable to be entered, and inventory comparisons may be made based on the length ranges.
(3) Printing information matching: the optical cable order can have requirements on the lettering information of the optical cable, and the optical cable order is divided into two types: and (3) precise matching: the lettering information of the optical cable in the stock is consistent with the lettering information in the order; fuzzy matching: the lettering information only needs to contain partial keywords, and the keywords are set to be manually input.
(4) The length is matched in pairs: the optical cable order requires that the optical cables can be paired with each other, and the pairing requirements are as follows: the sum of the lengths of the two cables belongs to the interval [3, 3.5 ], [4, 4.5 ], [5, 5.5 ], and the decimal part of the sum of the lengths is ensured to be minimum.
The screening conditions (1) - (3) can screen optical cable disc numbers meeting requirements, the screening of the condition (4) needs to ensure that the sum of every two lengths meets an interval range, and the decimal part of the length sum after being rounded is minimum, and a length matching accurate algorithm is designed for screening the condition (4).
The exact algorithm for the above length matching is as follows:
(1) any two optical cable stocks are combined, and the total is Cn 2Calculating the sum of the lengths of each combination, recording the combination I if the combination is in accordance with the interval range, and adding the combination I into the set I; recording the length and the S, and adding the length and the S into the set S;
(2) searching a combination with the length sum not being 0 and the smallest decimal part in the set S, recording the combination, and recording the length sum of the combinations including the optical cable reel number in the combination as 0 in the set S;
(3) and (4) judging whether the elements in the set S are all 0, if so, outputting the recorded combination, and otherwise, returning to the step (2).
In the step S4: if the order can be matched to the corresponding inventory, it is assigned to the factory where the inventory is located, via step S3; if no inventory matches, the capacity of each plant is assessed and the order selects the plant according to roulette selection.
The production of the optical cable needs 5 processes (coloring, ribbon, two sets, cabling and sheath), the optical cable respectively passes through 5 processing workshops, each process has a plurality of processing devices, the organization of the production department adopts a process specialization principle, and the feeding and the output of the product have a long interval period and obvious periodicity, so the input amount is selected as an evaluation index, namely the total equipment number and the installed capacity of single equipment are considered.
Plant throughput (throughput) = production capacity
Wherein: siThe total number of devices, Q, in the process plant of Process iiThe installed capacity of a single device of a processing workshop of the process i, and M is the total number of processing processes;
the factories are regarded as individuals, the production capacity of each factory is used as an individual fitness value, in the roulette selection method, the selection probability of each individual is proportional to the fitness value, the greater the fitness value is, the greater the selection probability is, and the order distribution is carried out according to the production capacity of the factories.
In step S5, an order distribution table is finally generated, and according to the matching result, the inventory information is updated by using SQL statements, and if the length of one optical cable inventory is used up, the optical cable reel number is deleted from the inventory information; otherwise, the length of the used optical cable reel number is deducted.
The invention provides an optimized intelligent auxiliary optical cable order distribution method, which establishes the relation between order requirements and inventory information through an SQL database, sequentially applies an inventory-driven order distribution algorithm to carry out order matching based on the order priority of an order information table, and provides an auxiliary order distribution scheme and an optical cable inventory matching scheme based on the purpose of inventory removal.
According to the inventory-driven optical cable order distribution method based on the SQL database, intelligent distribution auxiliary scheduling suggestions are given based on inventory real-time data and factory production capacity evaluation, the requirement of order distribution on the artificial experience level is reduced, the statistics of related data and the artificial production scheduling time are reduced, the order distribution is more accurate, and the inventory backlog is greatly reduced on the basis of ensuring the order distribution in a balanced manner.
Drawings
In order to more clearly illustrate the technical solutions in the embodiments of the present invention, the drawings needed to be used in the description of the embodiments are briefly introduced below, it is obvious that the drawings in the following description are only some embodiments of the present invention, and other drawings can be obtained by those skilled in the art without inventive efforts, wherein:
FIG. 1 is a flow chart of the SQL database based inventory driven order distribution method of the present invention;
FIG. 2 is a flow chart of an inventory driven cable order allocation algorithm of the present invention;
FIG. 3 is a flow chart of the length matching refinement algorithm of the present invention.
Detailed Description
The technical solutions in the embodiments of the present invention will be clearly and completely described below, and it is obvious that the described embodiments are only a part of the embodiments of the present invention, and not all embodiments. All other embodiments, which can be derived by a person skilled in the art from the embodiments given herein without making any creative effort, shall fall within the protection scope of the present invention.
Referring to fig. 1 to 3, an embodiment of the present invention includes:
the invention provides an inventory-driven order allocation method based on an SQL database, which comprises the following steps:
firstly, ordering orders manually according to order priority to generate an order information table.
And secondly, sequentially processing the orders according to the order sequence in the order information table, connecting the SQL database and acquiring optical cable inventory information of each factory.
And thirdly, manually setting matching requirements by applying an inventory-driven optical cable order distribution algorithm, and screening inventory in optical cable inventory information.
Step four, if the matching of the stock is successful, the order is distributed to the factory with the stock; otherwise, the order allocation is performed according to the production capacity of the factory.
And fifthly, generating a final order distribution table and updating the inventory information in the SQL database.
In step three, the process of the inventory-driven optical cable order allocation algorithm is as shown in fig. 2, the order information arrives, inventory matching is performed by accessing the SQL database, and there are 4 matching conditions in total:
product information matching: the method comprises the steps of type number, specification, first standard and second standard;
optical cable single information matching: including an upper and lower length bound;
printing information matching: fuzzy matching and precise matching are included;
the lengths are matched pairwise: pairing every two optical cables based on single optical cable inventory meeting the 3 conditions, ensuring that the sum of the lengths is within an interval range and the rounded decimal part is minimum;
finally, a matching inventory table is generated, all available optical cable inventory is listed, and the inventory information of the SQL database is updated.
Fig. 3 shows the implementation of the exact algorithm for length matching:
firstly, screening single optical cable stocks meeting the requirements through product information matching, single optical cable information matching and printing information matching, combining 2 optical cable stocks at will, and screening combinations with the sum of the lengths within a specified interval range;
and searching the combination with the smallest decimal part after the sum of the lengths is rounded until no available combination exists, ending the search, and finally generating an order distribution table.
And in the fourth step, if the inventory is not successfully matched, the order distribution is carried out according to the production capacity of a factory, the production mode of an optical cable production enterprise is considered, and the input amount is selected as an evaluation standard, namely the total equipment number and the installed capacity of single equipment are comprehensively considered.
Plant capacity (t)hroughput）=
Wherein: siThe total number of devices, Q, in the process plant of Process iiThe installed capacity of a single device of a processing workshop of the process i, and M is the total number of processing processes;
the production capacity ratio of each plant is calculated, and the plant is selected by roulette selection for each order.
The inventory-driven optical cable order distribution method based on the SQL database has the advantages that:
the intelligent allocation auxiliary scheduling suggestions are given based on real-time inventory data and factory production capacity assessment, the requirements of order allocation on the level of human experience are reduced, relevant data statistics and manual scheduling time are reduced, order allocation is more accurate, and inventory backlog is greatly reduced on the basis of ensuring balanced order allocation.
The above description is only an embodiment of the present invention, and not intended to limit the scope of the present invention, and all modifications of equivalent structures and equivalent processes, which are made by the present specification, or directly or indirectly applied to other related technical fields, are included in the scope of the present invention.
Claims (6)
1. An inventory-driven optical cable order distribution method based on an SQL database is characterized by comprising the following steps:
s1, ordering the orders to be processed according to the order priority, and establishing an order information processing table;
s2, taking out orders in the order information table in sequence, accessing the SQL database and obtaining optical cable inventory information of each factory;
s3, manually setting demand information by using an optical cable order distribution algorithm driven by inventory, and matching the demand information with the existing inventory information;
s4, matching the order of the stock and distributing the order to the factory where the stock is located; otherwise, selecting the factory by using a roulette selection method according to the evaluated factory production capacity;
s5, generating an order distribution table, and updating the stock information in the SQL database; the operation is repeated until the allocation of all orders is completed.
2. The SQL database-based inventory-driven optical cable order distribution method according to claim 1, wherein in step S1, the order information table associates optical cable information, which includes order codes, order quantity, product specifications, lettering requirements, and delivery date.
3. The SQL database-based inventory-driven optical cable order distribution method according to claim 2, wherein in step S2, optical cable inventory information in the SQL database is accessed, the inventory information including a tray number, a cable number, a material code, a material name, a specification model, an inventory length, a warehouse code, a warehouse name, a scan-in-warehouse time, and an order number.
4. The SQL database-based inventory-driven optical cable order distribution method according to claim 3, wherein in step S3, matching conditions are manually set, wherein the matching conditions include: product information, optical cable single length, printing information and pairwise length are paired:
wherein the product information includes: the model, the specification, the first standard and the second standard; the single length of optical cable comprises: an upper bound and a lower bound; the printing information includes: precise matching and fuzzy matching;
pairwise length pairing requirements include: the lengths of the optical cables can be matched with each other, the matching requirement is that the sum of the lengths of the two optical cables belongs to the interval [3, 3.5 ], [4, 4.5 ], [5, 5.5), and the decimal part of the sum of the lengths is ensured to be minimum.
5. The SQL database-based inventory-driven optical cable order distribution method according to claim 4, wherein in step S3, an inventory-driven optical cable order distribution algorithm is used for an order to perform inventory screening, and the screening process includes the following steps:
s31, screening out all optical cable reel numbers meeting the matching conditions of product information, single length of optical cable and printing information, combining any two optical cables, and calculating the sum of the lengths of each combination;
s32, searching the minimum value of the decimal part after the sum of the lengths of the optical cables is rounded, recording the combination, and deleting other combinations containing any optical cable reel number;
s33, repeating step S32 until no cable combination is available, and then outputting all cable reel numbers available for the order.
6. The SQL database-based inventory-driven optical cable order distribution method of claim 5, wherein in step S4, for the order that does not match the inventory, the production capacity of each factory needs to be evaluated, the factory is selected by roulette selection,
when the production capacity of each factory is evaluated, the factory input amount is selected as an evaluation index, namely the total equipment number and the installed capacity of each unit of equipment are considered:
plant throughput (throughput) = production capacity
Wherein: siThe total number of devices, Q, in the process plant of Process iiThe installed capacity of a single device of a processing workshop of the process i, and M is the total number of processing processes;
when selecting factories by the roulette selection method, each factory is regarded as an individual, the production capacity of the factory is taken as an individual fitness value, the selection probability of each individual is proportional to the fitness value, the greater the fitness value is, the greater the selection probability is, namely, the order distribution is carried out according to the production capacity of the factory.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202110965889.7A CN113850541A (en) | 2021-08-23 | 2021-08-23 | Inventory-driven optical cable order distribution method based on SQL database |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202110965889.7A CN113850541A (en) | 2021-08-23 | 2021-08-23 | Inventory-driven optical cable order distribution method based on SQL database |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN113850541A true CN113850541A (en) | 2021-12-28 |
Family
ID=78975781
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202110965889.7A Pending CN113850541A (en) | 2021-08-23 | 2021-08-23 | Inventory-driven optical cable order distribution method based on SQL database |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN113850541A (en) |
Cited By (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| TWI813510B (en) * | 2022-11-01 | 2023-08-21 | 大陸商鼎捷軟件股份有限公司 | Order matching and follow-up system and method thereof |
| CN117557078A (en) * | 2024-01-12 | 2024-02-13 | 诺彩科技(江苏)有限公司 | Intelligent production plan management method and system based on time nodes |
-
2021
- 2021-08-23 CN CN202110965889.7A patent/CN113850541A/en active Pending
Cited By (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| TWI813510B (en) * | 2022-11-01 | 2023-08-21 | 大陸商鼎捷軟件股份有限公司 | Order matching and follow-up system and method thereof |
| CN117557078A (en) * | 2024-01-12 | 2024-02-13 | 诺彩科技(江苏)有限公司 | Intelligent production plan management method and system based on time nodes |
| CN117557078B (en) * | 2024-01-12 | 2024-03-22 | 诺彩科技(江苏)有限公司 | Intelligent production plan management method and system based on time nodes |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN110555640B (en) | Route planning method and device | |
| CN113850541A (en) | Inventory-driven optical cable order distribution method based on SQL database | |
| CN102147792B (en) | Customized knowledge intelligent system | |
| CN109508344A (en) | Business datum querying method, device, electronic equipment and storage medium | |
| CN109740873A (en) | Distribution method, device, medium and the electronic equipment of declaration form | |
| CN111489201A (en) | Method, device and storage medium for analyzing customer value | |
| CN110232487A (en) | A kind of task allocating method and device | |
| CN110889656A (en) | Warehouse rule configuration method and device | |
| CN112330299A (en) | Business process management method, device, equipment and storage medium | |
| CN116468218A (en) | Data processing method and system for production plan scheduling | |
| CN113469704A (en) | System and method for automatically recommending customer service strategy | |
| CN113421037A (en) | Multi-source collaborative construction planning compilation method and device | |
| CN113362102A (en) | Client cable distribution method, system and storage medium | |
| CN106933971B (en) | Data analysis statistical system based on scientific and technological service | |
| CN110110153A (en) | A kind of method and apparatus of node searching | |
| TWI684147B (en) | Cloud self-service analysis platform and analysis method thereof | |
| CN113176873B (en) | Method and device for information management, electronic equipment and medium | |
| CN115618277A (en) | Operation and maintenance work order cost quantification method | |
| CN101140644A (en) | INTEDNET professional technology qualification reporting system | |
| CN114091797A (en) | Intelligent dispatching method and device | |
| Li et al. | Learning to bundle proactively for on-demand meal delivery | |
| CN112418727A (en) | Service assignment method, device, electronic equipment and medium | |
| CN114298474A (en) | Method and device for determining allocation resources, storage medium and electronic device | |
| CN109976271B (en) | Method for calculating information structure order degree by using information representation method | |
| CN112766719A (en) | Task allocation method and device |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination |