mes-client/UAS-MES/Batch_Generation_CodeDetail.cs

using NPOI.SS.Util;
using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Windows.Forms;
using 优软MES.DataOperate;
using 优软MES.PublicMethod;

namespace 优软MES
{
    public partial class Batch_Generation_CodeDetail : Form
    {
        DataHelper dh = new DataHelper();
        DataTable dt = new DataTable();
        //string caller = "ProdInOut!BarcodeIn";
        string PD_ID;
        string PI_ID;
        string[] field = { "pd_inoutno","pd_pdno","pd_prodcode","pr_detail","pr_unit","pd_inqty", "pr_tracekind" ,"pi_cardcode","pi_title", "pd_inqty-nvl(pd_barcodeinqty,0)" , "pr_zxbzs","pr_unit" };
        string table = "prodiodetail left join product on pr_code=pd_prodcode  left join prodinout on pi_id=pd_piid";
        //明细行需要隐藏的字段
        string[] hiddenfield = {"bi_pdid","bi_pdaget"};
        string[] EditAbleField = {"bi_location","bi_madedate", "bi_vendbarcode","bi_inqty" };
        public Batch_Generation_CodeDetail()
        {
            InitializeComponent();
        }

        public Batch_Generation_CodeDetail(string pd_id) {
            
            if (pd_id != null && pd_id != "")
            {
                InitializeComponent();
                //设置只能输入数字的TextBox的委托事件
                NumOnly();
                PD_ID = pd_id;
                //初始的时候设置timepicker为空
                bi_madedate.Format = DateTimePickerFormat.Custom;
                bi_madedate.CustomFormat = " ";

                string sql = BaseUtil.AddField(field);
                sql = "select " + sql + " from "+table+" where pd_id=" + pd_id;

                dt = (DataTable)dh.ExecuteSql(sql, "select");
                //加载数据
                LoadData();
                //通过控件名称和列的描述相同时赋值,由于表格上部分的控件是装在一个panel里面的，所以需要通过controls[0]从panel中获取
                BaseUtil.SetFormValue(Controls["Panel0"].Controls, dt);
                //从configs表中取数据
                string Data = dh.GetConfig("UseLocationOrNot", "BarCodeSetting").ToString();
                //如果配置的数据为1或者Y，说明启用这个配置
                if (Data == "1" || Data == "Y")
                {
                    bi_location.Visible = true;
                    bi_location_label.Visible = true;
                }
                BaseUtil.HideField(dataGridView1, hiddenfield);
                BaseUtil.SetDataGridViewReadOnly(dataGridView1,EditAbleField);
                //手动转换批总量
                ValueTransform();
                
              
            }
        }

        private void dateTimePicker1_ValueChanged(object sender, System.EventArgs e)
        {
            bi_madedate.Format = DateTimePickerFormat.Long;
        }

       

        private void bi_prodate_ValueChanged(object sender, EventArgs e)
        {
            bi_madedate.Format = DateTimePickerFormat.Long;
        }

