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

using NPOI.SS.Formula.Functions;
using Seagull.BarTender.Print;
using System;
using System.Data;
using System.Drawing;
using System.Drawing.Printing;
using System.Security.Cryptography;
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.Query
{
    public partial class Query_SN : Form
    {

        int serialnum = 0;
        //客户的流水号
        int custserialnum = 0;

        Engine engine = new Engine();

        LabelFormatDocument format;

        DataHelper dh = SystemInf.dh;

        //前缀条件

        //生成条码的流水号

        DataTable Dbfind;
        public Query_SN()
        {
            InitializeComponent();
        }

        private void PrintTest_Click(object sender, EventArgs e)
        {
            PrintDocument print = new PrintDocument();
            print.PrinterSettings.PrinterName = PrinterList.Text;
            print.PrintPage += Print_PrintPage;
            print.Print();
        }

        private void Print_PrintPage(object sender, PrintPageEventArgs e)
        {
            Graphics g = e.Graphics;
            float leftMargin = 10f; //左边距
            SolidBrush myBrush = new SolidBrush(Color.Black);//刷子
            float yPosition = 5f;//行定位
            Font printFont = new Font("微软雅黑", 20f, FontStyle.Bold);//设置字体
            yPosition += printFont.GetHeight(g);//另起一行
            g.DrawString("成功连接此打印机", printFont, myBrush, leftMargin, yPosition, new StringFormat());
        }

        private void Setting_Click(object sender, EventArgs e)
        {
            PrintDialog printd = new PrintDialog();
            printd.PrinterSettings.PrinterName = PrinterList.Text;
            printd.ShowDialog();
        }

        private void Export_Click(object sender, EventArgs e)
        {
            MD5 md5 = MD5.Create();
            folderBrowserDialog1.Description = "选择导出的路径";
            DialogResult result = folderBrowserDialog1.ShowDialog();
            if (result == DialogResult.OK)
            {
                string FolderPath = folderBrowserDialog1.SelectedPath;
                ExcelHandler eh = new ExcelHandler();
                DataTable dt = (DataTable)dh.ExecuteSql("select msl_makecode 工单号,msl_sncode 序列号 from makesnlist " +
                    "where msl_makecode='" + ma_code.Text + "' order by msl_sncode", "select");
                eh.ExportExcel(dt, FolderPath);
            }
        }

        public string BarcodeMethod1(string Prefix, string Suffix, int Index, int Length, int radix, string char_out)
        {
            string str = Prefix;
            //如果是流水则需要在前面加0
            string serialcode = BaseUtil.DToAny(custserialnum, radix, char_out);
            for (int j = serialcode.ToString().Length; j < Length; j++)
            {
                serialcode = "0" + serialcode;
            }
            str += serialcode;
            str += Suffix;
            custserialnum = custserialnum + 1;
            return str;
        }

        private static string lpad(int length, string number)
        {
            while (number.Length < length)
            {
                number = "0" + number;
            }
            number = number.Substring(number.Length - length, length);
            return number;
        }

        private void SystemSetting_PrinterTest_Load(object sender, EventArgs e)
        {
            engine.Start();


            ma_code.TableName = " make left join product on ma_prodcode=pr_code";
            ma_code.SelectField = "ma_code # 工单编号,pr_code # 产品编号,pr_spec # 型号";
            ma_code.FormName = Name;
            ma_code.SetValueField = new string[] { "ma_code" };
            //ma_code.Condition = "ma_statuscode='STARTED'";
            ma_code.DbChange += pr_code_DbChange;
        }

        private void pr_code_DbChange(object sender, EventArgs e)
        {
            Dbfind = ma_code.ReturnData;
            BaseUtil.SetFormValue(this.Controls, Dbfind);
            DataTable dt = (DataTable)dh.ExecuteSql("select ma_qty,ma_prodcode,pr_detail from make left join product on ma_prodcode=pr_code  where ma_code='" + ma_code.Text + "'", "select");
            if (dt.Rows.Count > 0)
            {
                pr_code.Text = dt.Rows[0]["ma_prodcode"].ToString();
                ma_qty.Text = dt.Rows[0]["ma_qty"].ToString();
                pr_detail.Text = dt.Rows[0]["pr_detail"].ToString();
            }
            ma_count.Text = ma_qty.Text;
        }

        private void GenSN_Click(object sender, EventArgs e)
        {
            string Prefix = "";

            string Suffix = "";
            //获取编码规则
            if (!dh.CheckExist("make", "ma_code='" + ma_code.Text + "'"))
            {
                MessageBox.Show("工单号不存在");
                return;
            }
            DataTable dt = (DataTable)dh.ExecuteSql("select pr_exbarcode,ma_qty from make left join product on ma_prodcode=pr_code where ma_code='" + ma_code.Text + "'", "select");
            string pr_exbarcode = "";
            string ma_qty = "";
            if (dt.Rows.Count > 0)
            {
                pr_exbarcode = dt.Rows[0]["pr_exbarcode"].ToString();
                ma_qty = dt.Rows[0]["ma_qty"].ToString();
            }
            if (dh.getRowCount("makesnlist", "msl_makecode='" + ma_code.Text + "' and msl_type='" + (msl_type.Text == "转号前" ? "before" : "after") + "'") + int.Parse(ma_count.Text) > int.Parse(ma_qty))
            {
                MessageBox.Show("工单号" + ma_code.Text + "生产序列号超出工单数");
                return;
            }
            DataTable Nr = (DataTable)dh.ExecuteSql("select nrd_detno,nrd_name,nrd_type,nrd_radix,nrd_sql,nrd_length,nrd_content,nvl(nr_changenum,0)nr_changenum from NoRuleDetail left join norule on nrd_nrid=nr_id where nr_code='" + pr_exbarcode + "' order by nrd_detno", "select");
            //如果没有则取公共规则
            if (Nr.Rows.Count == 0)
                Nr = (DataTable)dh.ExecuteSql("select nrd_detno,nrd_name,nrd_radix,nrd_type,nrd_sql,nrd_length,nrd_content,nvl(nr_changenum,0)nr_changenum from NoRuleDetail left join norule on nrd_nrid=nr_id where nr_custcode is null and nr_isdefault <> 0 order by nrd_detno", "select");
            //用于过滤参数的正则表达式
            Regex match = new Regex("{\\w+}");
            //用于存放每一项的明细的数据
            string[] NrData = new string[Nr.Rows.Count];
            //流水号的索引
            int SerialNumIndex = 0;
            //流水长度
            int SerialNumLength = 0;
            //存放键值对
            int Radix = 10;
            string PrefixFixed = "";
            string char_out = "";
            string nr_changenum = Nr.Rows[0]["nr_changenum"].ToString();
            //如果是
            if (nr_changenum != "-1")
            {
                for (int m = 0; m < Nr.Rows.Count; m++)
                {
                    switch (Nr.Rows[m]["nrd_type"].ToString())
                    {
                        //常量直接进行拼接
                        case "常量":
                            NrData[m] = Nr.Rows[m]["nrd_sql"].ToString();
                            Prefix += NrData[m];
                            Suffix += NrData[m];
                            break;
                        case "SQL":
                            string SQL = Nr.Rows[m]["nrd_sql"].ToString();
                            DataTable Temp;
                            //如果不包含参数替换
                            if (SQL.IndexOf("{") == 0)
                            {
                                Temp = (DataTable)dh.ExecuteSql(SQL, "select");
                            }
                            else
                            {
                                //替换参数后重新执行SQL
                                foreach (System.Text.RegularExpressions.Match mch in match.Matches(SQL))
                                {
                                    SQL = SQL.Replace(mch.Value.Trim(), "'" + ma_code.Text + "'");
                                }
                                Temp = (DataTable)dh.ExecuteSql(SQL, "select");
                            }
                            if (Temp.Rows.Count > 0)
                            {
                                NrData[m] = Temp.Rows[0][0].ToString();
                                Prefix += NrData[m];
                                Suffix += NrData[m];
                            }
                            else
                            {
                                NrData[m] = "";
                                Prefix += NrData[m];
                                Suffix += NrData[m];
                            }
                            break;
                        //流水需要通过MaxNumber去取
                        case "流水":
                            NrData[m] = dh.getFieldDataByCondition("RuleMaxNum", "rmn_maxnumber", "rmn_nrcode='" + pr_exbarcode + "'").ToString();
                            Suffix = "";
                            PrefixFixed = Prefix;
                            //设置当前流水
                            custserialnum = int.Parse(NrData[m] == "" ? "0" : NrData[m]);
                            SerialNumIndex = m;
                            SerialNumLength = int.Parse(Nr.Rows[m]["nrd_length"].ToString());
                            Radix = int.Parse(Nr.Rows[m]["nrd_radix"].ToString());
                            char_out = Nr.Rows[m]["nrd_content"].ToString();
                            break;
                        default:
                            break;
                    }
                }
                //获取最大的流水号
                string maxnum = dh.getFieldDataByCondition("RuleMaxNum", "rmn_maxnumber", "rmn_nrcode='" + pr_exbarcode + "' and rmn_prefix='" + Prefix + "'").ToString();
                //如果流水号为空则插入一条新记录,从1开始取
                if (maxnum == "")
                {
                    dh.ExecuteSql("insert into RuleMaxNum(rmn_id,rmn_nrcode,rmn_prefix,rmn_maxnumber) values(RuleMaxNum_seq.nextval,'" + pr_exbarcode + "','" + Prefix + "','1')", "insert");
                    custserialnum = 1;
                }
                //如果流水号不为空则取当前流水
                else
                {
                    custserialnum = int.Parse(maxnum);
                }
                //遍历整个Grid，勾选的项目全部进行条码生成
                ArrayList<string> custbarcode = new ArrayList<string>();
                for (int i = 0; i < int.Parse(ma_count.Text); i++)
                {
                    string data = BarcodeMethod1(PrefixFixed, Suffix, SerialNumIndex, SerialNumLength, Radix, char_out);
                    if (!custbarcode.Contains(data))
                        custbarcode.Add(data);
                }
                //插入条码
                string sql = "insert into makesnlist(MSL_ID, MSL_INDATE, MSL_MAKECODE, MSL_SNCODE,msl_status,MSL_TYPE)" +
                    "select makesnlist_seq.nextval,sysdate,'" + ma_code.Text + "',:custbarcode,0,'" + (msl_type.Text == "转号前" ? "before" : "after") + "' from dual where :custbarcode not in " +
                  "(select msl_sncode from makesnlist)";
                dh.BatchInsert(sql, new string[] { "custbarcode", "custbarcode" }, custbarcode.ToArray(), custbarcode.ToArray());
                //更新最大流水号
                dh.UpdateByCondition("RuleMaxNum", "rmn_maxnumber='" + custserialnum + "'", "rmn_nrcode='" + pr_exbarcode + "' and rmn_prefix='" + Prefix + "'");
            }
            else
            {
                for (int m = 0; m < Nr.Rows.Count; m++)
                {
                    switch (Nr.Rows[m]["nrd_type"].ToString())
                    {
                        //常量直接进行拼接
                        case "常量":
                            NrData[m] = Nr.Rows[m]["nrd_sql"].ToString();
                            Prefix += NrData[m];
                            Suffix += NrData[m];
                            break;
                        case "SQL":
                            string SQL = Nr.Rows[m]["nrd_sql"].ToString();
                            DataTable Temp;
                            //如果不包含参数替换
                            if (SQL.IndexOf("{") == 0)
                            {
                                Temp = (DataTable)dh.ExecuteSql(SQL, "select");
                            }
                            else
                            {
                                //替换参数后重新执行SQL
                                foreach (System.Text.RegularExpressions.Match mch in match.Matches(SQL))
                                {
                                    SQL = SQL.Replace(mch.Value.Trim(), "'" + ma_code.Text + "'");
                                }
                                Temp = (DataTable)dh.ExecuteSql(SQL, "select");
                            }
                            if (Temp.Rows.Count > 0)
                            {
                                NrData[m] = Temp.Rows[0][0].ToString();
                                Prefix += NrData[m];
                                Suffix += NrData[m];
                            }
                            else
                            {
                                NrData[m] = "";
                                Prefix += NrData[m];
                                Suffix += NrData[m];
                            }
                            break;
                        //流水需要通过MaxNumber去取
                        case "流水":
                            NrData[m] = dh.getFieldDataByCondition("RuleMaxNum", "rmn_maxnumber", "rmn_nrcode='" + pr_exbarcode + "'").ToString();
                            Suffix = "";
                            PrefixFixed = Prefix;
                            //设置当前流水
                            custserialnum = int.Parse(NrData[m] == "" ? "0" : NrData[m]);
                            SerialNumIndex = m;
                            SerialNumLength = int.Parse(Nr.Rows[m]["nrd_length"].ToString());
                            Radix = int.Parse(Nr.Rows[m]["nrd_radix"].ToString());
                            char_out = Nr.Rows[m]["nrd_content"].ToString();
                            break;
                        default:
                            break;
                    }
                }
                //获取最大的流水号
                string maxnum = dh.getFieldDataByCondition("RuleMaxNum", "rmn_maxnumber", "rmn_nrcode='" + pr_exbarcode + "'").ToString();
                //如果流水号为空则插入一条新记录,从1开始取
                if (maxnum == "")
                {
                    dh.ExecuteSql("insert into RuleMaxNum(rmn_id,rmn_nrcode,rmn_maxnumber) values(RuleMaxNum_seq.nextval,'" + pr_exbarcode + "','1')", "insert");
                    custserialnum = 1;
                }
                //如果流水号不为空则取当前流水
                else
                {
                    custserialnum = int.Parse(maxnum);
                }
                //遍历整个Grid，勾选的项目全部进行条码生成
                ArrayList<string> custbarcode = new ArrayList<string>();
                for (int i = 0; i < int.Parse(ma_count.Text); i++)
                {
                    string data = BarcodeMethod1(PrefixFixed, Suffix, SerialNumIndex, SerialNumLength, Radix, char_out);
                    if (!custbarcode.Contains(data))
                        custbarcode.Add(data);
                }
                string sql = "insert into makesnlist(MSL_ID, MSL_INDATE, MSL_MAKECODE, MSL_SNCODE, MSL_TYPE,msl_status)" +
              "select makesnlist_seq.nextval,sysdate,'" + ma_code.Text + "',:custbarcode,'before',0 from dual where :custbarcode not in " +
              "(select msl_sncode from makesnlist)";
                dh.BatchInsert(sql, new string[] { "custbarcode", "custbarcode" }, custbarcode.ToArray(), custbarcode.ToArray());
                //更新最大流水号
                dh.UpdateByCondition("RuleMaxNum", "rmn_maxnumber='" + custserialnum + "'", "rmn_nrcode='" + pr_exbarcode + "'");
            }
            //插入条码
            MessageBox.Show("工单" + ma_code.Text + "生成SN清单成功");
        }
    }
}