everlight.eap/backend/MachineMaterialHandler/MachineMaterialStatusHandler.cs

using System;
using System.Collections.Generic;
using System.IO;
using System.Text;
using NSoup.Nodes;
using NSoup;
using System.Data;
using DllEapEntity.OFILM;
using Cksoft.Data;
using Cksoft.Data.Repository;
using DllEapDal.OFILM;
using System.Linq;
using DllEapEntity;
using System.Configuration;
using Cksoft.Unity.Log4NetConfig;

namespace MachineMaterialHandler
{
    public class MachineMaterialStatusHandler
    {
        private readonly string dbConnectString = "";

        public MachineMaterialStatusHandler()
        {
            this.dbConnectString = ConfigurationManager.AppSettings["ConnectionString"];
        }

        /// <summary>
        /// 分析单个文件并存入数据库
        /// </summary>
        /// <param name="mst">未解析的记录</param>
        /// <param name="errorinfo">错误信息</param>
        /// <returns></returns>
        public int AnalyseHtml(MachineMaterialMst mst, ref string errorinfo)
        {
            try
            {
                if (!File.Exists(mst.FilePath))
                {
                    errorinfo = "文件不存在";
                    return -1;
                }
                FileStream fs = File.OpenRead(mst.FilePath);
                var buffer = new byte[fs.Length];
                fs.Read(buffer, 0, buffer.Length);
                fs.Dispose();
                fs.Close();
                var htmlStr = Encoding.UTF8.GetString(buffer);
                Document htmlDoc = NSoupClient.Parse(htmlStr);
                var tblName = "Material Status";
                var statusTbl = this.AppendDataTable(htmlDoc, tblName);
                var details = statusTbl.ToEntity<MachineMaterialDetail>().ToList();
                tblName = "Error Message";
                var errMsgTbl = this.AppendDataTable(htmlDoc, tblName);
                var errMsgs = errMsgTbl.ToEntity<MaterialErrorMessage>().ToList();
                using (IDatabase db = DbFactory.Base(dbConnectString, DatabaseType.MySql))
                {
                    db.BeginTrans();
                    var sql = $"delete from machinematerialdetail where mstid={mst.Id}";
                    if (db.ExecuteBySql(sql) < 0)
                    {
                        return -1;
                    }
                    sql = $"delete from materialerrormessage where mstid={mst.Id}";
                    if (db.ExecuteBySql(sql) < 0)
                    {
                        return -1;
                    }
                    if (details != null && details.Count > 0)
                    {
                        details.ForEach(item =>
                        {
                            item.MstId = mst.Id;
                            item.MacId = mst.MacId;
                            item.FDate = mst.FDate;
                            item.Shift = mst.Shift;
                            item.Recipe = mst.Recipe;
                            item.RecTime = DateTime.Now;
                            item.ModTime = DateTime.Now;
                        });
                        var detailDal = new MachineMaterialDetailDal(db);
                        if (detailDal.InsertTrans(details) < 0)
                        {
                            db.Rollback();
                            errorinfo = "插入数据表[MachineMaterialDetail]失败";
                            return -1;
                        }

                    }
                    if (errMsgs != null && errMsgs.Count > 0)
                    {
                        errMsgs.ForEach(item =>
                        {
                            item.MstId = mst.Id;
                            item.MacId = mst.MacId;
                            item.FDate = mst.FDate;
                            item.Shift = mst.Shift;
                            item.Recipe = mst.Recipe;
                            item.RecTime = DateTime.Now;
                            item.ModTime = DateTime.Now;
                        });
                        var errMsgDal = new MaterialErrorMessageDal(db);
                        if (errMsgDal.InsertTrans(errMsgs) < 0)
                        {
                            db.Rollback();
                            errorinfo = "插入数据表[MaterialErrorMessage]失败";
                            return -1;
                        }
                    }
                    mst.IsAnalysised = 1;
                    var mstDal = new MachineMaterialMstDal(db);
                    if (mstDal.Update(mst) < 0)
                    {
                        db.Rollback();
                        errorinfo = "更新MachineMaterialMst失败";
                        return -1;
                    }
                    db.Commit();
                }
                return 1;
            }
            catch (Exception ex)
            {
                errorinfo = ex.Message;
                return -1;
            }
        }