        private void GenerateCode_Click(object sender, EventArgs e)
        {
            //检查生成条码之前的逻辑
            CheckBeforeGenCode();
            //计算打包的数量num表示包数
            //restqty表示不足整包之后的数量，单独作为一个包
            int num = int.Parse(pd_qty.Text) / int.Parse(pr_zxbzs.Text);
            int restqty = int.Parse(pd_qty.Text) - (num * int.Parse(pr_zxbzs.Text));
            string ve_id = GetVendorID(PD_ID);
            string pr_code = dh.getFieldDataByCondition("prodiodetail", "pd_prodcode","pd_id="+PD_ID).ToString();
            //如果用为未选择日期则将日期置为空，否则选择日期的值
            string madedate = bi_madedate.Text;
            if (madedate.Trim() == "")
            {
                madedate = null;
            }
            else {
                madedate = bi_madedate.Value.ToString();
            }
            //用于拼接SQl的StringBulider对象
            StringBuilder sql = new StringBuilder();
            //用于存放条码的数组
            string[] barcode = new string[num];
            for (int i = 0; i < num; i++)
            {
                barcode[i] = BarcodeMethod(pr_code, ve_id);
            }
            BaseUtil.CleanDataTable(dt);
            //通过pd_id查询出需要插入的数据
            dt = dh.getFieldsDataByCondition("prodiodetail left join prodinout on pd_piid=pi_id", new string[] { "pd_pdno", "pi_class", "pd_prodmadedate", "pi_date", "pd_whcode", "pi_whcode" }, "pd_id=" + PD_ID);
            object pd_pdno = dt.Rows[0]["pd_pdno"];
            object pi_class = dt.Rows[0]["pi_class"];
            object pi_date = dt.Rows[0]["pi_date"];
            object pd_prodmadedate = dt.Rows[0]["pd_prodmadedate"];
            object pi_whcode = dt.Rows[0]["pi_whcode"];
            object pd_whcode = dt.Rows[0]["pd_whcode"];
            sql.Append("insert into barcodeio (");
            sql.Append("bi_id,bi_piid,bi_pdid,bi_pdno,bi_inoutno,bi_piclass,bi_barcode,bi_prodcode,bi_whcode,bi_inqty,");
            sql.Append("bi_madedate,bi_vendbarcode,bi_location,bi_status,bi_printstatus)");
            sql.Append(" values (barcodeio_seq.nextval," + PI_ID + "," + PD_ID + ",'" + pd_pdno + "','" + pd_inoutno.Text + "','" + pi_class + "',:barcode,'" + pd_prodcode.Text + "',nvl('" + pd_whcode + "','" + pi_whcode + "'),'");
            sql.Append(pr_zxbzs.Text + "',nvl( to_date('" + madedate + "', 'YYYY-MM-DD HH24:MI:SS'),to_date('" + pd_prodmadedate + "', 'YYYY-MM-DD HH24:MI:SS')),'" + bi_vendbarcode.Text + "','仓位', 0, 0)");
            if (num > 0) {    
                dh.BatchInsert(sql.ToString(),new string[] {"barcode"},barcode);
            }
            //有余数的话单独生成一条
            if (restqty != 0) {
                dh.BatchInsert(sql.ToString(), new string[] { "barcode" },new string[] { BarcodeMethod(pr_code, ve_id) } ); 
            }
            MessageBox.Show("生成条码成功");
            LoadData();
        }

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

        //生成条码的方法
        public static string BarcodeMethod(string pr_code, string ve_id) {
            StringBuilder code = new StringBuilder();
            DataTable dt1 = new DataTable();
            DataHelper dh = new DataHelper();
            string date = "0";
            dt1 =(DataTable) dh.ExecuteSql("select pr_id,pr_tracekind,pr_exbarcode from product where pr_code='"+pr_code+"'","select");
            if (dt1.Rows.Count > 0) {
                if (dt1.Rows[0]["pr_tracekind"].ToString() != "") {
                    if (int.Parse(dt1.Rows[0]["pr_tracekind"].ToString()) == 2)
                    { //批量管控
                        string pr_id = dt1.Rows[0]["pr_id"].ToString();
                        //清掉dt1的数据和结构
                        BaseUtil.CleanDataTable(dt1);
                        string[] field = { "bs_lenprid", "bs_datestr", "bs_lennum", "bs_maxnum", "bs_maxdate", "bs_lenveid" };
                        dt1 = dh.getFieldsDataByCondition("barcodeSet", field, "bs_type='BATCH'");
                        code.Append(lpad(int.Parse(dt1.Rows[0][0].ToString()), pr_id));
                        code.Append(lpad(int.Parse(dt1.Rows[0][5].ToString()), ve_id));
                        if (dt1.Rows[0][1].Equals("YYMMDD"))
                        {
                            SimpleDateFormat YMD = new SimpleDateFormat("yyMMdd");
                            date = YMD.Format(new DateTime());
                        }
                        else if (dt1.Rows[0][1].Equals("YYMM"))
                        {
                            SimpleDateFormat YM = new SimpleDateFormat("yyMM");
                            date = YM.Format(new DateTime());
                        }
                        else if (dt1.Rows[0][1].Equals("MMDD"))
                        {
                            SimpleDateFormat MD = new SimpleDateFormat("MMdd");
                            date = MD.Format(new DateTime());
                        }
                        code.Append(date);
                        dh.UpdateByCondition("barcodeSet", "bs_maxdate='" + date + "'", "bs_type='BATCH'");
                        if (!("").Equals(dt1.Rows[0][4]) && null != dt1.Rows[0][4] && (!date.Equals("0")) && (int.Parse(dt1.Rows[0][4].ToString()) > int.Parse(date)))
                        {
                            code.Append(lpad(int.Parse(dt1.Rows[0][2].ToString()), "1"));// 流水重新开始
                            dh.UpdateByCondition("barcodeSet", "bs_maxnum=2", "bs_type='BATCH'");
                        }
                        else
                        {
                            code.Append(lpad(int.Parse(dt1.Rows[0][2].ToString()), dt1.Rows[0][3].ToString()));// 当前流水号
                            dh.UpdateByCondition("barcodeSet", "bs_maxnum=bs_maxnum+1", "bs_type='BATCH'");// 流水号增加1
                        }
                    }
                    else
                    {
                        BaseUtil.ShowError("管控类型错误！");
                    }
                }
            }
            return code.ToString();
        }


