MES / MES-BaseMes10.0 · Files

using HHWelding.WeldingTask;
using Microsoft.Win32;
using System.Windows;
using System.Data;
using OfficeOpenXml;
using System.IO;
using System.Windows.Controls;
using Microsoft.VisualBasic.FileIO;
using System.Text;
using NPOI.HPSF;
using System.Configuration;
using CsvHelper;
using System.Globalization;
using System.Collections.Generic;
using OfficeOpenXml.Drawing;
namespace HHWelding
{
    /// <summary>
    /// WinMain.xaml 的交互逻辑
    /// </summary>
    public partial class WinMain : Window
    {
        /// <summary>
        /// 构造函数
        /// </summary>
        string connectionString;
        /// <summary>
        ///数据下发MES线程
        /// </summary>
        Thread mThread;
        /// <summary>
        /// 安装图下发MES线程
        /// </summary>
        Thread nThread;
        public WinMain()
        {
            InitializeComponent();
        }

        private void Import_Click(object sender, RoutedEventArgs e)
        {
            var config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
            var appSettings = config.AppSettings.Settings;
            // 读取ConnectionString配置值
            if (appSettings["PathCsv"] != null)
            {
                connectionString = appSettings["PathCsv"].Value;
            }
            else
            {
                MessageBox.Show("配置表无数据");
            }
            //焊道
            string handao = connectionString + @"\直管段明细表.csv";
            //焊口
            string hankou = connectionString + @"\焊口表.csv";
            //切割--暂无
            string qiege = connectionString + "";
            //string[] strings = { hankou,handao  };
            List<Dictionary<string, string>> list1 = ReadCsvToList(handao);
            List<Dictionary<string, string>> list2 = ReadCsvToList(hankou);
            List<Dictionary<string, string>> joinedData = JoinLists(list1, list2);
            BindJoinedDataToDataGrid(joinedData);
        }
        /// <summary>
        /// 数据按钮
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void Shuju_Click(object sender, RoutedEventArgs e)
        {
            Configuration configuration = new Configuration();
            configuration.ShowDialog();
        }
            /// <summary>
            /// csvz转list
            /// </summary>
            /// <param name="csvFilePaths"></param>
            /// <returns></returns>
            private List<object[]> ConvertCsvFilesToList(string[] csvFilePaths)
        {
            System.Text.Encoding.RegisterProvider(System.Text.CodePagesEncodingProvider.Instance);
            Encoding gbk = Encoding.GetEncoding("GBK");
            List<object[]> listData = new List<object[]>();
            foreach (string filePath in csvFilePaths)
            {
                using (TextFieldParser parser = new TextFieldParser(filePath,gbk))
                {
                    parser.TextFieldType = FieldType.Delimited;
                    parser.SetDelimiters(",");
                    while (!parser.EndOfData)
                    {
                        string[] rowData = parser.ReadFields();
                        listData.Add(rowData);
                    }
                }
            }
            return listData;
        }
        /// <summary>
        /// 线程接口
        /// </summary>
        public void ReceiveTask()
        {
            if (mThread == null)
               mThread = new Thread(new ThreadStart(mThradRun));//下发mes接口
            if (!mThread.IsAlive)
            {
                mThread.IsBackground = false;
                mThread.Start();
            }
            if (nThread == null)
             nThread = new Thread(new ThreadStart(nThradRun));//下发mes图片接口
            if (!nThread.IsAlive)
            {
                nThread.IsBackground = false;
                nThread.Start();
            }
        }
        
          

  
        /// <summary>
        /// 下发接口
        /// </summary>
        public void mThradRun()
        {
            while (true)
            {
                try
                {
                 
                    //实现方法
                }
                catch (Exception ex)
                {
                    MessageBox.Show("下发MES数据错误"+ex.Message);
                }
                finally
                {
                    Thread.Sleep(2017);
                }
            }
        }
        /// <summary>
        /// 图片接口
        /// </summary>
        public void nThradRun()
        {
            while (true)
            {
                try
                {
                    //nThreadMtd();//实现方法
                }
                catch (Exception ex)
                {
                    MessageBox.Show("下发MES图片数据错误" + ex.Message);
                }
                finally
                {
                    Thread.Sleep(2017);
                }

            }
        }
        /// <summary>
        /// 转list导入datagrid
        /// </summary>
        /// <returns></returns>
        private List<Dictionary<string, string>> ReadCsvToList(string filePath)
        {
            List<Dictionary<string, string>> listData = new List<Dictionary<string, string>>();
            System.Text.Encoding.RegisterProvider(System.Text.CodePagesEncodingProvider.Instance);
            Encoding gbk = Encoding.GetEncoding("GBK");
            using (var reader = new StreamReader(filePath,gbk))
            using (var csv = new CsvHelper.CsvReader(reader, CultureInfo.InvariantCulture))
            {
                csv.Read();
                csv.ReadHeader();
                var headers = csv.HeaderRecord;
                while (csv.Read())
                {
                    Dictionary<string, string> rowData = new Dictionary<string, string>();
                    for (int i = 0; i < headers.Length; i++)
                    {
                        rowData[headers[i]] = csv.GetField(i);
                    }
                    listData.Add(rowData);
                }
            }
            return listData;
        }
        /// <summary>
        /// 多个list联表查询
        /// </summary>
        /// <param name="lists"></param>
        /// <returns></returns>
        private List<Dictionary<string, string>> JoinLists(List<Dictionary<string, string>> list1, List<Dictionary<string, string>> list2)
        {
            var joinedList = from row2 in list2
                             join row1 in list1 on row2["管段编号"] equals row1["管段号"]
                             into aaa
                             from ccc in aaa.DefaultIfEmpty()  

            select new Dictionary<string, string>()
                     {
                         {"焊点编号", row2["焊点编号"]},
                         {"管线号", row2.ContainsKey("管线号")? row2["管线号"] : ""},
                         {"页码", row2.ContainsKey("页码")? row2["页码"] : ""},
                         {"管段编号", row2.ContainsKey("管段编号")? row2["管段编号"] : ""},
                         {"公称直径", row2.ContainsKey("公称直径")? row2["公称直径"] : ""},
                         {"焊点外径", row2.ContainsKey("焊点外径")? row2["焊点外径"] : ""},
                         {"英制", row2.ContainsKey("英制")? row2["英制"] : ""},
                         {"吋径", row2.ContainsKey("吋径")? row2["吋径"] : ""},
                         {"壁厚系列1", row2.ContainsKey("壁厚系列1")? row2["壁厚系列1"] : ""},
                         {"壁厚系列2", row2.ContainsKey("壁厚系列2")? row2["壁厚系列2"] : ""},
                         {"材质1", row2.ContainsKey("材质1")? row2["材质1"] : ""},
                         {"材质2", row2.ContainsKey("材质2")? row2["材质2"] : ""},
                         {"焊点类型", row2.ContainsKey("焊点类型")? row2["焊点类型"] : ""},
                         {"焊接类型", row2.ContainsKey("焊接类型")? row2["焊接类型"] : ""},
                         {"管路等级", row2.ContainsKey("管路等级")? row2["管路等级"] : ""},
                         {"热处理", row2.ContainsKey("热处理")? row2["热处理"] : ""},
                         {"管道级别", row2.ContainsKey("管道级别")? row2["管道级别"] : ""},
                         {"焊接材料1", row2.ContainsKey("焊接材料1")? row2["焊接材料1"] : ""},
                         {"描述1", row2.ContainsKey("描述1")? row2["描述1"] : ""},
                         {"焊接材料2", row2.ContainsKey("焊接材料2")? row2["焊接材料2"] : ""},
                         {"描述2", row2.ContainsKey("描述2")? row2["描述2"] : ""},
                         {"焊点位置", row2.ContainsKey("焊点位置")? row2["焊点位置"] : ""},
                         {"焊点坐标(焊缝两侧管子坐标)", row2.ContainsKey("焊点坐标(焊缝两侧管子坐标)")? row2["焊点坐标(焊缝两侧管子坐标)"] : ""},
                         {"管段长度", ccc?.ContainsKey("管段长度")??false? ccc["管段长度"] : ""},
                         {"管线号1", ccc?.ContainsKey("管线号1")??false? ccc["管线号1"] : ""},
                         {"名称", ccc?.ContainsKey("名称")??false? ccc["名称"] : ""},
                         {"材料代码", ccc?.ContainsKey("材料代码")??false? ccc["材料代码"] : ""},
                         {"备注（描述）", ccc?.ContainsKey("备注（描述）")??false? ccc["备注（描述）"] : ""},
                         {"单位", ccc?.ContainsKey("单位")??false? ccc["单位"] : ""},
                         {"管段数量", ccc?.ContainsKey("管段数量")??false? ccc["管段数量"] : ""},
                         {"操作温度", ccc?.ContainsKey("操作温度")??false? ccc["操作温度"] : ""},
                         {"设计温度", ccc?.ContainsKey("设计温度")??false? ccc["设计温度"] : ""},
                         {"隔热要求", ccc?.ContainsKey("隔热要求")??false? ccc["隔热要求"] : ""},

                         // 根据两个CSV文件实际的列名添加更多字段映射
                     };
           return joinedList.ToList();
            //return new();
        }
        /// <summary>
        /// 转换成datagrid
        /// </summary>
        /// <param name="joinedData"></param>
        private void BindJoinedDataToDataGrid(List<Dictionary<string, string>> joinedData)
        {
            DataTable dataTable = new DataTable();
            if (joinedData.Count > 0)
            {
                // 根据联表结果的第一行字典的键（列名）来构建DataTable的列
                foreach (var key in joinedData[0].Keys)
                {
                    dataTable.Columns.Add(key);
                }
                // 填充DataTable的数据行
                foreach (var rowData in joinedData)
                {
                    DataRow dataRow = dataTable.NewRow();
                    foreach (var col in dataTable.Columns)
                    {
                        var colName = col.ToString();
                        if (rowData.ContainsKey(colName))
                        {
                            dataRow[colName] = rowData[colName];
                        }
                        else
                        {
                            dataRow[colName] = DBNull.Value;
                        }
                    }
                    dataTable.Rows.Add(dataRow);
                }
            }
            // 将DataTable作为数据源绑定到DataGrid
            DataGrid dataGrid = this.FindName("Datagrid1") as DataGrid; // 假设DataGrid的名字是dataGrid，根据实际情况修改
            if (dataGrid != null)
            {
                dataGrid.ItemsSource = dataTable.DefaultView;
            }
        }
        private void DateReturn_Click(object sender, RoutedEventArgs e)
        {
            if (Datagrid1.Items.Count == 0)
            {
                MessageBox.Show("暂无数据允许上传MES");
            }
            DataSet ds = new DataSet();
            DataTable table = ConvertDataGridToDataTable(Datagrid1);
            ds.Tables.Add(table);
            WelddingPost welddingPost = new WelddingPost();
            welddingPost.GetDistinctValuesFromDataGrid(ds);

        }

