mes-client/UAS_MES_ZDXW/FunctionCode/Query/Query_SOP.cs

using System;
using System.IO;
using System.Text;
using System.Windows.Forms;
using System.Net;
using System.Collections.Generic;
using System.Threading;
using System.Net.Sockets;
using System.Data;
using UAS_MES_NEW.DataOperate;
using UAS_MES_NEW.PublicMethod;
using Spire.Xls;
using UAS_MES_NEW.PublicForm;
using System.Web.Script.Serialization;

namespace UAS_MES_NEW.Query
{
    public partial class Query_SOP : Form
    {
        DataHelper dh = new DataHelper();

        string usercode;
        string sourcecode;

        public Query_SOP()
        {
            InitializeComponent();
        }


        public Query_SOP(string iUserCode, string iSource)
        {
            usercode = iUserCode;
            sourcecode = iSource;
            InitializeComponent();
        }

        private void Form3_Load(object sender, EventArgs e)
        {
            CheckForIllegalCrossThreadCalls = false;
        }

        void uploadfile()
        {
            pr_code.Text = FilePath.Text.Substring(FilePath.Text.LastIndexOf(@"\") + 1).Replace(".xls", "").Replace(".xlsx", "");
            pr_code_Leave(new object(), new EventArgs());
            DataTable dt = (DataTable)dh.ExecuteSql("select * from productsop where instr('" + pr_code.Text + "',ps_prodcode)>0 ", "select");
            if (dt.Rows.Count == 0)
            {
                DataTable dt1 = (DataTable)dh.ExecuteSql("select pr_code from product where instr('" + pr_code.Text + "',pr_code)>0", "select");
                if (dt1.Rows.Count > 0)
                {
                    string code = LogicHandler.GetPiInoutCode("ProductSOP", "1");
                    pr_code.Text = dt1.Rows[0]["pr_code"].ToString();
                    dh.ExecuteSql("insert into productsop(ps_id,ps_code,ps_prodcode,ps_indate,ps_inman)values(productsop_seq.nextval,'" + code + "','" + pr_code.Text + "',sysdate,'" + usercode + "')", "insert");
                }
                else
                {
                    MessageBox.Show("产品编号" + pr_code.Text + "不存在");
                    return;
                }
            }
            else
            {
                pr_code.Text = dt.Rows[0]["ps_prodcode"].ToString();
            }
            Workbook workbook = new Workbook();
            workbook.LoadFromFile(FilePath.Text);
            workbook.ConverterSetting.JPEGQuality = 100;
            workbook.ConverterSetting.XDpi = 600;
            workbook.ConverterSetting.YDpi = 600;
            //先清空旧的 ps_attachsop，避免残留旧数据
            dh.ExecuteSql("update ProductSOP set ps_attachsop='' where ps_prodcode='" + pr_code.Text + "'", "update");
            OperatResult.AppendText("一共Sheet: " + workbook.Worksheets.Count + "\n");

            //串行处理每个Sheet，避免并发导致缺页
            //根因：DataHelper.connection是static全局唯一，多线程并发ExecuteSql会互相覆盖command字段
            //      且ps_attachsop=ps_attachsop||fp_id并发拼接非原子，后提交覆盖先提交
            //      Spire.Xls的SaveToPdf/SaveToImage也不是线程安全的
            //方案：串行转换+上传，内存收集fp_id，最后一次性UPDATE
            List<string> successIds = new List<string>();
            List<string> failedSheets = new List<string>();
            int total = workbook.Worksheets.Count;

            for (int i = 0; i < total; i++)
            {
                string sheetName = workbook.Worksheets[i].Name;
                OperatResult.AppendText("[" + (i + 1) + "/" + total + "] 处理Sheet: " + sheetName + "\n");
                try
                {
                    //处理TextBox（放大文字，保持原有逻辑）
                    for (int k = 0; k < workbook.Worksheets[i].TextBoxes.Count; k++)
                    {
                        if (workbook.Worksheets[i].TextBoxes[k].RichText.Text.Length > 1)
                        {
                            workbook.Worksheets[i].TextBoxes[k].RichText.Text = workbook.Worksheets[i].TextBoxes[k].RichText.Text;
                            workbook.Worksheets[i].TextBoxes[k].Width = workbook.Worksheets[i].TextBoxes[k].Width * 2;
                            workbook.Worksheets[i].TextBoxes[k].Height = workbook.Worksheets[i].TextBoxes[k].Height * 2;
                            workbook.Worksheets[i].TextBoxes[k].HAlignment = CommentHAlignType.Center;
                        }
                    }

                    //复制到独立Workbook再转换，避免污染原workbook
                    Workbook bw = new Workbook();
                    Worksheet she = bw.CreateEmptySheet();
                    she.Name = sheetName;
                    she.CopyFrom(workbook.Worksheets[i]);

                    string fp_id = "";
                    if (JPG.Checked)
                    {
                        fp_id = SaveFileToJPG(she);
                    }
                    else
                    {
                        fp_id = SaveFileToPDF(she);
                    }

                    if (fp_id != "")
                    {
                        successIds.Add(fp_id);
                        OperatResult.AppendText("  -> 成功, fp_id=" + fp_id + "\n");
                    }
                    else
                    {
                        failedSheets.Add(sheetName);
                        OperatResult.AppendText("  -> 失败\n");
                    }
                }
                catch (Exception ex)
                {
                    failedSheets.Add(sheetName);
                    OperatResult.AppendText("  -> 异常: " + ex.Message + "\n");
                }
            }

            //所有Sheet处理完成后，一次性拼接fp_id写入数据库
            if (successIds.Count > 0)
            {
                string idList = "";
                foreach (string id in successIds)
                {
                    idList += id + ";";
                }
                dh.ExecuteSql("update ProductSOP set ps_attachsop='" + idList + "' where ps_prodcode='" + pr_code.Text + "'", "update");
                OperatResult.AppendText("已写入" + successIds.Count + "个文件到ProductSOP\n");
            }

            if (failedSheets.Count > 0)
            {
                string msg = "以下Sheet上传失败，可能缺页:\n" + string.Join(", ", failedSheets.ToArray());
                OperatResult.AppendText(msg + "\n");
                MessageBox.Show(msg, "上传部分失败", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
            else
            {
                OperatResult.AppendText("全部" + total + "个Sheet上传成功\n");
            }
        }

        private string SaveFileToPDF(Worksheet sheet)
        {
            string fp_id = "";
            //失败重试2次
            for (int retry = 0; retry < 2; retry++)
            {
                try
                {
                    if (!Directory.Exists(Application.StartupPath + @"\" + pr_code.Text))
                    {
                        Directory.CreateDirectory(Application.StartupPath + @"\" + pr_code.Text);
                    }
                    string filePath = Application.StartupPath + @"\" + pr_code.Text + @"\" + sheet.Name + ".pdf";
                    sheet.SaveToPdf(filePath);
                    OperatResult.AppendText("解析PDF【" + sheet.Name + ".pdf】" + "\n");
                    Dictionary<string, object> dic = new Dictionary<string, object>();
                    dic.Add("em_name", "管理员");
                    dic.Add("em_code", "ADMIN");
                    dic.Add("caller", "ProductSOP");
                    OperatResult.AppendText("上传文件【" + sheet.Name + ".pdf" + "】\n");
                    fp_id = UploadFilesToRemoteUrl("http://192.168.101.253:8099/mes/MEScommon/uploadFiles.action?_noc=1", filePath, dic);
                    if (fp_id != "")
                    {
                        break;
                    }
                    if (retry == 0)
                    {
                        OperatResult.AppendText("首次上传失败,重试中...\n");
                    }
                }
                catch (Exception ex)
                {
                    OperatResult.AppendText("SaveFileToPDF异常: " + ex.Message + (retry == 0 ? " 重试中..." : "") + "\n");
                }
            }
            return fp_id;
        }

        private string SaveFileToJPG(Worksheet sheet)
        {
            string fp_id = "";
            //失败重试2次
            for (int retry = 0; retry < 2; retry++)
            {
                try
                {
                    if (!Directory.Exists(Application.StartupPath + @"\" + pr_code.Text))
                    {
                        Directory.CreateDirectory(Application.StartupPath + @"\" + pr_code.Text);
                    }
                    ExcelPicture picture = sheet.Pictures.Add(@"图片\电子受控章.png");
                    picture.Width = 113;
                    picture.Height = 38;
                    picture.LeftColumn = 20;
                    picture.TopRowOffset = 20;
                    ExcelPicture picture1 = sheet.Pictures.Add(@"图片\签名.png");
                    picture1.Width = 100;
                    picture1.Height = 38;
                    picture1.LeftColumn = 15;
                    picture1.TopRowOffset = 600;
                    string filePath = Application.StartupPath + @"\" + pr_code.Text + @"\" + sheet.Name + ".jpg";
                    sheet.SaveToImage(filePath);
                    OperatResult.AppendText("解析图片【" + sheet.Name + ".jpg】" + "\n");
                    Dictionary<string, object> dic = new Dictionary<string, object>();
                    dic.Add("em_name", "管理员");
                    dic.Add("em_code", "ADMIN");
                    dic.Add("caller", "ProductSOP");
                    OperatResult.AppendText("上传文件【" + sheet.Name + ".jpg" + "】\n");
                    fp_id = UploadFilesToRemoteUrl("http://192.168.101.253:8099/mes/MEScommon/uploadFiles.action?_noc=1", filePath, dic);
                    if (fp_id != "")
                    {
                        break;
                    }
                    if (retry == 0)
                    {
                        OperatResult.AppendText("首次上传失败,重试中...\n");
                    }
                }
                catch (Exception ex)
                {
                    OperatResult.AppendText("SaveFileToJPG异常: " + ex.Message + (retry == 0 ? " 重试中..." : "") + "\n");
                }
            }
            return fp_id;
        }

        /// <summary>
        /// 请求上传图片到阿里云
        /// </summary>
        /// <param name="url">上传地址</param>
        /// <param name="filepath">本地文件路径</param>
        /// <param name="dic">上传的数据信息</param>
        /// <returns></returns>
        public string UploadFilesToRemoteUrl(string url1, string filepath, Dictionary<string, object> dic)
        {
            try
            {
                ServicePointManager.DefaultConnectionLimit = 50;

                string boundary = DateTime.Now.Ticks.ToString("x");

                byte[] boundarybytes = System.Text.Encoding.UTF8.GetBytes("--" + boundary + "\r\n");

                HttpWebRequest request = (HttpWebRequest)WebRequest.Create(url1);
                request.Method = "POST";
                request.Timeout = 10 * 10000;
                request.ContentType = "multipart/form-data; boundary=" + boundary;

                Stream rs = request.GetRequestStream();

                var endBoundaryBytes = Encoding.UTF8.GetBytes("--" + boundary + "--\r\n");

                string formdataTemplate = "Content-Disposition: form-data; name=\"{0}\"\r\n" + "\r\n" + "{1}" + "\r\n";
                if (dic != null)
                {
                    foreach (string key in dic.Keys)
                    {
                        rs.Write(boundarybytes, 0, boundarybytes.Length);

                        string formitem = string.Format(formdataTemplate, key, dic[key]);

                        byte[] formitembytes = System.Text.Encoding.UTF8.GetBytes(formitem);

                        rs.Write(formitembytes, 0, formitembytes.Length);
                    }

                }

                string headerTemplate = "Content-Disposition: form-data; name=\"{0}\"; filename=\"{1}\"\r\n\r\n";
                {
                    rs.Write(boundarybytes, 0, boundarybytes.Length);

                    var header = string.Format(headerTemplate, "file", Path.GetFileName(filepath));

                    var headerbytes = System.Text.Encoding.UTF8.GetBytes(header);

                    rs.Write(headerbytes, 0, headerbytes.Length);

                    using (var fileStream = new FileStream(filepath, FileMode.Open, FileAccess.Read))
                    {
                        var buffer = new byte[1024];

                        var bytesRead = 0;

                        while ((bytesRead = fileStream.Read(buffer, 0, buffer.Length)) != 0)
                        {
                            rs.Write(buffer, 0, bytesRead);
                        }
                    }
                    var cr = Encoding.UTF8.GetBytes("\r\n");

                    rs.Write(cr, 0, cr.Length);
                }

                rs.Write(endBoundaryBytes, 0, endBoundaryBytes.Length);

                var response = request.GetResponse() as HttpWebResponse;

                StreamReader newReader = new StreamReader(response.GetResponseStream(), Encoding.UTF8);
                string Content = newReader.ReadToEnd();
                Dictionary<string, object> dic1 = new Dictionary<string, object>();
                List<Dictionary<string, object>> dic2 = null;
                dic1 = BaseUtil.ToDictionary(Content);
                dic2 = dic1["data"] as List<Dictionary<string, object>>;
                string fp_id = "";
                if (dic2[0]["filepath"] != null)
                {
                    fp_id = dic2[0]["filepath"].ToString();
                }
                if (response.StatusCode == HttpStatusCode.OK)
                {
                    return fp_id;
                }
            }
            catch (Exception e)
            {
                LogicHandler.DoCommandLog("SOP", usercode, "", "", sourcecode, "上传SOP", "上传失败", pr_code.Text, "");
                Console.WriteLine(e.Message + e.StackTrace);
            }
            return "";
        }

        private void ChooseFile_Click(object sender, EventArgs e)
        {
            ChoosePath.Filter = "(*.xls)|*.xls|(*.xlsx)|*.xlsx";
            DialogResult result = ChoosePath.ShowDialog();
            if (result == DialogResult.OK)
            {
                FilePath.Text = ChoosePath.FileName;
            }
        }

        private void UploadSOP_Click(object sender, EventArgs e)
        {
            Thread thread = new Thread(uploadfile);
            SetLoadingWindow stw = new SetLoadingWindow(thread, "上传文件");
            stw.StartPosition = FormStartPosition.CenterScreen;
            stw.ShowDialog();
            if (JPG.Checked)
            {
                LogicHandler.DoCommandLog("SOP", usercode, "", "", sourcecode, "上传SOP【图片格式】", "上传成功", pr_code.Text, "");
            }
            else
            {
                LogicHandler.DoCommandLog("SOP", usercode, "", "", sourcecode, "上传SOP【PDF格式】", "上传成功", pr_code.Text, "");
            }
        }

        private void OperatResult_TextChanged(object sender, EventArgs e)
        {
            //OperatResult.SelectionStart = Text.Length;
            OperatResult.ScrollToCaret();
        }

        TcpServer tcpserver = new TcpServer();

        private void SendSop_Click(object sender, EventArgs e)
        {
            try
            {
                IPHostEntry IpEntry = Dns.GetHostEntry(Dns.GetHostName());
                string IPAddress = "";
                for (int i = 0; i < IpEntry.AddressList.Length; i++)
                {
                    if (IpEntry.AddressList[i].AddressFamily == AddressFamily.InterNetwork)
                    {
                        IPAddress = IpEntry.AddressList[i].ToString();
                    }
                }
                dh.ExecuteSql("update SOPSOURCE set SS_BRDIP='" + IPAddress + "'", "update");
                DataTable dt = (DataTable)dh.ExecuteSql("select * from productsop where ps_prodcode='" + pr_code.Text + "'", "select");
                if (dt.Rows.Count > 0)
                {
                    tcpserver.Start();
                    Thread.Sleep(5000);
                    List<Dictionary<string, string>> listr = new List<Dictionary<string, string>>();
                    string PS_ATTACHSOP = dt.Rows[0]["PS_ATTACHSOP"].ToString();
                    PS_ATTACHSOP = PS_ATTACHSOP.Substring(0, PS_ATTACHSOP.Length - 1).Replace(";", ",");
                    dt = (DataTable)dh.ExecuteSql("select fp_path, fp_name from filepath where fp_id in (" + PS_ATTACHSOP + ") and nvl(fp_path, ' ') <> ' '", "select");
                    JavaScriptSerializer jss = new JavaScriptSerializer();
                    Dictionary<string, object> map1 = new Dictionary<string, object>();
                    Dictionary<string, object> map = new Dictionary<string, object>();
                    string path;
                    string pathroot = dh.GetConfig("filePathUrl", "sys").ToString();
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        Dictionary<string, string> dic1 = new Dictionary<string, string>();
                        path = dt.Rows[i]["fp_path"].ToString();
                        path = encryptBASE64(path.Replace("/app/uas/webapps/postattach", pathroot)).Replace("\\s*|\r|\n|\t", "");
                        dic1.Add("path", path);
                        dic1.Add("filename", dt.Rows[i]["fp_name"].ToString().Replace(" ", "").Replace("（", "").Replace("）", ""));
                        dic1.Add("ps_prodcode", pr_code.Text);
                        listr.Add(dic1);
                    }
                    map1.Add("ps_code", "");
                    map1.Add("url", listr);
                    map.Add("success", true);
                    map.Add("data", map1);
                    tcpserver.Send(jss.Serialize(map));
                    tcpserver.Stop();
                    LogicHandler.DoCommandLog("SOP", usercode, "", "", sourcecode, "广播SOP", "广播成功", pr_code.Text, "");
                    MessageBox.Show("产品编号" + pr_code.Text + "广播成功");
                }
                else
                {
                    MessageBox.Show("产品编号" + pr_code.Text + "未维护SOP文档");
                    LogicHandler.DoCommandLog("SOP", usercode, "", "", sourcecode, "广播SOP", "广播失败，未维护SOP文档", pr_code.Text, "");
                }
            }
            catch (Exception ex)
            {
                LogicHandler.DoCommandLog("SOP", usercode, "", "", sourcecode, "广播SOP", "广播失败", pr_code.Text, "");
                Console.WriteLine(ex.Message + ex.StackTrace);
            }
        }

        public string encryptBASE64(string key)
        {
            byte[] bytes = Encoding.Default.GetBytes(key);
            return Convert.ToBase64String(bytes);
        }

        public static string EncodeBase64(string code_type, string code)
        {
            string encode = "";
            byte[] bytes = Encoding.GetEncoding(code_type).GetBytes(code);
            try
            {
                encode = Convert.ToBase64String(bytes);
            }
            catch
            {
                encode = code;
            }
            return encode;
        }

        private void pr_code_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
            {
                LoadPrCode();
            }
        }

        private void LoadPrCode()
        {
            DataTable dt = (DataTable)dh.ExecuteSql("select pr_code,pr_spec,pr_detail from product where instr('" + pr_code.Text + "',pr_code)>0", "select");
            if (dt.Rows.Count > 0)
            {
                pr_spec.Clear();
                pr_spec.AppendText(dt.Rows[0]["pr_spec"].ToString());
                pr_detail.Text = dt.Rows[0]["pr_detail"].ToString();
                pr_code.Text = dt.Rows[0]["pr_code"].ToString();
            }
            else
            {
                MessageBox.Show("产品编号" + pr_code.Text + "不存在");
            }
        }

        private void pr_code_Leave(object sender, EventArgs e)
        {
            LoadPrCode();
        }

        private void pr_spec_Click(object sender, EventArgs e)
        {

        }
    }
}