        public static string outboxMethod(string pr_id, string kind) {

            StringBuilder code = new StringBuilder();
            DataHelper dh = new DataHelper();
            object pr_kind= dh.getFieldDataByCondition("product", "pr_tracekind", "pr_id=" + pr_id );
            if (pr_kind.ToString()!="") {
                if ((kind.Equals("1") && pr_kind.ToString() == "1") || (kind.Equals("2") && pr_kind.ToString() == "2"))
                {// 单间管控或者批管控
                    DataTable dt= dh.getFieldsDataByCondition("barcodeSet",new string[] { "bs_id ,bs_lenprid,bs_lennum,bs_maxnum" } ,"bs_type='PACK'");
                    if (dt.Rows.Count>0)
                    {
                        code.Append(lpad(int.Parse(dt.Rows[0]["bs_lenprid"].ToString()), pr_id));// PR_ID物料ID的长度
                        code.Append(lpad(int.Parse(dt.Rows[0]["bs_lennum"].ToString()),dt.Rows[0]["bs_maxnum"].ToString()));// 当前流水号
                        dh.UpdateByCondition("barcodeSet", "bs_maxnum=bs_maxnum+1", "bs_type='PACK' and  bs_id=" + dt.Rows[0]["bs_id"]);// 流水号增加1
                    }
                    else
                    {
                        BaseUtil.ShowError("未定义包装箱号产生规则或规则为审核");
                    }
                }
                else
                {
                   BaseUtil.ShowError("管控类型错误！");
                }
            }
            return code.ToString();
        }

        private void PrintCode_Click(object sender, EventArgs e)
        {
            
        }

        private void PrintAllBoxNum_Click(object sender, EventArgs e)
        {

        }

        private void deleteDetailButton1_Click(object sender, EventArgs e)
        {

        }

        private void saveButton1_Click(object sender, EventArgs e)
        {
            DataTable dt = (DataTable)dataGridView1.DataSource;
            dh.UpDateTableByCondition(dt.GetChanges(), "barcodeio", "bi_id");
        }

        private void deleteDetailButton1_Click_1(object sender, EventArgs e)
        {
            //新建一个和grid长度一样的数组
            string pd_status = dh.getFieldDataByCondition("prodiodetail", "pd_status", "pd_id=" +PD_ID).ToString();
            if (pd_status=="99") {
                BaseUtil.ShowError("单据已过账，不允许删除明细");
            }
            string[] deleteID = new string[dataGridView1.Rows.Count];
            foreach (DataGridViewRow dgvc in dataGridView1.Rows) {
                deleteID[dataGridView1.Rows.IndexOf(dgvc)] = dgvc.Cells["ID"].Value.ToString();
            }

            string bi_pdid=dh.getFieldDataByCondition("barcodeio", "bi_pdid", "bi_id=" + deleteID[0]).ToString();
            //删除PackageDetail表信息
            dh.ExecuteSql("delete from packagedetail where pd_outboxcode in (select bi_outboxcode from barcodeio where bi_pdid=" + bi_pdid + ")", "delete");
            //删除Package表的信息
            dh.ExecuteSql("delete from package where pa_outboxcode in (select bi_outboxcode from barcodeio where bi_pdid=" + bi_pdid + ")", "delete");
            //删除BarcodeIO表信息
            dh.DeleteDataByID("barcodeio", "bi_id", deleteID);
            MessageBox.Show("清除明细成功");
            LoadData();
        }