        private void ReadAndAssignMultipleCsvToDataGrid(string[] csvFilePaths)
        {
            // 用于存储所有CSV文件的数据（以字典形式，键为列名，值为对应单元格的值）
            List<Dictionary<string, string>> allData = new List<Dictionary<string, string>>();
            System.Text.Encoding.RegisterProvider(System.Text.CodePagesEncodingProvider.Instance);
            Encoding gbk = Encoding.GetEncoding("GBK");
            foreach (string filePath in csvFilePaths)
            {
                using (var reader = new StreamReader(filePath,gbk))
                using (var csv = new CsvReader(reader, CultureInfo.InvariantCulture))
                {
                    // 读取CSV文件头，获取列名
                    csv.Read();
                    csv.ReadHeader();
                    var headers = csv.HeaderRecord;

                    while (csv.Read())
                    {
                        Dictionary<string, string> rowData = new Dictionary<string, string>();
                        for (int i = 0; i < headers.Length; i++)
                        {
                            rowData[headers[i]] = csv.GetField(i);
                        }
                        allData.Add(rowData);
                    }
                }
            }

            // 构建DataTable和DataGrid列，并填充数据
            DataTable dataTable = new DataTable();
            foreach (var firstRow in allData)
            {
                if (firstRow.Count > 0)
                {
                    // 以第一个有数据的行的字典键（列名）作为DataTable的列名添加列
                    foreach (var key in firstRow.Keys)
                    {
                        dataTable.Columns.Add(key);
                    }
                    break;
                }
            }

            foreach (var rowData in allData)
            {
                DataRow dataRow = dataTable.NewRow();
                foreach (var col in dataTable.Columns)
                {
                    var colName = col.ToString();
                    if (rowData.ContainsKey(colName))
                    {
                        dataRow[colName] = rowData[colName];
                    }
                    else
                    {
                        dataRow[colName] = DBNull.Value; // 如果该行数据中没有对应列的值，设置为空值
                    }
                }
                dataTable.Rows.Add(dataRow);
            }

            // 将DataTable作为数据源绑定到DataGrid
            DataGrid dataGrid = this.FindName("Datagrid1") as DataGrid; // 假设DataGrid的名字是dataGrid，根据实际情况修改
            if (dataGrid != null)
            {
                dataGrid.ItemsSource = dataTable.DefaultView;
            }
        }

