private void cmdSave_Click(object sender, EventArgs e)
{
if (txtpsnCode.Text.Trim() == "")
{
MessageBox.Show("กรุณาเลือกพนักงานก่อน !!!", "ผลการตรวจสอบ", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
txtpsnCode.Focus();
return;
}
if (txtinsurance.Text.Trim() == "")
{
MessageBox.Show("กรุณาป้อนเงินประกันก่อน !!!", "ผลการตรวจสอบ", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
txtinsurance.Focus();
return;
}
Insurance newInsurance = new Insurance();
Person p = new Person();
p = personService.getPersonBypsnCode(txtpsnCode.Text.Trim());
newInsurance.person = p;
InsuranceDetail insuranceDetail = new InsuranceDetail();
insuranceDetail.insurance = newInsurance;
string _date = String.Format("{0:dd/MM/yyyy}", DateTime.Now);
insuranceDetail.indate = _date;
insuranceDetail.amount =Convert.ToInt32(txtinsurance.Text.Trim());
List<InsuranceDetail> insuranceDetails = new List<InsuranceDetail>();
insuranceDetails.Add(insuranceDetail);
newInsurance.insuranceDetails = insuranceDetails;
int result = 0;
Insurance insuranceOld = insuranceService.getInsuranceBypsnCode(txtpsnCode.Text.Trim());
if (insuranceOld == null)
{
result = insuranceService.CreateInsurance(newInsurance);
}
else {
result = insuranceService.UpdateInsurance(insuranceOld,Convert.ToInt32(txtinsurance.Text.Trim()));
}
if (result > -1)
{
Console.WriteLine("Insert Complete");
lblresult.Visible = true;
lblresult.Text = " บันทึกเรียบร้อย ";
}
else {
Console.WriteLine("Insert Not Complete");
lblresult.Visible = true;
lblresult.Text = " ไม่สามารถบันทึกข้อมูลได้";
}
}
public Insurance getInsuranceBypsnCode(string _psnCode)
{
Insurance insurance = null;
try
{
conn = db.openConn();
sb = new StringBuilder();
sb.Remove(0, sb.Length);
sb.Append(" SELECT INID,psnCode FROM tbInsurance");
sb.Append(" WHERE 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)
{
insurance = new Insurance();
insurance.ID = drw["INID"].ToString();
Person person = new Person();
person.psnCode = drw["psnCode"].ToString();
insurance.person = person;
//insurance.amount = Convert.ToInt32(drw["amount"].ToString());
}
}
}
catch (Exception ex) {
throw ex;
}
return insurance;
}
public int UpdateInsurance(Insurance _insurance,int _amount)
{
int result = -1;
try
{
conn = db.openConn();
tr = conn.BeginTransaction();
sb = new StringBuilder();
sb.Remove(0, sb.Length);
sb.Append("INSERT INTO tbInsurDetail(INID,inDate,amount)");
sb.Append(" VALUES (@INID,@inDate,@amount)");
string sqlSave;
sqlSave = sb.ToString();
comm = new SqlCommand();
comm.Connection = conn;
comm.CommandText = sqlSave;
comm.Transaction = tr;
comm.Parameters.Clear();
int year = Convert.ToInt32(DateTime.Now.Year.ToString("0000"));
string _indate = DateTime.Now.Day.ToString("00") + "/" + DateTime.Now.Month.ToString("00") + "/" + (year+543) ;
comm.Parameters.Add("@INID", SqlDbType.NVarChar).Value = _insurance.ID;
comm.Parameters.Add("@inDate", SqlDbType.NVarChar).Value = _indate;
comm.Parameters.Add("@amount", SqlDbType.NVarChar).Value = _amount;
comm.ExecuteNonQuery();
tr.Commit();
result = 1;
}
catch (Exception ex)
{
tr.Rollback();
conn.Close();
return result;
throw ex;
}
finally
{
conn.Close();
}
return result;
}
public int CreateInsurance(Insurance _insurance)
{
int result = -1;
try
{
conn = db.openConn();
tr = conn.BeginTransaction();
sb = new StringBuilder();
sb.Remove(0, sb.Length);
sb.Append("INSERT INTO tbInsurance(INID,psnCode)");
sb.Append(" VALUES (@INID,@psnCode)");
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 = _insurance.person.psnCode;
int year = Convert.ToInt32(DateTime.Now.Year.ToString("0000"));
string _dNow = DateTime.Now.Day.ToString("00") + "" + DateTime.Now.Month.ToString("00") + "" + year.ToString() + DateTime.Now.ToShortTimeString();
comm.Parameters.Add("@INID", SqlDbType.NVarChar).Value = _dNow;
comm.ExecuteNonQuery();
foreach (InsuranceDetail n in _insurance.insuranceDetails) {
sb.Remove(0, sb.Length);
sb.Append("INSERT INTO tbInsurDetail(INID,inDate,amount)");
sb.Append(" VALUES (@INID,@inDate,@amount)");
string sqlsave2;
sqlsave2 = sb.ToString();
comm = new SqlCommand();
comm.Connection = conn;
comm.Transaction = tr;
comm.CommandText = sqlsave2;
comm.Parameters.Clear();
comm.Parameters.Add("@INID", SqlDbType.NVarChar).Value = _dNow;
comm.Parameters.Add("@inDate", SqlDbType.NVarChar).Value = n.indate;
comm.Parameters.Add("@amount", SqlDbType.NVarChar).Value = n.amount;
comm.ExecuteNonQuery();
}
tr.Commit();
result = 1;
}
catch (Exception ex)
{
tr.Rollback();
conn.Close();
return result;
throw ex;
}
finally
{
conn.Close();
}
return result;
}
public List<InsuranceDetail> getInsuranceDetailByinID(string _inID)
{
InsuranceDetail insuranceDetail = null;
List<InsuranceDetail> insuranceDetails = new List<InsuranceDetail>();
try
{
conn = db.openConn();
sb = new StringBuilder();
sb.Remove(0, sb.Length);
sb.Append(" SELECT ID,INID,inDate,amount FROM tbInsurDetail ");
sb.Append(" WHERE INID='"+_inID+"'");
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)
{
insuranceDetail = new InsuranceDetail();
insuranceDetail.ID = Convert.ToInt32(drw["ID"].ToString());
Insurance insurance = new Insurance();
insurance.ID = drw["INID"].ToString();
insuranceDetail.indate =(drw["inDate"].ToString());
insuranceDetail.amount = Convert.ToInt32(drw["amount"].ToString());
insuranceDetails.Add(insuranceDetail);
}
}
dr.Close();
}
catch (Exception ex)
{
dr.Close();
conn.Close();
return null;
throw ex;
}
finally
{
conn.Close();
}
return insuranceDetails;
}