        /// <summary>
        /// 生成条码和箱号按钮
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void GenerateCodeAndBoxNum_Click(object sender, EventArgs e)
        {
            //调用生成条码的方法
            CheckBeforeGenCode();
            if (bi_boxnum.Text == "")
            {
                BaseUtil.ShowError("箱内总数必须填写");
            }
            else
            {
                int rest = int.Parse(bi_boxnum.Text) % int.Parse(pr_zxbzs.Text);
                if (rest != 0)
                {
                    BaseUtil.ShowError("箱内总是必须是最小包装量的整数倍");
                }
            }
            string ve_id = GetVendorID(PD_ID);
            DataTable dt = dh.getFieldsDataByCondition("prodiodetail", new string[] { "pd_prodcode,pd_prodid" }, "pd_id=" + PD_ID);
            string pr_code = dt.Rows[0]["pd_prodcode"].ToString();
            string pr_id = dt.Rows[0]["pd_prodid"].ToString();
            //箱内件数
            int numinpack = int.Parse(bi_boxnum.Text) / int.Parse(pr_zxbzs.Text);
            //装箱的数量和条码的数量
            int packnum = int.Parse(pd_qty.Text) / int.Parse(bi_boxnum.Text);
            int codenum =   int.Parse(pd_qty.Text) / int.Parse(pr_zxbzs.Text);
            //是否有剩余的最小包
            int restcode = int.Parse(pd_qty.Text) -(codenum* int.Parse(pr_zxbzs.Text)) ;
            //是否有剩余的尾箱
            int restpack = int.Parse(pd_qty.Text) -(packnum * int.Parse(bi_boxnum.Text));
            //记录箱号的数组
            
            //根据pd_id选出需要插入的数据
            dt = dh.getFieldsDataByCondition("prodiodetail left join prodinout on pd_piid=pi_id", new string[] { "pd_pdno", "pi_class", "pd_prodmadedate", "pi_date", "pd_whcode", "pi_whcode" }, "pd_id=" + PD_ID);
            object pd_pdno = dt.Rows[0]["pd_pdno"];
            object pi_class = dt.Rows[0]["pi_class"];
            object pi_date = dt.Rows[0]["pi_date"];
            object pd_prodmadedate = dt.Rows[0]["pd_prodmadedate"];
            object pi_whcode = dt.Rows[0]["pi_whcode"];
            object pd_whcode = dt.Rows[0]["pd_whcode"];
            string madedate = bi_madedate.Text;
            if (madedate.Trim() == "")
            {
                madedate = null;
            }
            else
            {
                madedate = bi_madedate.Value.ToString();
            }
            StringBuilder sql = new StringBuilder();
            //
           
            //如果剩余的箱数量大于0，则箱号+1
            if (restpack != 0) {
                packnum = packnum + 1;
            }
            string[] pa_id = new string[packnum];
            string[] packNum = new string[packnum];
            if (packnum > 0)
            {
                for (int i = 0; i < packnum; i++)
                {      
                    packNum[i] = outboxMethod(pr_id, "2");
                    //通过序列获取到ID    
                    pa_id[i] = dh.GetSEQ("PACKAGE_SEQ");
                }
                sql.Append("insert into package (");
                sql.Append("pa_id,pa_outboxcode,pa_prodcode, pa_packdate,pa_packageqty, pa_totalqty, pa_status, pa_indate)");
                sql.Append("values (:pa_id,:packnum,'" + pr_code + "', sysdate," + numinpack + "," + bi_boxnum.Text + ",'0',nvl( to_date('" + madedate + "', 'YYYY-MM-DD HH24:MI:SS'),to_date('" + pd_prodmadedate + "', 'YYYY-MM-DD HH24:MI:SS')))");
                dh.BatchInsert(sql.ToString(), new string[] { "pa_id", "packnum" }, pa_id, packNum);
            }
            sql.Clear();
            //记录条码号的数组
           
            //如果有余数则条码数量+1
            if (restcode != 0){
                codenum=codenum+1;
            }
            string[] codeNum = new string[codenum];
            string[] bi_outboxid = new string[codenum];
            string[] bi_outboxcode = new string[codenum];
            if (codenum > 0){
                for (int i = 0; i < codenum; i++)
                {
                    codeNum[i] = BarcodeMethod(pr_code, ve_id);
                }
                //包装数-1*箱内数量，先处理整箱的
                int k = 0;
                for (int i = 0; i < packnum-1; i++) {
                    for (int j = 0; j < numinpack; j++) {
                        bi_outboxid[k] = pa_id[i];
                        bi_outboxcode[k] = packNum[i];
                        k=k+1;
                    }
                }
            
                //最后单独处理剩余的一箱的条码，不论整箱还是有零件都可以
                //最后一箱的条码数量
                int remain = codenum - k;
                for (int i = 0; i <remain; i++) {
                    bi_outboxid[k] = pa_id[pa_id.Length - 1];
                    bi_outboxcode[k] = packNum[packNum.Length - 1];
                    k=k+1;
                }
          
                //每个条码需要存对应的箱号的ID
                sql.Append("insert into barcodeio (");
                sql.Append("bi_id,bi_piid,bi_pdid,bi_pdno,bi_inoutno,bi_piclass,bi_barcode,bi_prodcode,bi_whcode,bi_inqty,");
                sql.Append("bi_madedate,bi_vendbarcode,bi_location,bi_status,bi_printstatus,bi_outboxcode,bi_outboxid)");
                sql.Append(" values (barcodeio_seq.nextval," + PI_ID + "," + PD_ID + ",'" + pd_pdno + "','" + pd_inoutno.Text + "','" + pi_class + "',:barcode,'" + pd_prodcode.Text + "',nvl('" + pd_whcode + "','" + pi_whcode + "'),'");
                sql.Append(pr_zxbzs.Text + "',nvl( to_date('" + madedate + "', 'YYYY-MM-DD HH24:MI:SS'),to_date('" + pd_prodmadedate + "', 'YYYY-MM-DD HH24:MI:SS')),'" + bi_vendbarcode.Text + "','仓位',0,0,:packcode,:pa_id)");
                dh.BatchInsert(sql.ToString(), new string[] { "barcode","packcode","pa_id" },codeNum, bi_outboxcode,bi_outboxid);

                  
            }


            sql.Clear();
            //insert into packageDetail(pd_id, pd_paid, pd_outboxcode, pd_barcode, pd_innerqty)values(PACKAGEDETAIL_SEQ, pd_id, 箱号, 条码号, 条码数量);
            sql.Append("insert into packageDetail(pd_id, pd_paid, pd_outboxcode, pd_barcode, pd_innerqty) values (");
            sql.Append("PACKAGEDETAIL_SEQ.nextval,:pd_paid,:packcode,:barcode,"+pd_qty.Text+")");
            dh.BatchInsert(sql.ToString(), new string[] { "pd_paid","packcode", "barcode" },bi_outboxid,bi_outboxcode, codeNum);
            MessageBox.Show("成功生成条码和箱号");
            LoadData();
        }

