Application of.Net open source framework in industrial production

One 、 background

        At present, the country vigorously promotes the integration of industrialization and informatization , In this good environment “ Integration of the two ” Get rapid development .

        Over the past decade .NET Technology has played an important role in the process of coal mine industrialization , In particular, almost all kinds of safety production auxiliary systems in coal mines are .NET product , Stable operation for more than ten years with few errors , Silently escort the development of coal mine safety . Different from Internet products , Coal mine products pursue safety 、 Stable 、 Less iteration 、 Ease of use and simple maintenance . At present liunx The use of servers is growing explosively , Coal mines are no exception .

       .NET Core The framework has High performance 、 Cross platform 、 Flexible deployment 、 Good compatibility, Microsoft maintenance and other features . add 2014 Open source since ,6 In a matter of years .NET Core Various excellent open source frameworks are constantly complemented .NET In various technical scenarios , The ecology is constantly improving . therefore , choose .NET Core Development of framework operation coal mine business system , Just meet all kinds of needs .

       2020 A year when the virus was rampant , It is also an unprecedented year for information technology .  This year is also a challenging year for our team , Received the data project of this major . With excitement and challenge , We use pure .NET Technology has successfully developed a complete set of AI Data analysis products , It has been affirmed by all parties 、 Awards and papers issued by China Coal Mining Industry Association , Stick to it for us .NET technology , Strengthened confidence .

Two 、 customer demand

       XXX company 2018 Purchased a set of monitoring system , Monitor the environmental data of the operation site . Number of probes 500-700 about .XXX The company requires every 1 Minutes to collect data 、 Put in storage , Provide daily data query and generate curves and 1 Hourly time series prediction , The reports generated by the Department also need to be uploaded . meanwhile , According to industry standards , Convert some probe data into standard data with the uploaded report , Production day 、 Zhou 、 Monthly report and one week AI Data forecast report . Real time data generated by the system 、 Historical data and forecast data are required to be collected and can be viewed .

3、 ... and 、 Business analysis

      According to the needs of customers , The focus of our processing system is ： Massive data storage 、 Data transformation ETL、 real time 、 Periodic data query and AI Data analysis . Then our business model will be clear , The business process is as follows .     

        Store a large number of probe historical data in the historical database by database and table , Report data is saved in business system data , According to the relationship between probe historical data and report data ETL To the data warehouse . The data warehouse passes sql Generate analysis data set , utilize .NET Of AI Analysis Library ML.NET and TensorFlow.NET Provide algorithms for AI forecast , The final will be AI The prediction results are written into the database of the business system .

