mes-client/UAS-出货标签管理/生成条码.cs

using NPOI.SS.Util;
using System;
using System.Collections.Generic;
using System.Data;
using System.Drawing;
using System.Text;
using System.Text.RegularExpressions;
using System.Windows.Forms;
using UAS_LabelMachine.PublicMethod;

namespace UAS_LabelMachine
{
    public partial class 生成条码 : Form
    {
        AutoSizeFormClass asc = new AutoSizeFormClass();

        DataHelper dh;

        DataTable dt;

        StringBuilder sql = new StringBuilder();

        string pi_id;
        //用于提示超出数量的物料
        Dictionary<string, string> NotPass = new Dictionary<string, string>();
        //前缀条件
        string Prefix = "";
        //生成条码的流水号
        int serialnum = 0;
        //客户的流水号
        int custserialnum = 0;
        //是否生成过条码
        bool FirstCode = false;
        //客户编号
        string CustCode = "";
        //编码规则编号
        string NrCode = "";

        public 生成条码(string PI_INOUTNO)
        {
            InitializeComponent();
            pi_inoutno.Text = PI_INOUTNO;
        }

        private void 生成条码_Load(object sender, EventArgs e)
        {
            dh = new DataHelper();
            ChooseAll.ChooseAll(ProdIoInfDGV);
            //如果传进了出入库单号则默认执行一次取数据
            if (pi_inoutno.Text != "")
            {
                bi_inoutno_KeyDown(sender, new KeyEventArgs(Keys.Enter));
            }
            pr_kind.Text = "全部";
            asc.controllInitializeSize(this);
            Width = Width - 1;
        }

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

        private void 生成条码_SizeChanged(object sender, EventArgs e)
        {
            asc.controlAutoSize(this);
        }

        /// <summary>
        /// 筛选按钮
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void Screen_Click(object sender, EventArgs e)
        {
            bi_inoutno_KeyDown(sender, new KeyEventArgs(Keys.Enter));
        }

