mes-client/FileWatcher/AutoAnalysisDeviceKS.cs

using Microsoft.Win32;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Threading;
using System.Windows.Forms;
using System.Xml;
using System.Text;
using System.Net;
using System.Net.Sockets;
using System.Text.RegularExpressions;
using Oracle.ManagedDataAccess.Client;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;

namespace FileWatcher
{
    public partial class AutoAnalysisDeviceKS : Form
    {

        DataHelper dh;

        /// <summary>
        /// 用户编号
        /// </summary>
        string iusercode;
        /// <summary>
        /// 岗位资源
        /// </summary>
        string isource;

        Tip tipform;

        Thread InitDB;

        StringBuilder sql = new StringBuilder();
        /// <summary>
        /// 缓存的文件
        /// </summary>
        public static string CachePath = Environment.GetEnvironmentVariable("windir").Substring(0, 1) + @":/UAS_MES/XmlAnalysor/Cache.xml";
        /// <summary>
        /// 缓存的文件夹
        /// </summary>
        public static string CachePathFolder = Environment.GetEnvironmentVariable("windir").Substring(0, 1) + @":/UAS_MES/XmlAnalysor/";
        /// <summary>
        /// 需要解析的文件名
        /// </summary>
        List<string> _FileName = new List<string>();

        ftpOperater ftp = new ftpOperater();

        public AutoAnalysisDeviceKS()
        {
            tipform = new Tip();
            InitializeComponent();
            StartPosition = FormStartPosition.CenterScreen;
        }

        public AutoAnalysisDeviceKS(string iUserName, string iSource, string iMaster)
        {
            tipform = new Tip();
            InitializeComponent();
            iusercode = iUserName;
            isource = iSource.ToUpper();
            StartPosition = FormStartPosition.CenterScreen;
        }
        string IPAddress = "";

        private void Form1_Load(object sender, EventArgs e)
        {
            tipform.Show();
            CheckForIllegalCrossThreadCalls = false;
            FormBorderStyle = FormBorderStyle.FixedSingle;
            InitDB = new Thread(ConnectDB);
            //添加监控事件
            XmlWatcher.Changed += new FileSystemEventHandler(XmlWatcher_Created);
            //ATEFile.Changed += new FileSystemEventHandler(XmlWatcher_Created);
            SetLoadingWindow stw = new SetLoadingWindow(InitDB, "正在启动程序");
            stw.StartPosition = FormStartPosition.CenterScreen;
            stw.ShowDialog();
            List<string> CacheInf = new List<string>();

            IPHostEntry IpEntry = Dns.GetHostEntry(Dns.GetHostName());
            for (int i = 0; i < IpEntry.AddressList.Length; i++)
            {
                if (IpEntry.AddressList[i].AddressFamily == AddressFamily.InterNetwork)
                    IPAddress = IpEntry.AddressList[i].ToString();
            }
            //获取缓存信息
            try
            {
                FolderPath.Text = BaseUtil.GetCacheData("FolderPath").ToString();
                Device.Text = BaseUtil.GetCacheData("Device").ToString();
                //BackUpFolderPath.Text = BaseUtil.GetCacheData("BackUpFolderPath").ToString();
                //Master.Text = BaseUtil.GetCacheData("Master").ToString();
                //AutoStart.Checked = (bool)BaseUtil.GetCacheData("AutoStart");
            }
            catch (Exception ex) { MessageBox.Show(ex.Message); }
            //获取岗位资源相关信息
            StartWatch.PerformClick();
        }

        private void ConnectDB()
        {
            DataHelper.DBConnectionString = "Connection Timeout=0;Pooling=false;Password=select!#%*(;User ID=N_MES;Pooling=false;Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.3.7)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl)));";
            dh = new DataHelper();
        }

        private void StartWatch_Click(object sender, EventArgs e)
        {
            if (FolderPath.Text == "")
            {
                OperateResult.AppendText("请选择监控文件夹\n");
                return;
            }
            else
            {
                if (!Directory.Exists(FolderPath.Text))
                {
                    OperateResult.AppendText("监控文件夹不存在\n");
                    return;
                }
            }
            XmlWatcher.Path = FolderPath.Text;
            XmlWatcher.EnableRaisingEvents = true;
            XmlWatcher.NotifyFilter = NotifyFilters.FileName | NotifyFilters.LastWrite;
            //设置缓存数据
            BaseUtil.SetCacheData("FolderPath", FolderPath.Text);
            BaseUtil.SetCacheData("Device", Device.Text);
            Timer.Interval = 1000 * 60;
            Timer.Start();
            //设置按钮不可点击
            StartWatch.Enabled = false;
            ChooseFolder.Enabled = false;
            StopWatch.Enabled = true;
            OperateResult.AppendText("开始执行监控\n");
        }

        private void XmlWatcher_Created(object sender, FileSystemEventArgs e)
        {
            OperateResult.AppendText("文件修改:" + e.FullPath + "\n");
            TxtHandleProcess(e.FullPath);
        }

        string nextLine;
        private void TxtHandleProcess(string FileName)
        {

        }

        private void StopWatch_Click(object sender, EventArgs e)
        {
            XmlWatcher.EnableRaisingEvents = false;
            StartWatch.Enabled = true;
            ChooseFolder.Enabled = true;
            StopWatch.Enabled = false;
            OperateResult.AppendText("停止执行监控\n");
        }

        private void Clean_Click(object sender, EventArgs e)
        {
            OperateResult.Clear();
        }

