private void btnGen_Click(object sender, EventArgs e)
        {
            if (txtNumber.Text.Trim() == "")
            {
                MessageBox.Show("กรุณาป้อน จำนวน ก่อน !!!", "ผลการตรวจสอบ", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                txtNumber.Focus();
                return;
            }

            FinshingLabel flabel = new FinshingLabel();
            int _genNumber =0;
            // completeNo
            flabel.CompleteNo = cboComplete.SelectedValue.ToString();
            // date
            string _date = String.Format("{0:dd/MM/yyyy}", dtbDate.Value);
            flabel.ProductTextDate = _date;
            // line
            flabel.Line = cboline.Text.Trim();
            // shift
            flabel.Shift = cboShift.Text.Trim();

            // rev
            flabel.Rev = lblrevision.Text.Trim();
            //planceName
            flabel.PlaceName = lblplacename.Text.Trim();

            // number run
            _genNumber = Convert.ToInt32(txtNumber.Text.Trim());

            finshingLabels = finishService.GenBarcode(flabel, _genNumber);
            dgvList.DataSource = finshingLabels;
            dgvList.ReadOnly = true;
            FormatdgvList();
        }
        public int CreateFinishingLabel(FinshingLabel newFinshingLabel)
        {
            int result = -1;
            try
            {
                conn = db.openConn();
                tr = conn.BeginTransaction();
                sb = new StringBuilder();
                sb.Remove(0, sb.Length);
                sb.Append("INSERT INTO finishing_labels(barcode,complete_no,rev,place_name,product_barcode_date,product_text_date,line,shift,serial,print_date,user_create_name,status)");
                sb.Append(" VALUES (@barcode,@complete_no,@rev,@place_name,@product_barcode_date,@product_text_date,@line,@shift,@serial,@print_date,@user_create_name,@status)");

                string sqlsave;
                sqlsave = sb.ToString();

                comm = new SqlCommand();
                comm.Connection = conn;
                comm.Transaction = tr;
                comm.CommandText = sqlsave;
                comm.Parameters.Clear();
                comm.Parameters.Add("@barcode", SqlDbType.NVarChar).Value = newFinshingLabel.Barcode;
                comm.Parameters.Add("@complete_no", SqlDbType.NVarChar).Value = newFinshingLabel.CompleteNo;
                comm.Parameters.Add("@rev", SqlDbType.NVarChar).Value = newFinshingLabel.Rev;
                comm.Parameters.Add("@place_name", SqlDbType.NVarChar).Value = newFinshingLabel.PlaceName;
                comm.Parameters.Add("@product_barcode_date", SqlDbType.NVarChar).Value = newFinshingLabel.ProductBarCodeDate;
                comm.Parameters.Add("@product_text_date", SqlDbType.NVarChar).Value = newFinshingLabel.ProductTextDate;
                comm.Parameters.Add("@line", SqlDbType.NVarChar).Value = newFinshingLabel.Line;
                comm.Parameters.Add("@shift", SqlDbType.NVarChar).Value = newFinshingLabel.Shift;
                comm.Parameters.Add("@serial", SqlDbType.NVarChar).Value = newFinshingLabel.Serial;
                comm.Parameters.Add("@print_date", SqlDbType.DateTime).Value = newFinshingLabel.PrintDate;
                comm.Parameters.Add("@user_create_name", SqlDbType.NVarChar).Value = newFinshingLabel.UserCreateName;
                comm.Parameters.Add("@status", SqlDbType.NVarChar).Value = newFinshingLabel.Status ;
                comm.ExecuteNonQuery();
                tr.Commit();

                result = 1;

            }
            catch (Exception ex)
            {
                tr.Rollback();
                conn.Close();
                return result;
                throw ex;

            }
            finally
            {
                conn.Close();
            }
            return result;
        }
        private void dgvList_CellMouseUp(object sender, DataGridViewCellMouseEventArgs e)
        {
            if (e.RowIndex == -1)
            {
                return;
            }

            string _barcode = "";
            _barcode = dgvList.Rows[e.RowIndex].Cells[1].Value.ToString();

            finshingLabel = finishService.GetByBarCode(_barcode);
        }
        public List<FinshingLabel> GetByBarCodes(string _completeno,string _product_text_date)
        {
            List<FinshingLabel> finshingLabels = new List<FinshingLabel>();
            FinshingLabel fbl = null;
            try
            {
                conn = db.openConn();
                sb = new StringBuilder();
                sb.Remove(0, sb.Length);
                sb.Append(" select barcode,complete_no,rev,place_name,product_barcode_date,product_text_date,line,shift,serial,print_date,user_create_name,status from  dbo.finishing_labels");
                sb.Append(" where  complete_no  ='" + _completeno + "'");
                sb.Append(" and  product_text_date  ='" + _product_text_date + "'");

                string sql;
                sql = sb.ToString();
                comm = new SqlCommand();
                comm.CommandText = sql;
                comm.CommandType = CommandType.Text;
                comm.Connection = conn;
                dr = comm.ExecuteReader();
                if (dr.HasRows)
                {

                    DataTable dt = new DataTable();
                    dt.Load(dr);
                    foreach (DataRow drw in dt.Rows)
                    {
                        fbl = new FinshingLabel();
                        fbl.Barcode = drw["barcode"].ToString();
                        fbl.CompleteNo = drw["complete_no"].ToString();
                        fbl.Rev = drw["rev"].ToString();
                        fbl.PlaceName = drw["place_name"].ToString();
                        fbl.ProductBarCodeDate = drw["product_barcode_date"].ToString();
                        fbl.ProductTextDate = drw["product_text_date"].ToString();
                        fbl.Shift = drw["line"].ToString();
                        fbl.Line = drw["shift"].ToString();
                        fbl.Serial = Convert.ToString(drw["serial"].ToString());
                        fbl.PrintDate = Convert.ToDateTime(drw["print_date"].ToString());
                        fbl.UserCreateName = drw["user_create_name"].ToString();
                        fbl.Status = drw["status"].ToString();
                        finshingLabels.Add(fbl);
                    }
                }

            }
            catch (Exception ex)
            {
                dr.Close();
                conn.Close();
                return null;
                throw ex;

            }
            finally
            {
                conn.Close();
            }

            return finshingLabels;
        }
        public List<FinshingLabel> GenBarcode(FinshingLabel _finshingLabel,int genNumber)
        {
            string _barcode = "";
            List<FinshingLabel> finshingLabels = new List<FinshingLabel>();

            //_finshingLabel.Rev = "00";
            //_finshingLabel.PlaceName = "TOPLA";
            string[] arr = Regex.Split(_finshingLabel.ProductTextDate, "/");
            int _year = Convert.ToInt32(arr[2]) /*- 543*/;
            _finshingLabel.ProductTextDate = arr[0] + "/" + arr[1] + "/" + _year;
            _finshingLabel.ProductBarCodeDate = StringTools.Right(Convert.ToString(_year),2) + arr[1] + arr[0];

            /*
            int year = Convert.ToInt32(DateTime.Now.Year.ToString("0000"));
            string _dNow = DateTime.Now.Day.ToString("00") + "/" + DateTime.Now.Month.ToString("00") + "/" + year.ToString();
            */

            FinshingLabel flabel;
            // หา serialNo ล่าสุด
            // int _lastSerial = this.GetLastSerial(_finshingLabel.CompleteNo, _finshingLabel.ProductTextDate, _finshingLabel.Line, _finshingLabel.Shift);
            int _lastSerial = this.GetLastSerial(_finshingLabel.ProductTextDate);

            if (_lastSerial == 0)
            {
                _lastSerial = 1;
            }
            else {
                _lastSerial++;
            }
            int j = 0;
            genNumber = genNumber + _lastSerial;
            for (j = _lastSerial; j < genNumber ; j++)
            {
                flabel = new FinshingLabel();
                flabel.Barcode = _finshingLabel.CompleteNo+"!"+_finshingLabel.Rev+"!" ;
                flabel.Barcode += _finshingLabel.PlaceName+"!"+_finshingLabel.ProductBarCodeDate+"!"+_finshingLabel.Line+"!";
                flabel.Barcode += _finshingLabel.Shift+"!"+j.ToString("00000");
                flabel.CompleteNo = _finshingLabel.CompleteNo;
                flabel.Rev = _finshingLabel.Rev;
                flabel.PlaceName = _finshingLabel.PlaceName;
                flabel.ProductBarCodeDate = _finshingLabel.ProductBarCodeDate;
                flabel.ProductTextDate = _finshingLabel.ProductTextDate;
                flabel.Shift = _finshingLabel.Shift;
                flabel.Line = _finshingLabel.Line;
                flabel.Serial = Convert.ToString(j);
                flabel.PrintDate = DateTime.Now;
                flabel.UserCreateName = "admin";
                flabel.Status = "1";

                finshingLabels.Add(flabel);

            }

            return finshingLabels;
        }