        private void ReadAndAssignMultipleCsvToDataGrid2(string[] csvFilePaths)
        {
            // 用于存储所有去重后的列名
            List<string> uniqueColumnNames = new List<string>();
            System.Text.Encoding.RegisterProvider(System.Text.CodePagesEncodingProvider.Instance);
            Encoding gbk = Encoding.GetEncoding("GBK");
            // 用于存储所有CSV文件的数据（以字典形式，键为列名，值为对应单元格的值）
            List<Dictionary<string, string>> allData = new List<Dictionary<string, string>>();
            foreach (string filePath in csvFilePaths)
            {
                using (var reader = new StreamReader(filePath,gbk))
                using (var csv = new CsvReader(reader, CultureInfo.InvariantCulture))
                {
                    // 读取CSV文件头，获取列名
                    csv.Read();
                    csv.ReadHeader();
                    var headers = csv.HeaderRecord;
                    // 提取当前文件的列名，添加到去重列表中
                    foreach (var header in headers)
                    {
                        if (!uniqueColumnNames.Contains(header))
                        {
                            uniqueColumnNames.Add(header);
                        }
                    }

                    while (csv.Read())
                    {
                        Dictionary<string, string> rowData = new Dictionary<string, string>();
                        for (int i = 0; i < headers.Length; i++)
                        {
                            rowData[headers[i]] = csv.GetField(i);
                        }
                        allData.Add(rowData);
                    }
                }
            }

            // 构建DataTable和DataGrid列，并填充数据
            DataTable dataTable = new DataTable();
            foreach (var columnName in uniqueColumnNames)
            {
                dataTable.Columns.Add(columnName);
            }

            foreach (var rowData in allData)
            {
                DataRow dataRow = dataTable.NewRow();
                foreach (var col in dataTable.Columns)
                {
                    var colName = col.ToString();
                    if (rowData.ContainsKey(colName))
                    {
                        dataRow[colName] = rowData[colName];
                    }
                    else
                    {
                        dataRow[colName] = DBNull.Value; // 如果该行数据中没有对应列的值，设置为空值
                    }
                }
                dataTable.Rows.Add(dataRow);
            }

            // 将DataTable作为数据源绑定到DataGrid
            DataGrid dataGrid = this.FindName("Datagrid1") as DataGrid; // 假设DataGrid的名字是dataGrid，根据实际情况修改
            if (dataGrid != null)
            {
                dataGrid.ItemsSource = dataTable.DefaultView;
            }
        }
        /// <summary>
        /// 合并所有的csv格式到datagrid里
        /// </summary>
        private void MergeAndImportCsvFilesToDataGrid(string[] csvFilePaths)
            {
            DataTable dataTable = new DataTable();
            bool isFirstFile = true;
            List<string[]> allRows = new List<string[]>();
            System.Text.Encoding.RegisterProvider(System.Text.CodePagesEncodingProvider.Instance);
            Encoding gbk = Encoding.GetEncoding("GBK");
            foreach (string filePath in csvFilePaths)
            {
                using (TextFieldParser parser = new TextFieldParser(filePath,gbk))
                {
                    parser.TextFieldType = FieldType.Delimited;
                    parser.SetDelimiters(",");
                    if (isFirstFile)
                    {
                        string[] headers = parser.ReadFields();
                        foreach (string header in headers)
                        {
                            dataTable.Columns.Add(header);
                        }
                        isFirstFile = false;
                    }
                    while (!parser.EndOfData)
                    {
                        string[] fields = parser.ReadFields();
                        for (int i = 0; i < fields.Length+40; i++)
                        {
                            // 进行文本格式转换，例如添加前缀
                            fields[i] = fields[i];
                        }
                        if (!allRows.Contains(fields))
                        {
                            allRows.Add(fields);
                        }
                    }
                }
            }
            // 将数据添加到DataTable并绑定到DataGrid
            foreach (var row in allRows)
            {
                DataRow dataRow = dataTable.NewRow();
                for (int i = 0; i < row.Length; i++)
                {
                    dataRow[i] = row[i];
                }
                dataTable.Rows.Add(dataRow);
            }
            // 将DataTable作为数据源绑定到DataGrid
            Datagrid1.ItemsSource = dataTable.DefaultView;
            }
        