        /// <summary>
        /// 查询所有未解析的文件进行解析
        /// </summary>
        public void StartAnalysis()
        {
            string errorinfo = string.Empty;
            IEnumerable<MachineMaterialMst> msts = null;
            using (IDatabase db = DbFactory.Base(dbConnectString, DatabaseType.MySql))
            {
                var mstDal = new MachineMaterialMstDal(db);
                msts = mstDal.Get(" and a.IsAnalysised=-1", ref errorinfo);
            }
            if (msts != null && msts.Count() > 0)
            {
                foreach (var item in msts)
                {
                    if (AnalyseHtml(item, ref errorinfo) > 0)
                    {
                        LogHelper<MacModelProgram>.LogFatal($"文件[{item.FilePath}]解析成功", "文件解析", string.Empty);
                    }
                    else
                    {
                        LogHelper<MacModelProgram>.LogFatal($"文件[{item.FilePath}]解析失败，原因：{errorinfo}", "文件解析", string.Empty);
                    }
                }
            }

        }

        /// <summary>
        /// 将HTML解析成DataTable
        /// </summary>
        /// <param name="doc">html文档</param>
        /// <param name="title">表格名称</param>
        /// <returns></returns>
        public DataTable AppendDataTable(Document doc, string title)
        {
            var titles = doc.GetElementsByTag("h4");
            Element table = null;
            if (titles != null && titles.Count > 0)
            {
                foreach (var t in titles)
                {
                    if (t.Text() == title)
                    {
                        table = t.NextElementSibling;
                        break;
                    }
                }
            }
            if (table == null)
                return null;
            var dataTable = new DataTable(title);
            foreach (var item in table.Children)
            {
                var trs = item.GetElementsByTag("tr");
                if (trs != null && trs.Count > 0)
                {
                    for (int j = 0; j < trs.Count; j++)
                    {
                        if (j == 0)
                        {
                            var headerRow = trs[j];
                            foreach (var th in headerRow.Children)
                            {
                                DataColumn col = new DataColumn();
                                var colName = th.Text();
                                if (string.IsNullOrEmpty(colName))
                                {
                                    colName = "Category";
                                }
                                else if (colName.Contains("%"))
                                {
                                    colName = colName.Replace("%", "");
                                }
                                else if (colName == "Total idle time(s)")
                                {
                                    colName = "TotalIdleTime";
                                }
                                //col.ColumnName = string.IsNullOrEmpty(th.Text()) ? "Category" : th.Text();
                                col.ColumnName = colName;
                                dataTable.Columns.Add(col);
                            }
                        }
                        else
                        {
                            var tBody = trs[j];
                            DataRow row = dataTable.NewRow();
                            var colIndex = 0;
                            foreach (var td in tBody.Children)
                            {
                                var value = td.Text();
                                if (value.Contains("%"))
                                {
                                    value = value.Replace("%", "");
                                }
                                row[colIndex] = value;
                                colIndex++;
                            }
                            dataTable.Rows.Add(row);
                        }
                    }
                }
            }
            return dataTable;
        }

        /// <summary>
        /// 将HTML解析成DataTable
        /// </summary>
        /// <param name="doc">html文档</param>
        /// <param name="index">表格所在下标</param>
        /// <param name="tblName">datatable名称</param>
        /// <returns></returns>
        public DataTable AppendDataTable(Document doc, int index, string tblName)
        {
            var table = doc.GetElementsByTag("table").Eq(index);
            var dataTable = new DataTable(tblName);
            foreach (var item in table)
            {
                var trs = item.GetElementsByTag("tr");
                if (trs != null && trs.Count > 0)
                {
                    for (int j = 0; j < trs.Count; j++)
                    {
                        if (j == 0)
                        {
                            var headerRow = trs[j];
                            foreach (var th in headerRow.Children)
                            {
                                DataColumn col = new DataColumn();
                                col.ColumnName = string.IsNullOrEmpty(th.Text()) ? "Category" : th.Text();
                                dataTable.Columns.Add(col);
                            }
                        }
                        else
                        {
                            var tBody = trs[j];
                            DataRow row = dataTable.NewRow();
                            var colIndex = 0;
                            foreach (var td in tBody.Children)
                            {
                                row[colIndex] = td.Text();
                                colIndex++;
                            }
                            dataTable.Rows.Add(row);
                        }
                    }
                }
            }
            return dataTable;
        }
    }
}