mes-client/UAS_MES_HY/FunctionCode/Special/Special_SmtLocation.cs

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Windows.Forms;
using UAS_MES_NEW.DataOperate;
using UAS_MES_NEW.Entity;
using UAS_MES_NEW.PublicMethod;

namespace UAS_MES_NEW.Special
{
    public partial class Special_SmtLocation : Form
    {
        DataHelper dh;

        LogStringBuilder sql = new LogStringBuilder();
        DataTable dt = new DataTable();
        DataTable dbfind;
        ExcelHandler eh;
        DataTable dbbom;
        

        public Special_SmtLocation()
        {
            InitializeComponent();
        }

        private void Special_BoxSplit_Load(object sender, EventArgs e)
        {
             eh = new ExcelHandler();
            dh = SystemInf.dh;
            pr_code.FormName = Name;
            pr_code.SetValueField = new string[] { "pr_code" };
            pr_code.TableName = "product";
            pr_code.SelectField = "pr_code # 物料编号,pr_detail # 物料名称,pr_kind # 物料种类,pr_spec # 物料规格";
            pr_code.Condition = "PR_MANUTYPE = 'MAKE'";
            pr_code.DbChange += Pr_code_DbChange;
        }

        private void Pr_code_DbChange(object sender, EventArgs e)
        {
            dbfind = pr_code.ReturnData;
            BaseUtil.SetFormValue(this.Controls, dbfind);
        }

        private void normalButton2_Click(object sender, EventArgs e)
        {
            DialogResult result = openFileDialog1.ShowDialog();
            if (result == DialogResult.OK)
            {
                textBox1.Text = openFileDialog1.FileName;
            }
        }

