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 = " ไม่สามารถบันทึกข้อมูลได้";
}
}
}
}