public int CreatePaintingLabel(PaintingLabels newPaintingLabels)
{
int result = -1;
try
{
conn = db.openConn();
tr = conn.BeginTransaction();
sb = new StringBuilder();
sb.Remove(0, sb.Length);
sb.Append("INSERT INTO painting_labels(barcode,part,product_date,serial,shift,rev,color_no,process,mold_no,user_create_name,create_date,status)");
sb.Append(" VALUES (@barcode,@part,@product_date,@serial,@shift,@rev,@color_no,@process,@mold_no,@user_create_name,@create_date,@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 = newPaintingLabels.BarCode;
comm.Parameters.Add("@part", SqlDbType.NVarChar).Value = newPaintingLabels.Part;
comm.Parameters.Add("@product_date", SqlDbType.NVarChar).Value = newPaintingLabels.ProductDate;
comm.Parameters.Add("@serial", SqlDbType.NVarChar).Value = newPaintingLabels.Serial;
comm.Parameters.Add("@shift", SqlDbType.NVarChar).Value = newPaintingLabels.Shift;
comm.Parameters.Add("@rev", SqlDbType.NVarChar).Value = newPaintingLabels.Rev;
comm.Parameters.Add("@color_no", SqlDbType.NVarChar).Value = newPaintingLabels.ColorNo;
comm.Parameters.Add("@process", SqlDbType.NVarChar).Value = newPaintingLabels.ProCess;
comm.Parameters.Add("@mold_no", SqlDbType.NVarChar).Value = newPaintingLabels.Mold;
comm.Parameters.Add("@user_create_name", SqlDbType.NVarChar).Value = newPaintingLabels.UserCreateName;
comm.Parameters.Add("@create_date", SqlDbType.DateTime).Value = newPaintingLabels.CreateDate;
comm.Parameters.Add("@status", SqlDbType.NVarChar).Value = newPaintingLabels.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 btnGen_Click(object sender, EventArgs e)
{
if (cbopart.Text.Trim() == "")
{
MessageBox.Show("กรุณาเลือก Part ก่อน !!!", "ผลการตรวจสอบ", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
cbopart.Focus();
return;
}
if (txtNumber.Text.Trim() == "")
{
MessageBox.Show("กรุณาป้อน จำนวน ก่อน !!!", "ผลการตรวจสอบ", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
txtNumber.Focus();
return;
}
PaintingLabels p = new PaintingLabels();
int _genNumber = 0;
p.Part = cbopart.SelectedValue.ToString();
// date
string _date = String.Format("{0:dd/MM/yyyy}", dtbDate.Value);
p.ProductDate = _date;
// shift
p.Shift = cboShift.Text.Trim();
p.Mold = cbomold.Text.Trim();
p.Rev = lblRevision.Text.Trim();
p.ColorNo = lblColor.Text.Trim();
p.ProCess = lblProcess.Text.Trim();
// number run
_genNumber = Convert.ToInt32(txtNumber.Text.Trim());
paintingLabels = paintingService.GenBarcode(p, _genNumber);
dgvList.DataSource = paintingLabels;
dgvList.ReadOnly = true;
FormatdgvList();
}
private void txtSearch_PreviewKeyDown(object sender, PreviewKeyDownEventArgs e)
{
if (e.KeyValue == 13)
{
List<PaintingLabels> paintingLabels = new List<PaintingLabels>();
paintingLabels = paintingService.GetByBarCodes(txtSearch.Text.Trim());
if (paintingLabels != null && paintingLabels.Count > 0)
{
dgvList.DataSource = paintingLabels;
dgvList.ReadOnly = true;
FormatdgvList();
}
else
{
dgvList.DataSource = null;
lblresult.Visible = true;
lblresult.Text = " ไม่พบข้อมูล";
}
}
}
private void dgvList_CellMouseUp(object sender, DataGridViewCellMouseEventArgs e)
{
if (e.RowIndex == -1)
{
return;
}
string _barcode = "";
_barcode = dgvList.Rows[e.RowIndex].Cells[1].Value.ToString();
paintingLabels = paintingService.GetByBarCode(_barcode);
}
public List<PaintingLabels> GetByBarCodes(string _part,string _date)
{
List<PaintingLabels> paintingLabels = new List<PaintingLabels>();
PaintingLabels fbl = null;
try
{
conn = db.openConn();
sb = new StringBuilder();
sb.Remove(0, sb.Length);
sb.Append(" select barcode,part,product_date,serial,shift,rev,color_no,process,mold_no,user_create_name,create_date,status from dbo.painting_labels");
sb.Append(" where part ='" + _part + "'");
sb.Append(" and _date ='" + _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 PaintingLabels();
fbl.BarCode = drw["barcode"].ToString();
fbl.Part = drw["part"].ToString();
fbl.ProductDate = drw["product_date"].ToString();
fbl.Serial = drw["serial"].ToString();
fbl.Shift = drw["shift"].ToString();
fbl.Rev = drw["rev"].ToString();
fbl.ColorNo = drw["color_no"].ToString();
fbl.ProCess = drw["process"].ToString();
fbl.Mold = drw["mold_no"].ToString();
fbl.UserCreateName = drw["user_create_name"].ToString();
fbl.CreateDate = Convert.ToDateTime(drw["create_date"].ToString());
fbl.Status = drw["status"].ToString();
paintingLabels.Add(fbl);
}
}
}
catch (Exception ex)
{
dr.Close();
conn.Close();
return null;
throw ex;
}
finally
{
conn.Close();
}
return paintingLabels;
}
public List<PaintingLabels> GenBarcode(PaintingLabels _paintingLabels, int genNumber)
{
List<PaintingLabels> paintingLabels = new List<PaintingLabels>();
string[] arr = Regex.Split(_paintingLabels.ProductDate, "/");
int _year = Convert.ToInt32(arr[2]) /*- 543*/;
_paintingLabels.ProductDate = arr[0] + "/" + arr[1] + "/" + _year;
//_paintingLabels.ProductDate = StringTools.Right(Convert.ToString(_year), 2) + arr[1] + arr[0];
/*
_paintingLabels.Rev = "00";
_paintingLabels.ColorNo = "T-MGR1007";
_paintingLabels.ProCess = "MGR1007";*/
PaintingLabels p;
int _lastSerial = this.GetLastSerial(_paintingLabels.Part, _paintingLabels.ProductDate, _paintingLabels.Shift);
if (_lastSerial == 0)
{
_lastSerial = 1;
}
else
{
_lastSerial++;
}
int j = 0;
genNumber = genNumber + _lastSerial;
for (j = _lastSerial; j < genNumber; j++)
{
p = new PaintingLabels();
p.BarCode = _paintingLabels.Part + "!" + _paintingLabels.ProductDate + "!" ;
p.BarCode += j.ToString("00000") + "!" + _paintingLabels.Shift + "!" + _paintingLabels.Rev+"!";
p.BarCode += _paintingLabels.ColorNo + "!Paint," + _paintingLabels.ProCess;
p.Part = _paintingLabels.Part;
p.ProductDate = _paintingLabels.ProductDate;
p.Serial = Convert.ToString(j);
p.Shift = _paintingLabels.Shift;
p.Rev = _paintingLabels.Rev;
p.ColorNo = _paintingLabels.ColorNo;
p.ProCess = _paintingLabels.ProCess;
p.Mold = _paintingLabels.Mold;
p.UserCreateName = "admin";
p.CreateDate = DateTime.Now ;
p.Status = "1";
paintingLabels.Add(p);
}
return paintingLabels;
}