        private void normalButton3_Click(object sender, EventArgs e)
        {
            if (pr_code.Text == "" || textBox1.Text == "")
            {
                MessageBox.Show("产品编号与座标图地址不可为空");
                return;
            }
            dt =  eh.ImportExcel(textBox1.Text, "test");
            DataTable dta = new DataTable();
            DataTable dtb = new DataTable();
            DataTable dtc = new DataTable();

            dta.Columns.Add("序号", typeof(string));
            dta.Columns.Add("线别", typeof(string));
            dta.Columns.Add("产品编号", typeof(string));
            dta.Columns.Add("设备编号", typeof(string));
            dta.Columns.Add("站位", typeof(string));
            dta.Columns.Add("板面", typeof(string));
            dta.Columns.Add("首选料号", typeof(string));
            dta.Columns.Add("替代料号", typeof(string));
            dta.Columns.Add("单位用量", typeof(int));
            dta.Columns.Add("规格", typeof(string));


            dtb.Columns.Add("序号", typeof(string));
            dtb.Columns.Add("线别", typeof(string));
            dtb.Columns.Add("产品编号", typeof(string));
            dtb.Columns.Add("设备编号", typeof(string));
            dtb.Columns.Add("站位", typeof(string));
            dtb.Columns.Add("板面", typeof(string));
            dtb.Columns.Add("首选料号", typeof(string));
            dtb.Columns.Add("替代料号", typeof(string));
            dtb.Columns.Add("单位用量", typeof(int));
            dtb.Columns.Add("规格", typeof(string));

            dtc.Columns.Add("序号", typeof(string));
            dtc.Columns.Add("线别", typeof(string));
            dtc.Columns.Add("产品编号", typeof(string));
            dtc.Columns.Add("设备编号", typeof(string));
            dtc.Columns.Add("站位", typeof(string));
            dtc.Columns.Add("板面", typeof(string));
            dtc.Columns.Add("首选料号", typeof(string));
            dtc.Columns.Add("替代料号", typeof(string));
            dtc.Columns.Add("单位用量", typeof(int));
            dtc.Columns.Add("规格", typeof(string));

            //for (int i = 0; i < dt.Rows.Count; i++)
            //{
            //    MessageBox.Show(dt.Rows[i]["RefDes"].ToString()+":"+ dt.Rows[i]["Layer"].ToString());
            //}
            BaseUtil.FillDgvWithDataTable(LabelDataGridView, dt);

            dbbom = (DataTable)dh.ExecuteSql("select BD_SONCODE, replace(case  when SUBSTR(bd_remark2,length(bd_remark2)) = ',' then SUBSTR(bd_remark2,1,LENGTH(bd_remark2)-1) else bd_remark2 end,' ','') BD_LOCATION,replace(pr_spec,',','-') pr_spec from bomdetail@ERP LEFT JOIN Product ON bd_soncode=pr_code  where (bd_mothercode = '" + pr_code.Text + "' or bd_mothercode in(select BD_SONCODE from BOMDetail LEFT JOIN Product ON bd_soncode=pr_code where bd_mothercode = '"+ pr_code.Text + "' and pr_detail =  'SMT贴片部件' ) ) and bd_remark2 is not null and nvl(bd_usestatus,' ') <>'DISABLE' ", "select");
            for (int i = 0; i < dbbom.Rows.Count; i++)
            {
                String[] locationsum = dbbom.Rows[i]["BD_LOCATION"].ToString().Replace(" ","").Replace("，",",").Split(',');
                int A = 0;
                int B = 0;
                int C = 0;
                for (int j = 0; j < locationsum.Length; j++)
                {
                    String location = locationsum[j];
                    if (location.Contains('-'))
                    {
                        String[] arrheng = ConvertToCollection(location).ToArray();
                        for (int s = 0; s < arrheng.Length; s++)
                        {
                            String location2 = arrheng[s];
                            DataRow[] dra = dt.Select("RefDes = '" + location2 + "' and Layer = 'A'");
                            DataRow[] drb = dt.Select("RefDes = '" + location2 + "' and Layer = 'B'");
                            DataRow[] drc = dt.Select("RefDes = '" + location2 + "' and Layer = 'C'");
                            if (dra.Length > 0)
                            {
                                A++;
                            }
                            if (drb.Length > 0)
                            {
                                B++;
                            }
                            if (drc.Length > 0)
                            {
                                C++;
                            }
                        }
                    }
                    else
                    {
                        DataRow[] dra = dt.Select("RefDes = '" + location + "' and Layer = 'A'");
                        DataRow[] drb = dt.Select("RefDes = '" + location + "' and Layer = 'B'");
                        DataRow[] drc = dt.Select("RefDes = '" + location + "' and Layer = 'C'");
                        if (dra.Length > 0)
                        {
                            A++;
                        }
                        if (drb.Length > 0)
                        {
                            B++;
                        }
                        if (drc.Length > 0)
                        {
                            C++;
                        }
                    }
                }
                if (A > 0)
                {
                    DataRow dr = dta.NewRow();
                    dr[2] = pr_code.Text;
                    dr[5] = "A";
                    dr[6] = dbbom.Rows[i]["BD_SONCODE"].ToString();
                    dr[7] = dbbom.Rows[i]["BD_SONCODE"].ToString();
                    dr[8] = A;
                    dr[9] = dbbom.Rows[i]["pr_spec"].ToString();
                    dta.Rows.Add(dr);
                }
                if (B > 0)
                {
                    DataRow dr = dtb.NewRow();
                    dr[2] = pr_code.Text;
                    dr[5] = "B";
                    dr[6] = dbbom.Rows[i]["BD_SONCODE"].ToString();
                    dr[7] = dbbom.Rows[i]["BD_SONCODE"].ToString();
                    dr[8] = B;
                    dr[9] = dbbom.Rows[i]["pr_spec"].ToString();
                    dtb.Rows.Add(dr);
                }
                if (C > 0)
                {
                    DataRow dr = dtc.NewRow();
                    dr[2] = pr_code.Text;
                    dr[5] = "C";
                    dr[6] = dbbom.Rows[i]["BD_SONCODE"].ToString();
                    dr[7] = dbbom.Rows[i]["BD_SONCODE"].ToString();
                    dr[8] = C;
                    dr[9] = dbbom.Rows[i]["pr_spec"].ToString();
                    dtc.Rows.Add(dr);
                }
            }
            String filepath;
            DialogResult result = folderBrowserDialog1.ShowDialog();
            if (result == DialogResult.OK)
            {
                filepath = folderBrowserDialog1.SelectedPath;
                if (dta.Rows.Count > 0)
                {
                    eh.ExportExcel(dta, filepath, "A");
                }
                if (dtb.Rows.Count > 0)
                {
                    eh.ExportExcel(dtb, filepath, "B");
                }
                if (dtc.Rows.Count > 0)
                {
                    eh.ExportExcel(dtc, filepath, "C");
                }
                MessageBox.Show("OK");
            }


        }

  

  public static List<string> ConvertToCollection(string input)
    {
        var matches = Regex.Matches(input, @"^[A-Z]{1,3}\d{1,3}-[A-Z]{1,3}\d{1,3}");
        var collection = new List<string>();

        foreach (Match match in matches)
        {
            var range = match.Value.Split('-');
            var start = range[0];
            var end = range[1];

            var prefix = Regex.Matches(start, @"^[A-Z]{1,3}")[0].Value;
            var startNumber = int.Parse(Regex.Matches(start, @"\d{1,3}")[0].Value);
            var endNumber = int.Parse(Regex.Matches(end, @"\d{1,3}")[0].Value);

            for (int i = startNumber; i <= endNumber; i++)
            {
                var item = $"{prefix}{i}";
                collection.Add(item);
            }
        }

        return collection;
    }

}
}