        /// <summary>
        /// 生成条码
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void GenerateBarCode_Click(object sender, EventArgs e)
        {
            //获取编码规则
            DataTable Nr = (DataTable)dh.ExecuteSql("select nrd_detno,nrd_name,nrd_type,nrd_sql,nrd_length from NoRuleDetail left join norule on nrd_nrid=nr_id where nr_custcode='" + CustCode + "' order by nrd_detno", "select");
            //如果没有则取公共规则
            if (Nr.Rows.Count == 0)
                Nr = (DataTable)dh.ExecuteSql("select nrd_detno,nrd_name,nrd_type,nrd_sql,nrd_length 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;
            //存放键值对
            Dictionary<int, string> NrDic = new Dictionary<int, string>();
            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();
                        NrDic.Add(m, 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 (Match mch in match.Matches(SQL))
                            {
                                SQL = SQL.Replace(mch.Value.Trim(), "'" + pi_inoutno.Text + "'");
                            }
                            Temp = (DataTable)dh.ExecuteSql(SQL, "select");
                        }
                        if (Temp.Rows.Count > 0)
                        {
                            NrData[m] = Temp.Rows[0][0].ToString();
                            NrDic.Add(m, NrData[m]);
                        }
                        else
                        {
                            NrData[m] = "";
                            NrDic.Add(m, NrData[m]);
                        }
                        break;
                    //流水需要通过MaxNumber去取
                    case "流水":
                        NrData[m] = dh.getFieldDataByCondition("RuleMaxNum", "rmn_maxnumber", "rmn_nrcode='" + CustCode + "'").ToString();
                        NrDic.Add(m, NrData[m]);
                        //设置当前流水
                        custserialnum = int.Parse(NrData[m] == "" ? "0" : NrData[m]);
                        SerialNumIndex = m;
                        SerialNumLength = int.Parse(Nr.Rows[m]["nrd_length"].ToString());
                        break;
                    default:
                        break;
                }
            }
            Prefix = "";
            for (int i = 0; i < NrDic.Count; i++)
            {
                //如果不是流水则正常添加
                if (i != SerialNumIndex)
                {
                    Prefix += NrDic[i];
                }
                else
                {
                    //获取最大的流水号
                    string maxnum = dh.getFieldDataByCondition("RuleMaxNum", "rmn_maxnumber", "rmn_nrcode='" + NrCode + "' 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,'" + NrCode + "','" + Prefix + "','1')", "insert");
                        custserialnum = 1;
                    }
                    //如果流水号不为空则取当前流水
                    else
                    {
                        custserialnum = int.Parse(maxnum);
                    }
                    break;
                }
            }
            //有错误需要提醒的内容
            int CheckedRowCount = 0;
            //遍历整个Grid，勾选的项目全部进行条码生成
            for (int i = 0; i < ProdIoInfDGV.RowCount; i++)
            {
                if (ProdIoInfDGV.Rows[i].Cells["Choose"].FormattedValue.ToString() == "True")
                {
                    CheckedRowCount++;
                    List<string> pib_inqty = new List<string>();
                    string pd_id = ProdIoInfDGV.Rows[i].Cells["pd_id"].FormattedValue.ToString();
                    string pd_prodcode = ProdIoInfDGV.Rows[i].Cells["pd_prodcode"].FormattedValue.ToString();
                    string pr_id = ProdIoInfDGV.Rows[i].Cells["pr_id"].FormattedValue.ToString();
                    string pd_ordercode = ProdIoInfDGV.Rows[i].Cells["pd_ordercode"].FormattedValue.ToString();
                    string pd_pdno = ProdIoInfDGV.Rows[i].Cells["pd_pdno"].FormattedValue.ToString();
                    string pd_orderdetno = ProdIoInfDGV.Rows[i].Cells["pd_orderdetno"].FormattedValue.ToString();
                    //总数
                    int pd_totalqty = int.Parse(ProdIoInfDGV.Rows[i].Cells["pd_totalqty"].FormattedValue.ToString());
                    //本次数量
                    int pd_qty = int.Parse(ProdIoInfDGV.Rows[i].Cells["pd_qty"].FormattedValue.ToString());
                    //最小包装数
                    int pr_zxbzs = int.Parse(ProdIoInfDGV.Rows[i].Cells["pr_zxbzs"].FormattedValue.ToString());
                    //中盒容量
                    int mid_qty;
                    try
                    {
                        mid_qty = int.Parse(ProdIoInfDGV.Rows[i].Cells["mid_qty"].FormattedValue.ToString());
                    }
                    catch (Exception)
                    {
                        MessageBox.Show("请检查中盒容量");
                        return;
                    }
                    //如果中盘盒数量为1且有尾数则表示一箱未装满
                    int mid_num = int.Parse(ProdIoInfDGV.Rows[i].Cells["mid_num"].FormattedValue.ToString());
                    //中盒尾数
                    int mid_remain = int.Parse(ProdIoInfDGV.Rows[i].Cells["mid_remain"].FormattedValue.ToString());
                    string pib_barcode = dh.getFieldDataByCondition("prodiobarcode", "max(pib_barcode)", "PIB_INOUTNO='" + pi_inoutno.Text + "'").ToString();
                    //中盒数量*中盒容量=需要打印的单盘标签
                    ArrayList<string> midcode = new ArrayList<string>();
                    ArrayList<string> barcode = new ArrayList<string>();
                    ArrayList<string> custbarcode = new ArrayList<string>();
                    //循环中盒号的个数，取当前出入库单最大 的中盒号+1
                    for (int j = 0; j < mid_num; j++)
                    {
                        //获取中盘的编号
                        string mid_code = dh.getFieldDataByCondition("PRODIOBARCODE", "nvl(max(to_number(PIB_OUTBOXCODE1)),0)+" + (j + 1), "PIB_INOUTNO='" + pi_inoutno.Text + "'").ToString();
                        //如果尾数不为0，并且已经遍历到了最后一箱(未装满的箱)
                        int count = 0;
                        int AddNum = pr_zxbzs;
                        if (mid_remain != 0 && j + 1 == mid_num)
                        {
                            //剩下的尾数刚好够整数的最小包或者加上一个未装满的最小包
                            count = mid_remain % pr_zxbzs == 0 ? mid_remain / pr_zxbzs : (mid_remain / pr_zxbzs) + 1;
                        }
                        else
                        {
                            //循环中盒的箱内容量
                            count = mid_qty;
                        }
                        for (int k = 0; k < count; k++)
                        {
                            //将箱号添加进List
                            barcode.Add(BarcodeMethod1(pd_id, pr_id, pib_barcode));
                            custbarcode.Add(BarcodeMethod1(NrDic, SerialNumIndex, SerialNumLength));
                            midcode.Add(mid_code);
                            if (mid_remain % pr_zxbzs != 0 && k + 1 == count && j + 1 == mid_num)
                                AddNum = mid_remain % pr_zxbzs;
                            pib_inqty.Add(AddNum.ToString());
                        }
                    }
                    //插入条码
                    sql.Clear();
                    sql.Append("insert into prodiobarcode (PIB_ID,PIB_PRODCODE,PIB_INDATE,PIB_INOUTNO,PIB_PIID,PIB_BARCODE,PIB_CUSTBARCODE,PIB_PDNO,");
                    sql.Append("PIB_PDID,PIB_PICLASS,PIB_QTY,PIB_PRODID,PIB_OUTBOXCODE1,PIB_IFPRINT,PIB_IFPICK,PIB_ORDERCODE,PIB_CUSTPO,pib_orderdetno)");
                    sql.Append(" values (prodiobarcode_seq.nextval,'" + pd_prodcode + "',sysdate,'" + pi_inoutno.Text + "'," + pi_id + ",:barcode,:custbarcode,'" + pd_pdno + "','" + pd_id + "',");
                    sql.Append("'" + pi_class.Text + "',:pib_inqty,'" + pr_id + "',:midcode,0,0,'" + pd_ordercode + "','','" + pd_orderdetno + "')");
                    dh.BatchInsert(sql.ToString(), new string[] { "barcode", "custbarcode", "pib_inqty", "midcode" }, barcode.ToArray(), custbarcode.ToArray(), pib_inqty.ToArray(), midcode.ToArray());
                    //更新最大流水号
                    dh.UpdateByCondition("RuleMaxNum", "rmn_maxnumber='" + custserialnum + "'", "rmn_nrcode='" + NrCode + "' and rmn_prefix='" + Prefix + "'");
                }
            }
            if (CheckedRowCount > 0)
            {
                LoadData();
                MessageBox.Show("生成条码成功！");
            }
            else
            {
                MessageBox.Show("未勾选需要生成的明细!");
            }
            //如果含有内容不符合的选项，进行提示
            string str = "";
            foreach (string ss in NotPass.Values)
            {
                str += ss + "\n";
            }
            if (str != "")
                MessageBox.Show(str);
        }

        //生成唯一条码
        public string BarcodeMethod1(string pd_id, string pr_id, string pib_barcode)
        {
            if (pib_barcode != "")
            {
                if (FirstCode)
                {
                    serialnum = serialnum + 1;
                }
                //第一次的时候去获取数据库查询出来的值
                else
                {
                    serialnum = int.Parse(pib_barcode.Substring(pib_barcode.Length - 4)) + 1;
                    FirstCode = true;
                }
            }
            else
            {
                serialnum = serialnum + 1;
            }
            string serialcode = serialnum.ToString();
            for (int i = serialnum.ToString().Length; i < 4; i++)
            {
                serialcode = "0" + serialcode;
            }
            return pd_id + "-" + pr_id + "-" + serialcode;
        }

        //生成客户条码
        public string BarcodeMethod1(Dictionary<int, string> Dic, int Index, int Length)
        {
            string str = "";
            for (int i = 0; i < Dic.Count; i++)
            {
                //如果不是流水则正常添加
                if (i != Index)
                {
                    str += Dic[i];
                }
                else
                {
                    //如果是流水则需要在前面加0
                    string serialcode = custserialnum.ToString();
                    for (int j = serialcode.ToString().Length; j < Length; j++)
                    {
                        serialcode = "0" + serialcode;
                    }
                    str += serialcode;
                }
            }
            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;
        }

        /// <summary>
        /// 重绘指定列的背景色
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void ProdIoInfDGV_CellPainting(object sender, DataGridViewCellPaintingEventArgs e)
        {
            bool mouseOver = e.CellBounds.Contains(this.PointToClient(Cursor.Position));
            if (e.ColumnIndex > 0)
                if (ProdIoInfDGV.Columns[e.ColumnIndex].Name == "pr_zxbzs" || ProdIoInfDGV.Columns[e.ColumnIndex].Name == "pd_qty" || ProdIoInfDGV.Columns[e.ColumnIndex].Name == "mid_qty")
                {
                    SolidBrush solidBrush = new SolidBrush(Color.FromArgb(51, 153, 255));
                    e.Graphics.FillRectangle(mouseOver ? solidBrush : Brushes.LightSeaGreen, e.CellBounds);
                    Rectangle border = e.CellBounds;
                    border.Width -= 1;
                    e.Graphics.DrawRectangle(Pens.White, border);
                    e.PaintContent(e.CellBounds);
                    e.Handled = true;
                }
        }

        /// <summary>
        /// 计算中盘尾数的方法
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void ProdIoInfDGV_CellEndEdit(object sender, DataGridViewCellEventArgs e)
        {
            object pr_zxbzs = ProdIoInfDGV.Rows[e.RowIndex].Cells["pr_zxbzs"].Value;
            object pd_qty = ProdIoInfDGV.Rows[e.RowIndex].Cells["pd_qty"].Value;
            object mid_qty = ProdIoInfDGV.Rows[e.RowIndex].Cells["mid_qty"].Value;
            object pd_totalqty = ProdIoInfDGV.Rows[e.RowIndex].Cells["pd_totalqty"].Value;
            if (pr_zxbzs != null && pd_qty != null && mid_qty != null)
            {
                int 最小包装量 = int.Parse(pr_zxbzs.ToString());
                int 中盘容量 = int.Parse(mid_qty.ToString());
                int 本次数量 = int.Parse(pd_qty.ToString());
                int 总数 = int.Parse(pd_totalqty.ToString());
                if (最小包装量 > 0)
                {
                    if (本次数量 > 总数)
                    {
                        MessageBox.Show("本次数量不能大于总数");
                        return;
                    }
                    if (中盘容量 <= 0 || 最小包装量 <= 0)
                    {
                        MessageBox.Show("中盘容量和单盘数量必须是正整数");
                        return;
                    }
                    //计算中盘数量
                    int mid_num = 本次数量 / (最小包装量 * 中盘容量);
                    //计算中盘尾数
                    if (本次数量 % (最小包装量 * 中盘容量) == 0)
                        ProdIoInfDGV.Rows[e.RowIndex].Cells["mid_num"].Value = mid_num;
                    else
                        ProdIoInfDGV.Rows[e.RowIndex].Cells["mid_num"].Value = mid_num + 1;
                    ProdIoInfDGV.Rows[e.RowIndex].Cells["mid_remain"].Value = 本次数量 - mid_num * 最小包装量 * 中盘容量;
                }
            }
        }

        //设置全部中盒容量
        private void SetMidCapacity_Click(object sender, EventArgs e)
        {
            for (int i = 0; i < ProdIoInfDGV.Rows.Count; i++)
            {
                ProdIoInfDGV.Rows[i].Cells["mid_qty"].Value = MidCapacity.Text;
                if (ProdIoInfDGV.Rows[i].Cells["pr_zxbzs"].Value.ToString() != "" && ProdIoInfDGV.Rows[i].Cells["pd_qty"].Value.ToString() != "" && ProdIoInfDGV.Rows[i].Cells["mid_qty"].Value.ToString() != "" && ProdIoInfDGV.Rows[i].Cells["pr_zxbzs"].Value.ToString() != "0")
                {
                    object pr_zxbzs = ProdIoInfDGV.Rows[i].Cells["pr_zxbzs"].Value;
                    object pd_qty = ProdIoInfDGV.Rows[i].Cells["pd_qty"].Value;
                    object mid_qty = ProdIoInfDGV.Rows[i].Cells["mid_qty"].Value;
                    object pd_totalqty = ProdIoInfDGV.Rows[i].Cells["pd_totalqty"].Value;
                    if (pr_zxbzs != null && pd_qty != null && mid_qty != null)
                    {
                        int 最小包装量 = int.Parse(pr_zxbzs.ToString());
                        int 中盘容量 = int.Parse(mid_qty.ToString());
                        int 本次数量 = int.Parse(pd_qty.ToString());
                        int 总数 = int.Parse(pd_totalqty.ToString());
                        //计算中盘数量
                        int mid_num = 本次数量 / (最小包装量 * 中盘容量);
                        //计算中盘尾数
                        if (本次数量 % (最小包装量 * 中盘容量) == 0)
                            ProdIoInfDGV.Rows[i].Cells["mid_num"].Value = mid_num;
                        else
                            ProdIoInfDGV.Rows[i].Cells["mid_num"].Value = mid_num + 1;
                        ProdIoInfDGV.Rows[i].Cells["mid_remain"].Value = 本次数量 - mid_num * 最小包装量 * 中盘容量;
                    }
                }
            }
        }

        private void LoadData()
        {
            //获取客户编号
            CustCode = dh.getFieldDataByCondition("ProdInOut", "pi_cardcode", "pi_inoutno='" + pi_inoutno.Text + "'").ToString();
            //获取编码规则编号
            NrCode = dh.getFieldDataByCondition("NoRule", "nr_code", "nr_custcode='" + CustCode + "'").ToString();
            //获取编码规则
            DataTable Nr = (DataTable)dh.ExecuteSql("select nrd_detno,nrd_name,nrd_type,nrd_sql,nrd_length from NoRuleDetail left join norule on nrd_nrid=nr_id where nr_custcode='" + CustCode + "' order by nrd_detno", "select");
            //用于过滤参数的正则表达式
            Regex match = new Regex("{\\w+}");
            //用于存放每一项的明细的数据
            string[] NrData = new string[Nr.Rows.Count];
            //流水号的索引
            int SerialNumIndex = 0;
            //流水长度
            int SerialNumLength = 0;
            //存放键值对
            Dictionary<int, string> NrDic = new Dictionary<int, string>();
            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();
                        NrDic.Add(m, 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 (Match mch in match.Matches(SQL))
                            {
                                SQL = SQL.Replace(mch.Value.Trim(), "'" + pi_inoutno.Text + "'");
                            }
                            Temp = (DataTable)dh.ExecuteSql(SQL, "select");
                        }
                        if (Temp.Rows.Count > 0)
                        {
                            NrData[m] = Temp.Rows[0][0].ToString();
                            NrDic.Add(m, NrData[m]);
                        }
                        break;
                    //流水需要通过MaxNumber去取
                    case "流水":
                        NrData[m] = dh.getFieldDataByCondition("RuleMaxNum", "rmn_maxnumber", "rmn_nrcode='" + NrCode + "'").ToString();
                        NrDic.Add(m, NrData[m]);
                        //设置当前流水
                        custserialnum = int.Parse(NrData[m] == "" ? "0" : NrData[m]);
                        SerialNumIndex = m;
                        SerialNumLength = int.Parse(Nr.Rows[m]["nrd_length"].ToString());
                        break;
                    default:
                        break;
                }
            }
            //流水前的前缀，查询RuleMaxNum表看是否有对应的MaxNumber记录，如果有则取MaxNumber，没有的话则进行插入
            Prefix = "";
            for (int i = 0; i < NrDic.Count; i++)
            {
                //如果不是流水则正常添加
                if (i != SerialNumIndex)
                {
                    Prefix += NrDic[i];
                }
                else
                {
                    //获取最大的流水号
                    string maxnum = dh.getFieldDataByCondition("RuleMaxNum", "rmn_maxnumber", "rmn_nrcode='" + NrCode + "' 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,'" + NrCode + "','" + Prefix + "','1')", "insert");
                        custserialnum = 1;
                    }
                    //如果流水号不为空则取当前流水
                    else
                    {
                        custserialnum = int.Parse(maxnum);
                    }
                    break;
                }
            }
            dt = (DataTable)dh.ExecuteSql("select pi_class,pi_id from prodinout where pi_inoutno='" + pi_inoutno.Text + "'", "select");
            if (dt.Rows.Count > 0)
            {
                pi_id = dt.Rows[0]["pi_id"].ToString();
                BaseUtil.SetFormValue(this.Controls, dt);
                //查询出入库的类型
                dt = (DataTable)dh.ExecuteSql("select ds_inorout from documentsetup where ds_name='" + pi_class.Text + "'", "select");
                sql.Clear();
                sql.Append("select * from prodiobarcode_view where  pd_piid='" + pi_id + "'");
                dt = (DataTable)dh.ExecuteSql(sql.ToString(), "select");
                BaseUtil.FillDgvWithDataTable(ProdIoInfDGV, dt);
            }
            else
            {
                MessageBox.Show("当前单据不存在");
                pi_inoutno.Text = "";
            }
        }

        private void ResetSerialNum_Click(object sender, EventArgs e)
        {
            dt = (DataTable)dh.ExecuteSql("select pi_class,pi_id from prodinout where pi_inoutno='" + pi_inoutno.Text + "'", "select");
            if (dt.Rows.Count > 0)
            {
                if (NrCode != "")
                {
                    dh.UpdateByCondition("RuleMaxNum", "rmn_maxnumber=1", "rmn_nrcode='" + NrCode + "' and rmn_prefix='" + Prefix + "'");
                    MessageBox.Show("流水重置成功");
                }
                else
                    MessageBox.Show("当前客户无对应条码规则");
            }
            else
            {
                MessageBox.Show("当前单据不存在");
                pi_inoutno.Text = "";
            }
        }
    }
}