        private void ChooseFolder_Click(object sender, EventArgs e)
        {
            FolderBrowserDialog folder = new FolderBrowserDialog();
            folder.Description = "选择监控文件夹";
            DialogResult result = folder.ShowDialog();
            if (result == DialogResult.OK)
            {
                FolderPath.Text = folder.SelectedPath;
            }
        }

        private void ChooseBackUpFolder_Click(object sender, EventArgs e)
        {
            FolderBrowserDialog folder = new FolderBrowserDialog();
            folder.Description = "选择备份文件夹";
            DialogResult result = folder.ShowDialog();
            if (result == DialogResult.OK)
            {

            }
        }

        private void Form1_FormClosing(object sender, FormClosingEventArgs e)
        {
            string ExitConfirm = MessageBox.Show(this, "确认退出？", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Question).ToString();
            if (ExitConfirm != "Yes")
            {
                WindowState = FormWindowState.Minimized;
                e.Cancel = true;
            }
        }

        private void AutoStart_CheckedChanged(object sender, EventArgs e)
        {
            SetAutoRun();
        }
        private void SetAutoRun()
        {
            if (AutoStart.Checked) //设置开机自启动  
            {
                string path = Application.ExecutablePath;
                RegistryKey rk = Registry.LocalMachine;
                RegistryKey rk2 = rk.CreateSubKey(@"Software\Microsoft\Windows\CurrentVersion\Run");
                rk2.SetValue("FileWatcher.exe", path);
                rk2.Close();
                rk.Close();
            }
            else //取消开机自启动  
            {
                string path = Application.ExecutablePath;
                RegistryKey rk = Registry.LocalMachine;
                RegistryKey rk2 = rk.CreateSubKey(@"Software\Microsoft\Windows\CurrentVersion\Run");
                rk2.DeleteValue("FileWatcher.exe", false);
                rk2.Close();
                rk.Close();
            }
        }

        private void Timer_Tick(object sender, EventArgs e)
        {
            string filePath = FolderPath.Text+ @"\LotLog.csv";
            string connectionString = "Connection Timeout=0;Pooling=false;Password=select!#%*(;User ID=N_MES;Pooling=false;Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.3.7)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl)));";
            // 读取Excel文件
            var data = ReadExcelFile(filePath);
            // 插入Oracle数据库
            InsertDataIntoOracle(data, connectionString);
        }

        static List<Dictionary<string, string>> ReadExcelFile(string filePath)
        {
            var data = new List<Dictionary<string, string>>();

            using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
            {
                XSSFWorkbook workbook = new XSSFWorkbook(file);
                ISheet sheet = workbook.GetSheetAt(0); // 获取第一个工作表

                IRow headerRow = sheet.GetRow(0); // 获取表头行
                int cellCount = headerRow.LastCellNum;

                for (int i = 1; i <= sheet.LastRowNum; i++) // 从第二行开始读取数据
                {
                    IRow row = sheet.GetRow(i);
                    if (row == null) continue;

                    var rowData = new Dictionary<string, string>();

                    for (int j = 0; j < cellCount; j++)
                    {
                        ICell cell = row.GetCell(j);
                        string cellValue = cell?.ToString() ?? string.Empty;
                        string header = headerRow.GetCell(j).ToString();

                        rowData[header] = cellValue;
                    }

                    data.Add(rowData);
                }
            }

            return data;
        }

        static void InsertDataIntoOracle(List<Dictionary<string, string>> data, string connectionString)
        {
            using (OracleConnection connection = new OracleConnection(connectionString))
            {
                connection.Open();

                foreach (var row in data)
                {
                    string query = @"
                        INSERT INTO your_table_name (psl_program, psl_begintime, psl_endtime, psl_stop, psl_repair, psl_boards, psl_waitupper, psl_waitlower, psl_move, psl_points, psl_consumes, psl_plat)
                        VALUES (:psl_program, :psl_begintime, :psl_endtime, :psl_stop, :psl_repair, :psl_boards, :psl_waitupper, :psl_waitlower, :psl_move, :psl_points, :psl_consumes, :psl_plat)";

                    using (OracleCommand command = new OracleCommand(query, connection))
                    {
                        command.Parameters.Add("psl_program", OracleDbType.Varchar2).Value = row["基板名"];
                        command.Parameters.Add("psl_begintime", OracleDbType.Date).Value = DateTime.Parse(row["生产开始时间"]);
                        command.Parameters.Add("psl_endtime", OracleDbType.Date).Value = DateTime.Parse(row["生产完成时间"]);
                        command.Parameters.Add("psl_stop", OracleDbType.Int32).Value = int.Parse(row["出错停止时间(秒)"]);
                        command.Parameters.Add("psl_repair", OracleDbType.Int32).Value = int.Parse(row["出错修复时间(秒)"]);
                        command.Parameters.Add("psl_boards", OracleDbType.Int32).Value = int.Parse(row["完成贴装的拼板数"]);
                        command.Parameters.Add("psl_waitupper", OracleDbType.Int32).Value = int.Parse(row["因上游待机的时间(秒)"]);
                        command.Parameters.Add("psl_waitlower", OracleDbType.Int32).Value = int.Parse(row["因下游待机的时间(秒)"]);
                        command.Parameters.Add("psl_move", OracleDbType.Int32).Value = int.Parse(row["可动率(%)"]);
                        command.Parameters.Add("psl_points", OracleDbType.Int32).Value = int.Parse(row["贴装点数"]);
                        command.Parameters.Add("psl_consumes", OracleDbType.Int32).Value = int.Parse(row["元件消耗数"]);
                        command.Parameters.Add("psl_plat", OracleDbType.Varchar2).Value = row["输送台"];

                        command.ExecuteNonQuery();
                    }
                }
            }
        }
    }
}