Four 、 Technology selection     

       1、xml Document processing

        There is special data exchange software in the original monitoring system , The format of generated exchange data is xml. We only need to correctly parse the data according to the agreement documents provided by the other party .xml The data is mainly 2 File , One is that the basic information of the device says that the file has a timestamp （SBM202012291653.XML）, One is that the device data value file has a timestamp （SJZ202012291653.XML）.

       1）xml Data processing , I use the strategy of deleting after parsing . Scan the file first , Classify documents according to the document header , First delete the timestamp and copy it to another folder , Write in sequence according to the timestamp , Delete the data immediately after writing .

       2） Prepare basic information xml file , Because of the basic information xml The file change frequency is low, so the overlay storage method is adopted （ The latest data is always in the table ）.

       3） Device data value xml file , Then it is stored according to the sequence .

       2、 Historical data storage

        The data volume is calculated as 60 minute *24 Hours *800 individual （ Than XXX The company gave more probes 100 individual ）= 1152000 Data , About a day 115 Ten thousand data . I use the daily timetable here 、 Monthly sub Treasury 、 Annual sub folder strategy for storage . Choose the new team's open source NewLife.XCode Database middleware stores data in different databases and tables .NewLife.XCode Support at the same time Fx and Core, Database support Oracle、SqlServer、MySql、SQLite And other mainstream relational databases . What I'm using here is sqlite Store historical data . Make data acquisition Windows service , Set the service to auto start and auto restart . Portal ：XCode GitHub The source code library . Portal 2：XCode  Use tutorial blog .

       1） The rules of sub Treasury ：DataBaseName_yyyyMM The naming method is used for sub database operation .

       2） Table rules ：TableName_yyyyMMdd The naming method is used for table splitting .

       3） Equipment data duty table ： Write accumulatively according to the data actually collected on that day .

       4） Daily table of basic equipment information ： Then write according to the latest equipment information underground coverage .

       5） Temporary table of equipment data values ： Store only 2-3 Probe value data of days .

       3、 Business 、 Report data storage

        The business system contains Excel Data collection , And consider the separation of reading and writing , So we use WTM Framework to build business systems .WTM frame , Support one click generation, addition, deletion, modification and query , Import and export , Batch operation code 、 Support separation (React+AntD,Vue+Element) And not separate (LayUI) Two modes 、 Provides users , role , User group , menu , Log and other common modules 、 Support read-write separation and database sub library . We use Sql server As the main database of the business system . Portal 3：WTM Github The source code library . Portal 4：WTM Using document .

       1） Try to design the watch according to the original Excel Model design based on the style of , utilize WTM The code generator automatically generates CURD page 、 With error message Excel Import pages and Export , If the report is complex , use WTM Built in Excel Processing library for processing .

       2） stay appsettings.json Of ConnectionStrings according to WTM Database configuration rules , To configure SQL server Separation of reading and writing .  Portal 5：SQL server Subscription distribution configuration .

       3）WTM Provided cookie and JWT Hybrid identity authentication mechanism ,swagger And so on to help you develop quickly .

       4、 data ETL

        I understand. ETL Data conversion , According to the business situation, extract the data in other tables into the data warehouse , Then, according to certain conditions, re assemble a new table . The feature of the new table is that compared with the previous business table , There are obviously many fields . For example, the previous salesman table may only have data of one department , after ETL After that, the data of many units are collected in the new table according to conditions , Even the values of some data are recalculated and changed . here ETL I use ant scheduling AntJob frame .AntJob .NET Framework distributed task scheduling system .AntJob The core is Ant algorithm ： Split any big data into small pieces , The ant moving strategy is used to calculate each piece ! pure .NET Build a real-time computing platform for big data . Portal 6：AntJob Github The source code library .  Portal 7：AntJob Use the tutorial .

         5、AI forecast

        Here I choose ML.NET、 and TensorFlow.NET For providing AI Algorithm model .  Portal 8：ML.NET Use the tutorial .   Portal 9：TensorFlow.NET Github The source code library .  Portal 10：TensorFlow.NET Use the tutorial .   I am here. AI The calculation scenario is 2 Different numerical predictions , One is to predict the time sequence of the probe data , A multi condition analysis （ Multiple conditions are calculation 、 Analyze the number of a column ）.

        1） Timing prediction ： It's easy to understand , Data only has time and value 2 Dimensions . Here, if the probe value is affected by the operation at different times under normal circumstances . So if it's not a special data situation , The temporary table designed above is used to store 2-3 The probe historical data of days will work , Use the timing algorithm to predict the timing of the probe . But this algorithm , Personally, I think the accuracy is a little low .

        2） neural network / Multiple regression ： this 2 Both algorithms can be used for value prediction , Specifically, when looking at the test, the root mean square error evaluated by the algorithm model and the data error after fitting . Generally, the smaller the root mean square error, the better , But the actual situation has the phenomenon of over fitting . therefore , After the model is made , Use the test data to fit the measured data with the predicted data , see 2 The overall trend and calculation of this curve is the largest 、 Minimum and average error . After choosing the algorithm , Use the task framework to calculate offline after midnight according to the cycle .

       2.1） Every calculation reevaluates the model , If the index meets the standard, use the model , If not, recalculate . Calculate the number of times to set the upper limit , If the number of calculations reaches the upper limit, the failure record will be recorded , Adjust the model with manual intervention .

       2.2） The calculation results are written directly into the business system database , The prediction data is stored in binary Json character string . It is convenient for the business system to render and display the data prediction results .

       2.3） May adopt ML.NET AutoML Perform automatic algorithm tuning or automatic algorithm selection .

       2.4） Provide data set selection and self-service upload forecast environment data set function page , Users can do it online according to their own needs AI Calculation .

      stay 2 More than months of running time , The system automatically 8 Time AI Mission （ Once a week ）, by 30 Value prediction at multiple operation sites , The error rate is 0.2~9.8% Between , Meet customer needs . There is only 2 Second largest error . this 2 The secondary error is in the recalculation function provided by the system , All meet the prediction and calculation requirements after recalculation .

5、 ... and 、 summary     

        This system development , From data collection 、 data ETL、 Business system construction and AI analysis ,.NET Core There are corresponding technology stacks , For developers , And because the Convention is greater than the configuration , It saves a lot of learning time , Use C# Language is easy to develop . Business problems , We are also .NET Ask in the Technology Group , Generally, enthusiastic group friends come out to solve , The boss who is really difficult to solve comes out to provide ideas or help to solve the problem debug, Personal sense of belonging to find ideas , Finally complete the research and development of this system . at present ,.NET Core It can already run in Godson CPU, In the future, the process of national industrialization is approaching step by step . hug .NET Put into localization operation .