        /// <summary>
        /// 通过pd_id获取供应商的ID
        /// </summary>
        /// <param name="pd_id"></param>
        /// <returns></returns>
        public string GetVendorID(string pd_id) {
            DataHelper dh = new DataHelper();
            string pi_id=dh.getFieldDataByCondition("prodiodetail", "pd_piid", "pd_id=" + pd_id).ToString() ;
            PI_ID = pi_id;
            object ve_id=dh.getFieldDataByCondition("Vendor left join ProdInOut on pi_cardcode=ve_code", "ve_id", "pi_id=" + pi_id);
            if (ve_id != null)
            {
                return ve_id.ToString();
            }
            else {
                return "0";
            }
        }

        private void ValueTransform() {
           
            switch (pr_tracekind.Text) {
                case "":
                    pr_tracekind.Text = "不管控";
                    break;
                case "0":
                    pr_tracekind.Text = "不管控";
                    break;
                case "1":
                    pr_tracekind.Text = "单间管控";
                    break;
                case "2":
                    pr_tracekind.Text = "批量管控";
                    break;
            }
        }

        private void CheckBeforeGenCode() {
            if (pr_tracekind.Text == "单件管控")
            {
                BaseUtil.ShowError("管控类型为单件管控时不允许生成条码");
            }
            if (pd_qty.Text != "")
            {
                if (!(int.Parse(pd_qty.Text) > 0) || !(int.Parse(pr_zxbzs.Text) > 0))
                {
                    BaseUtil.ShowError("批总量，最小包装数必须大于0");
                }
                if (int.Parse(pd_qty.Text) > int.Parse(pd_inqty.Text))
                {
                    BaseUtil.ShowError("批总量不允许大于来料总量！");
                }
            }
            else
            {
                BaseUtil.ShowError("请填写批总量！");
            }

            //判断是否已经入库，状态为99表示已经入库
            if (dh.getFieldDataByCondition("prodiodetail", "pd_status", "pd_id=" + PD_ID).ToString().Equals("99"))
            {
                BaseUtil.ShowError("该批次已入库，不允许生成条码");
            }
            object checknum = dh.getFieldDataByCondition("barcodeio", "sum(bi_inqty)", "bi_pdid=" + PD_ID);
            if (checknum != null && checknum.ToString() != "")
            {
                if (int.Parse(pd_qty.Text) > int.Parse(pd_inqty.Text) - int.Parse(checknum.ToString()))
                {
                    BaseUtil.ShowError("批总量不能大于未生成条码数");
                }
            }
        }

        private void LoadData() {
            dataGridView1.DataSource = dh.GetConfigureData("ProdInOut!BarcodeIn", "detailgrid", "bi_pdid=" + PD_ID);
        }

        private void NumOnly()
        {
            bi_boxnum.KeyPress += BaseUtil.NumOnly;
            pr_zxbzs.KeyPress += BaseUtil.NumOnly;
            pd_qty.KeyPress += BaseUtil.NumOnly;
        }
    }
}