        /// <summary>
        /// 只支持csv格式
        /// </summary>
        /// <param name="filename"></param>
        private void ImportAndConvertCsvToDataGrid(string filename)
        {
            DataTable dataTable = new DataTable();
            string utf8Content = "";
            System.Text.Encoding.RegisterProvider(System.Text.CodePagesEncodingProvider.Instance);
            //var codem = Encoding.GetEncoding("GBK");
            Encoding gbk = Encoding.GetEncoding("GBK");
            // 先以ANSI编码读取文件内容
            using (StreamReader ansiReader = new StreamReader(filename, gbk))
            {
                utf8Content = Encoding.UTF8.GetString(Encoding.Default.GetBytes(ansiReader.ReadToEnd()));
            }
            // 使用TextFieldParser处理转换后的UTF-8内容
            using (TextFieldParser parser = new TextFieldParser(new MemoryStream(Encoding.UTF8.GetBytes(utf8Content))))
            {
                parser.TextFieldType = FieldType.Delimited;
                parser.SetDelimiters(",");
                string[] headers = parser.ReadFields();

                // 添加列到DataTable
                foreach (string header in headers)
                {
                    dataTable.Columns.Add(header);
                }

                while (!parser.EndOfData)
                {
                    string[] fields = parser.ReadFields();
                    DataRow dataRow = dataTable.NewRow();
                    for (int i = 0; i < fields.Length+40; i++)
                    {
                        dataRow[i] = fields[i];
                    }
                    dataTable.Rows.Add(dataRow);
                }
            }

            // 将DataTable作为数据源绑定到DataGrid
            Datagrid1.ItemsSource = dataTable.DefaultView;
        }
        /// <summary>
        /// 只支持xlxs和xls
        /// </summary>
        /// <param name="filename"></param>
        private void ImportExcelToDataGrid(string filename)
        {
            // 打开Excel文件，替换路径为实际的Excel文件路径
            using (var package = new ExcelPackage(new FileInfo(filename)))
            {
                ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
                var worksheet = package.Workbook.Worksheets[0]; // 获取第一个工作表
                int rowCount = worksheet.Dimension.Rows;
                int colCount = worksheet.Dimension.Columns;

                // 创建DataTable来存储Excel数据
                DataTable dataTable = new DataTable();

                // 添加列到DataTable
                for (int col = 1; col <= colCount; col++)
                {
                    dataTable.Columns.Add(worksheet.Cells[1, col].Value.ToString());
                }

                // 添加行数据到DataTable
                for (int row = 2; row <= rowCount; row++)
                {
                    DataRow dataRow = dataTable.NewRow();
                    for (int col = 1; col <= colCount; col++)
                    {
                        dataRow[col - 1] = worksheet.Cells[row, col].Value;
                    }
                    dataTable.Rows.Add(dataRow);
                }
                Datagrid1.ItemsSource = dataTable.DefaultView;
            }
        }

