public int CreatePaySalary(PaySalary newPaySalary)
{
int result = -1;
try
{
conn = db.openConn();
tr = conn.BeginTransaction();
sb = new StringBuilder();
sb.Remove(0, sb.Length);
sb.Append("INSERT INTO tbPaySalary(payID,psnCode,payStartDate,payEndDate,period,salary,revenceAll,netIncome,total,pSalary,pOt,pWork,ppSo,psLa,psLast,psLess,psReam,psIns,psPenalty)");
sb.Append(" VALUES (@payID,@psnCode,@payStartDate,@payEndDate,@period,@salary,@revenceAll,@netIncome,@total,@pSalary,@pOt,@pWork,@ppSo,@psLa,@psLast,@psLess,@psReam,@psIns,@psPenalty)");
string sqlsave;
sqlsave = sb.ToString();
comm = new SqlCommand();
comm.Connection = conn;
comm.Transaction = tr;
comm.CommandText = sqlsave;
comm.Parameters.Clear();
comm.Parameters.Add("@payID", SqlDbType.NVarChar).Value = newPaySalary.payID;
comm.Parameters.Add("@psnCode", SqlDbType.NVarChar).Value = newPaySalary.psnCode;
comm.Parameters.Add("@payStartDate", SqlDbType.NVarChar).Value = newPaySalary.payStartDate;
comm.Parameters.Add("@payEndDate", SqlDbType.NVarChar).Value = newPaySalary.payEndDate;
comm.Parameters.Add("@period", SqlDbType.NVarChar).Value = newPaySalary.period;
comm.Parameters.Add("@salary", SqlDbType.NVarChar).Value = newPaySalary.salary;
comm.Parameters.Add("@revenceAll", SqlDbType.NVarChar).Value = newPaySalary.revenceAll;
comm.Parameters.Add("@netIncome", SqlDbType.NVarChar).Value = newPaySalary.netIncome;
comm.Parameters.Add("@total", SqlDbType.NVarChar).Value = newPaySalary.total;
comm.Parameters.Add("@pSalary", SqlDbType.NVarChar).Value = newPaySalary.pSalary;
comm.Parameters.Add("@pOt", SqlDbType.NVarChar).Value = newPaySalary.pOt;
comm.Parameters.Add("@pWork", SqlDbType.NVarChar).Value = newPaySalary.pWork;
comm.Parameters.Add("@ppSo", SqlDbType.NVarChar).Value = newPaySalary.ppSo;
comm.Parameters.Add("@psLa", SqlDbType.NVarChar).Value = newPaySalary.psLa;
comm.Parameters.Add("@psLast", SqlDbType.NVarChar).Value = newPaySalary.psLast;
comm.Parameters.Add("@psLess", SqlDbType.NVarChar).Value = newPaySalary.psLess;
comm.Parameters.Add("@psReam", SqlDbType.NVarChar).Value = newPaySalary.psReam;
comm.Parameters.Add("@psIns", SqlDbType.NVarChar).Value = newPaySalary.psIns;
comm.Parameters.Add("@psPenalty", SqlDbType.NVarChar).Value = newPaySalary.psPenalty;
comm.ExecuteNonQuery();
tr.Commit();
result = 1;
}
catch (Exception ex)
{
tr.Rollback();
conn.Close();
return result;
throw ex;
}
finally
{
conn.Close();
}
return result;
}
public PaySalary getPaySalaryByPayId(string _payId)
{
PaySalary paySalary = null;
try
{
conn = db.openConn();
sb = new StringBuilder();
sb.Remove(0, sb.Length);
sb.Append(" SELECT ID,payID,psnCode,payStartDate,payEndDate,period,salary,revenceAll,netIncome,total,pSalary,pOt,pWork,ppSo,psLa,psLast,psLess,psReam,psIns,psPenalty FROM tbPaySalary ");
sb.Append(" WHERE payID='"+ _payId +"'");
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)
{
paySalary = new PaySalary();
paySalary.ID = drw["ID"].ToString();
paySalary.payID = drw["payID"].ToString();
paySalary.psnCode = drw["psnCode"].ToString();
paySalary.payStartDate = drw["payStartDate"].ToString();
paySalary.payEndDate = drw["payEndDate"].ToString();
paySalary.period = drw["period"].ToString();
paySalary.revenceAll = drw["revenceAll"].ToString();
paySalary.netIncome = drw["netIncome"].ToString();
paySalary.total =Convert.ToInt32(drw["total"].ToString());
// รายได้
paySalary.pSalary = Convert.ToInt32(drw["pSalary"].ToString());
paySalary.pOt = Convert.ToInt32(drw["pOt"].ToString());
paySalary.pWork = Convert.ToInt32(drw["pWork"].ToString());
// รายการหัก
paySalary.ppSo = Convert.ToInt32(drw["ppSo"].ToString());
paySalary.psLa = Convert.ToInt32(drw["psLa"].ToString());
paySalary.psLast = Convert.ToInt32(drw["psLast"].ToString());
paySalary.psLess = Convert.ToInt32(drw["psLess"].ToString());
paySalary.psReam = Convert.ToInt32(drw["psReam"].ToString());
paySalary.psIns = Convert.ToInt32(drw["psIns"].ToString());
paySalary.psPenalty = Convert.ToInt32(drw["psPenalty"].ToString());
}
}
dr.Close();
}
catch (Exception ex)
{
dr.Close();
conn.Close();
return null;
throw ex;
}
finally
{
conn.Close();
}
return paySalary;
}
private void cmdSave_Click(object sender, EventArgs e)
{
if (txtpsnCode.Text.Trim() == "")
{
MessageBox.Show("กรุณาเลือกพนักงานก่อน !!!", "ผลการตรวจสอบ", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
txtpsnCode.Focus();
return;
}
PaySalary newPaySalary = new PaySalary();
newPaySalary.payID = txtpayID.Text.Trim();
newPaySalary.psnCode = txtpsnCode.Text.Trim();
int _year = Convert.ToInt32(dtppaystart.Value.Year.ToString("0000"));
string _dStart = dtppaystart.Value.Day.ToString("00") + "/" + dtppaystart.Value.Month.ToString("00") + "/" + (_year + 543);
newPaySalary.payStartDate =_dStart;
int year = Convert.ToInt32(dtpend.Value.Year.ToString("0000"));
string _dEnd = dtpend.Value.Day.ToString("00") + "/" + dtpend.Value.Month.ToString("00") + "/" + (year+543);
newPaySalary.payEndDate = _dEnd;
newPaySalary.period =txtperiod.Text.Trim();
newPaySalary.salary = txtnetincome.Text.Trim();
newPaySalary.revenceAll = txtrevanceAll.Text.Trim();
newPaySalary.netIncome= txtnetincome.Text.Trim();
newPaySalary.total = Convert.ToInt32(txtotal.Text.Trim());
// รายได้
newPaySalary.pSalary = Convert.ToInt32(txtpsalary.Text.Trim());
newPaySalary.pOt = Convert.ToInt32(txtpot.Text.Trim());
newPaySalary.pWork = Convert.ToInt32(txtpwork.Text.Trim());
// รายการหัก
newPaySalary.ppSo = Convert.ToInt32(txtppso.Text.Trim()) ;
newPaySalary.psLa = Convert.ToInt32(txtpsla.Text.Trim()) ;
newPaySalary.psLast = Convert.ToInt32(txtpslast.Text.Trim());
newPaySalary.psLess = Convert.ToInt32(txtpsless.Text.Trim());
newPaySalary.psReam = Convert.ToInt32(txtream.Text.Trim());
newPaySalary.psIns = Convert.ToInt32(txtpsins.Text.Trim());
newPaySalary.psPenalty = Convert.ToInt32(txtpspenalty.Text.Trim());
int year2 = Convert.ToInt32(DateTime.Now.Year.ToString("0000"));
string _dPayDate = DateTime.Now.Day.ToString("00") + "/" + DateTime.Now.Month.ToString("00") + "/" + (year2 + 543);
newPaySalary.payDate = _dPayDate;
int result = paySalaryService.CreatePaySalary(newPaySalary);
if (result > -1)
{
Console.WriteLine("Insert Complete");
lblresult.Visible = true;
lblresult.Text = " บันทึกเรียบร้อย ";
}
else
{
Console.WriteLine("Insert Not Complete");
lblresult.Visible = true;
lblresult.Text = " ไม่สามารถบันทึกข้อมูลได้";
}
}