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();
            }
            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 * 5;
            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";
            // 插入Oracle数据库
            List<string[]> csvData = ReadAndFilterCsvFile(filePath);
            // 2. 解析数据并写入Oracle数据库
            WriteToOracleDatabase(csvData);
        }

        static List<string[]> ReadAndFilterCsvFile(string filePath)
        {
            var filteredData = new List<string[]>();
            DateTime currentTime = DateTime.Now;
            DateTime fiveMinutesAgo = currentTime.AddMinutes(-1440);

            // 使用StreamReader自动检测编码
            using (var reader = new StreamReader(filePath, Encoding.Default, true))
            {
                Console.WriteLine($"检测到文件编码: {reader.CurrentEncoding.EncodingName}");

                // 跳过第一行（版本信息行）
                reader.ReadLine();

                // 读取标题行
                string headerLine = reader.ReadLine();
                if (headerLine == null)
                {
                    throw new Exception("CSV文件格式不正确，缺少标题行");
                }

                // 确定生产开始时间的列索引
                string[] headers = headerLine.Split(',');
                int startTimeIndex = Array.IndexOf(headers, "生产开始时间");
                if (startTimeIndex == -1)
                {
                    throw new Exception("CSV文件中找不到'生产开始时间'列");
                }

                // 读取并过滤数据行
                while (!reader.EndOfStream)
                {
                    string line = reader.ReadLine();
                    if (!string.IsNullOrWhiteSpace(line))
                    {
                        string[] values = line.Split(',');

                        // 解析生产开始时间
                        if (startTimeIndex < values.Length &&
                            DateTime.TryParse(values[startTimeIndex], out DateTime startTime))
                        {
                            // 只保留生产开始时间在当前时间5分钟以内的记录
                            filteredData.Add(values);
                        }
                    }
                }
            }
            return filteredData;
        }

        public static Encoding DetectFileEncoding(string filePath)
        {
            // 读取文件前几个字节来检测编码
            byte[] buffer = new byte[5];
            using (FileStream file = new FileStream(filePath, FileMode.Open))
            {
                file.Read(buffer, 0, 5);
                file.Close();
            }

            // 检测UTF-8 with BOM
            if (buffer[0] == 0xEF && buffer[1] == 0xBB && buffer[2] == 0xBF)
                return Encoding.UTF8;

            // 检测UTF-16 (Big Endian)
            if (buffer[0] == 0xFE && buffer[1] == 0xFF)
                return Encoding.BigEndianUnicode;

            // 检测UTF-16 (Little Endian)
            if (buffer[0] == 0xFF && buffer[1] == 0xFE)
                return Encoding.Unicode;

            // 默认使用GB2312（中文常用编码）
            return Encoding.GetEncoding("GB2312");
        }


         void WriteToOracleDatabase(List<string[]> csvData)
        {
            // Oracle连接字符串 - 根据实际情况修改
            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)));";

            // 字段索引映射（根据CSV标题行顺序调整）
            const int BOARD_NAME_INDEX = 1;         // 基板名
            const int START_TIME_INDEX = 4;         // 生产开始时间
            const int END_TIME_INDEX = 6;           // 生产完成时间
            const int ERROR_STOP_TIME_INDEX = 45;   // 出错停止时间(秒)
            const int ERROR_REPAIR_TIME_INDEX = 46; // 出错修复时间(秒)
            const int COMPLETED_PANELS_INDEX = 48;  // 完成贴装的拼板数
            const int UPSTREAM_WAIT_INDEX = 49;     // 因上游待机的时间(秒)
            const int DOWNSTREAM_WAIT_INDEX = 50;   // 因下游待机的时间(秒)
            const int EFFICIENCY_INDEX = 51;        // 可动率(%)
            const int PLACEMENT_POINTS_INDEX = 52;  // 贴装点数
            const int COMPONENT_CONSUMP_INDEX = 53; // 元件消耗数
            const int CONVEYOR_INDEX = 54;          // 输送台

            using (OracleConnection connection = new OracleConnection(connectionString))
            {
                try
                {
                    connection.Open();

                    // 使用参数化SQL防止注入
                    string insertSql = @"
                    INSERT INTO productsmtlocation_1 (psl_id,
                        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 (productsmtlocation_seq.nextval,
                        :boardName, :startTime, :endTime, :errorStopTime, 
                        :errorRepairTime, :completedPanels, :upstreamWaitTime, 
                        :downstreamWaitTime, :efficiency, :placementPoints, 
                        :componentConsumption, :conveyor
                    )";

                    using (OracleCommand command = new OracleCommand(insertSql, connection))
                    {
                        // 添加参数
                        command.Parameters.Add(":boardName", OracleDbType.Varchar2);
                        command.Parameters.Add(":startTime", OracleDbType.TimeStamp);
                        command.Parameters.Add(":endTime", OracleDbType.TimeStamp);
                        command.Parameters.Add(":errorStopTime", OracleDbType.Double);
                        command.Parameters.Add(":errorRepairTime", OracleDbType.Double);
                        command.Parameters.Add(":completedPanels", OracleDbType.Int32);
                        command.Parameters.Add(":upstreamWaitTime", OracleDbType.Double);
                        command.Parameters.Add(":downstreamWaitTime", OracleDbType.Double);
                        command.Parameters.Add(":efficiency", OracleDbType.Double);
                        command.Parameters.Add(":placementPoints", OracleDbType.Int32);
                        command.Parameters.Add(":componentConsumption", OracleDbType.Int32);
                        command.Parameters.Add(":conveyor", OracleDbType.Varchar2);

                        // 遍历CSV数据行
                        for (int i = 0; i < csvData.Count; i++)
                        {   
                            string[] values = csvData[i];

                            try
                            {
                                // 设置参数值
                                command.Parameters[":boardName"].Value = GetStringValue(values, BOARD_NAME_INDEX);
                                command.Parameters[":startTime"].Value = GetDateTimeValue(values, START_TIME_INDEX);
                                command.Parameters[":endTime"].Value = GetDateTimeValue(values, END_TIME_INDEX);
                                command.Parameters[":errorStopTime"].Value = GetDoubleValue(values, ERROR_STOP_TIME_INDEX);
                                command.Parameters[":errorRepairTime"].Value = GetDoubleValue(values, ERROR_REPAIR_TIME_INDEX);
                                command.Parameters[":completedPanels"].Value = GetIntValue(values, COMPLETED_PANELS_INDEX);
                                command.Parameters[":upstreamWaitTime"].Value = GetDoubleValue(values, UPSTREAM_WAIT_INDEX);
                                command.Parameters[":downstreamWaitTime"].Value = GetDoubleValue(values, DOWNSTREAM_WAIT_INDEX);
                                command.Parameters[":efficiency"].Value = GetDoubleValue(values, EFFICIENCY_INDEX);
                                command.Parameters[":placementPoints"].Value = GetIntValue(values, PLACEMENT_POINTS_INDEX);
                                command.Parameters[":componentConsumption"].Value = GetIntValue(values, COMPONENT_CONSUMP_INDEX);
                                command.Parameters[":conveyor"].Value = GetStringValue(values, CONVEYOR_INDEX);
                                if (!dh.CheckExist("productsmtlocation_1", 
                                    "to_char(PSL_BEGINTIME,'yyyy-mm-dd hh24:mi:ss')='"+ GetDateTimeValue(values, START_TIME_INDEX).ToString("yyyy-MM-dd HH:mm:ss") + "' " +
                                    "and psl_plat='"+ GetStringValue(values, CONVEYOR_INDEX) + "'"))
                                {
                                    int rowsAffected = command.ExecuteNonQuery();
                                    OperateResult.AppendText($"已插入记录 {i + 1}/{csvData.Count}: {GetStringValue(values, BOARD_NAME_INDEX)}\n");
                                }
                                // 执行插入
                            }
                            catch (Exception ex)
                            {
                                Console.WriteLine($"插入记录 {i + 1} 失败: {ex.Message}");
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    Console.WriteLine($"数据库错误: {ex.Message}");
                }
            }
        }

        static double? GetDoubleValue(string[] values, int index)
        {
            if (values == null || index >= values.Length || string.IsNullOrWhiteSpace(values[index]))
                return null;

            double result;
            return double.TryParse(values[index], out result) ? result : (double?)null;
        }

        static int? GetIntValue(string[] values, int index)
        {
            if (values == null || index >= values.Length || string.IsNullOrWhiteSpace(values[index]))
                return null;

            int result;
            return int.TryParse(values[index], out result) ? result : (int?)null;
        }

        static string GetStringValue(string[] values, int index)
        {
            return (values != null && index < values.Length) ? values[index] : null;
        }

        static DateTime GetDateTimeValue(string[] values, int index)
        {
            DateTime result;
             DateTime.TryParse(values[index], out result);
            return result;
        }
    }
}