        private void Import2_Click(object sender, RoutedEventArgs e)
        {
            OpenFileDialog file = new OpenFileDialog();
            file.ShowDialog();
            if (file.FileName == "")
            {
                MessageBox.Show("请选中文件夹");
            }
            if (!file.FileName.Contains("焊口"))
            {
                MessageBox.Show("请选择焊口表进行导入操作！！！");
            }
            if (file.FileName.Contains(".csv"))
            {
                ImportAndConvertCsvToDataGrid(file.FileName);
            }
            else if (file.FileName.Contains(".xlxs") || file.FileName.Contains(".xls"))
            {
                ImportExcelToDataGrid(file.FileName);
            }
            else
            {
                MessageBox.Show("程序出错");
            }

        }

        private void Import3_Click(object sender, RoutedEventArgs e)
        {
            OpenFileDialog file = new OpenFileDialog();
            file.ShowDialog();
            if (file.FileName == "")
            {
                MessageBox.Show("请选中文件夹");
            }
            if (!file.FileName.Contains("管段切割"))
            {
                MessageBox.Show("请选择管段切割表进行导入操作！！！");
            }
            if (file.FileName.Contains(".csv"))
            {
                ImportAndConvertCsvToDataGrid(file.FileName);
            }
            else if (file.FileName.Contains(".xlxs") || file.FileName.Contains(".xls"))
            {
                ImportExcelToDataGrid(file.FileName);
            }
            else
            {
                MessageBox.Show("程序出错");
            }
            
        }

