public int CreateWorkProcess(WorkProcess newWorkProcess)
        {
            int result = -1;
            try
            {
                conn = db.openConn();
                tr = conn.BeginTransaction();
                sb = new StringBuilder();
                sb.Remove(0, sb.Length);
                sb.Append("INSERT INTO tbWorkprocess(psnCode,psnFullName,wpDate,wpLast,wpOt,wplacking,wpmsalary,wpmOt,wpOrther)");
                sb.Append(" VALUES (@psnCode,@psnFullName,@wpDate,@wpLast,@wpOt,@wplacking,@wpmsalary,@wpmOt,@wpOrther)");

                string sqlsave;
                sqlsave = sb.ToString();

                comm = new SqlCommand();
                comm.Connection = conn;
                comm.Transaction = tr;
                comm.CommandText = sqlsave;
                comm.Parameters.Clear();
                comm.Parameters.Add("@psnCode", SqlDbType.NVarChar).Value = newWorkProcess.psnCode;
                comm.Parameters.Add("@psnFullName", SqlDbType.NVarChar).Value = newWorkProcess.psnFullName;
                comm.Parameters.Add("@wpDate", SqlDbType.NVarChar).Value = newWorkProcess.wpDate;
                comm.Parameters.Add("@wpLast", SqlDbType.NVarChar).Value = newWorkProcess.wpLast;
                comm.Parameters.Add("@wpOt", SqlDbType.NVarChar).Value = newWorkProcess.wpOt;
                comm.Parameters.Add("@wplacking", SqlDbType.NVarChar).Value = newWorkProcess.wplacking;
                comm.Parameters.Add("@wpmsalary", SqlDbType.NVarChar).Value = newWorkProcess.wpmsalary;
                comm.Parameters.Add("@wpmOt", SqlDbType.NVarChar).Value = newWorkProcess.wpmOt;
                comm.Parameters.Add("@wpOrther", SqlDbType.NVarChar).Value = newWorkProcess.wpOrther;
                comm.ExecuteNonQuery();
                tr.Commit();

                result = 1;

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

            }
            finally
            {
                conn.Close();
            }
            return result;
        }
        public int UpdateWorkProcess(WorkProcess _workProcess)
        {
            int result = -1;
            try
            {
                conn = db.openConn();
                tr = conn.BeginTransaction();

                sb = new StringBuilder();

                sb.Remove(0, sb.Length);
                sb.Append(" UPDATE tbWorkprocess ");
                sb.Append(" SET wpLast='" + _workProcess.wpLast + "',");
                sb.Append(" wpOt='" + _workProcess.wpOt + "',");
                sb.Append(" wplacking='"+_workProcess.wplacking +"',");
                sb.Append(" wpmOt='" + _workProcess.wpmOt + "',");
                sb.Append(" wpOrther='"+_workProcess.wpOrther +"'");
                sb.Append(" WHERE (psnCode='" + _workProcess.psnCode  + "')");
                sb.Append(" AND wpDate='"+_workProcess.wpDate +"'");

                string sqlUpdate;
                sqlUpdate = sb.ToString();

                comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = sqlUpdate;
                comm.Transaction = tr;
                comm.Parameters.Clear();
                comm.ExecuteNonQuery();
                tr.Commit();

                result = 1;

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

            }
            finally
            {
                conn.Close();
            }

            return result;
        }
        public List<WorkProcess> getWorkProcessBydate(string _startDate, string _endDate,string _psnCode)
        {
            List<WorkProcess> workProcess = new List<WorkProcess>();
            WorkProcess work = null;

            try
            {
                conn = db.openConn();
                sb = new StringBuilder();

                sb.Remove(0, sb.Length);
                sb.Append(" SELECT ID,psnCode,psnFullName,wpDate,wptext,wpLast,wpOt,wplacking,wpmsalary,wpmOt,wpOrther FROM tbWorkprocess ");
                sb.Append(" WHERE wpDate BETWEEN '" + _startDate + "' AND '" + _endDate + "'");
                sb.Append(" AND psnCode='" + _psnCode + "'");

                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)
                    {
                        work = new WorkProcess();
                        work.ID = Convert.ToInt32(drw["ID"].ToString());
                        work.psnCode = drw["psnCode"].ToString();
                        Person p = new Person();
                        p = personService.getPersonBypsnCode(drw["psnCode"].ToString());

                        work.psnFullName = p.psnPreFix + " " + p.psnName + " " + p.psnLastName;

                        work.wpDate = drw["wpDate"].ToString();
                        work.wptext = drw["wptext"].ToString();
                        work.wpLast = Convert.ToInt32(drw["wpLast"].ToString());
                        work.wpOt = Convert.ToInt32(drw["wpOt"].ToString());
                        work.wplacking = Convert.ToInt32(drw["wplacking"].ToString());
                        work.wpmsalary = Convert.ToInt32(p.pssalary);
                        work.wpmOt = Convert.ToInt32(drw["wpmOt"].ToString());
                        work.wpOrther = drw["wpOrther"].ToString();
                        workProcess.Add(work);
                    }

                }

                dr.Close();

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

            }
            finally
            {
                conn.Close();
            }

            return workProcess;
        }
        private void cmdSave_Click(object sender, EventArgs e)
        {
            WorkProcess _workprocess ;

            if (workProcess != null && workProcess.Count > 0) {

                foreach (DataGridViewRow r in dgvList.Rows)
                {
                    _workprocess  = new WorkProcess();

                    _workprocess.psnCode = r.Cells[1].Value.ToString();
                    _workprocess.wpDate = r.Cells[3].Value.ToString();
                    _workprocess.wpLast = Convert.ToInt32(r.Cells[5].Value.ToString());
                    _workprocess.wpOt = Convert.ToInt32(r.Cells[6].Value.ToString());
                    _workprocess.wplacking = Convert.ToInt32(r.Cells[7].Value.ToString());
                    _workprocess.wpmOt = Convert.ToInt32(r.Cells[9].Value.ToString());
                    _workprocess.wpOrther = "";

                    int result = workprocessUpdateService.UpdateWorkProcess(_workprocess);

                    if (result > -1)
                    {
                        Console.WriteLine("Update Complete");
                        lblresult.Visible = true;
                        lblresult.Text = " บันทึกเรียบร้อย ";
                    }
                    else
                    {
                        Console.WriteLine("Update Not Complete");
                        lblresult.Visible = true;
                        lblresult.Text = " ไม่สามารถบันทึกข้อมูลได้";

                    }

                }

            }
        }