        /// <summary>
        /// datagrid转换dataset通用
        /// </summary>
        /// <param name="dataGrid"></param>
        /// <returns></returns>
        private DataTable ConvertDataGridToDataTable(DataGrid dataGrid)
        {
            DataTable dataTable = new DataTable();

            // 获取DataGrid的列信息添加到DataTable作为列
            foreach (DataGridColumn column in dataGrid.Columns)
            {
                // 对于数据绑定列，获取绑定的属性类型作为DataTable列类型
                if (column is DataGridBoundColumn boundColumn)
                {
                    var binding = boundColumn.Binding as System.Windows.Data.Binding;
                    if (binding != null && binding.Path != null)
                    {
                        var propertyInfo = binding.Path.Path.GetType();
                        dataTable.Columns.Add(column.Header.ToString(), propertyInfo);
                    }
                    else
                    {
                        dataTable.Columns.Add(column.Header.ToString(), typeof(object));
                    }
                }
                else
                {
                    // 对于非绑定列，设置为object类型（可能需要根据实际情况进一步处理）
                    dataTable.Columns.Add(column.Header.ToString(), typeof(object));
                }
            }

            // 获取DataGrid的行数据添加到DataTable
            foreach (var item in dataGrid.Items)
            {
                DataRow dataRow = dataTable.NewRow();
                var row = item as DataRowView;
                if (row != null)
                {
                    for (int i = 0; i < dataTable.Columns.Count; i++)
                    {
                        dataRow[i] = row.Row.ItemArray[i];
                    }
                    dataTable.Rows.Add(dataRow);
                }
            }

            return dataTable;
        }
    }
}