private void cmdSave_Click(object sender, EventArgs e)
{
if (txtlmoney.Text.Trim() == "")
{
MessageBox.Show("กรุณาป้อนจำนวนเงินกู้ ก่อน !!!", "ผลการตรวจสอบ", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
txtlmoney.Focus();
return;
}
if (txtlowmoney.Text.Trim() == "")
{
MessageBox.Show("กรุณาเลือกระยะผ่อนชำระ ก่อน !!!", "ผลการตรวจสอบ", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
txtlowmoney.Focus();
return;
}
Loan newLoan = new Loan();
int _year = Convert.ToInt32(DateTime.Now.Year.ToString("0000"));
_lid = DateTime.Now.Day.ToString("00") + "" + DateTime.Now.Month.ToString("00") + "" + _year.ToString() + DateTime.Now.ToShortTimeString();
newLoan.ID = "L" + _lid;
Person _p = new Person();
_p.psnCode = txtpsnCode.Text.Trim();
newLoan.person = _p;
newLoan.lmoney = Convert.ToInt32(txtlmoney.Text.Trim());
if (rdbterm3.Checked == true) {
newLoan.term = 3;
}
else if (rdbterm6.Checked == true) {
newLoan.term = 6;
}
else if (rdbterm12.Checked == true) {
newLoan.term = 12;
}
newLoan.lowmoney = Convert.ToInt32(txtlowmoney.Text.Trim());
int year = Convert.ToInt32(dtploandate.Value.Year.ToString("0000"));
string _dNow = dtploandate.Value.Day.ToString("00") + "/" + dtploandate.Value.Month.ToString("00") + "/" + (year + 543);
newLoan.loandate = _dNow;
newLoan.reasonLoan = txtresonloan.Text.Trim();
int result = loanService.CreateLoan(newLoan);
if (result > -1)
{
Console.WriteLine("Insert Complete");
lblresult.Visible = true;
lblresult.Text = " บันทึกเรียบร้อย ";
}
else
{
Console.WriteLine("Insert Not Complete");
lblresult.Visible = true;
lblresult.Text = " ไม่สามารถบันทึกข้อมูลได้";
}
}
public int CreateLoan(Loan newLoan)
{
int result = -1;
try
{
conn = db.openConn();
tr = conn.BeginTransaction();
sb = new StringBuilder();
sb.Remove(0, sb.Length);
sb.Append("INSERT INTO tbRoan(psnCode,LID,lmoney,term,lowmoney,loandate,reasonloan,complete)");
sb.Append(" VALUES (@psnCode,@LID,@lmoney,@term,@lowmoney,@loandate,@reasonloa,@complete)");
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 = newLoan.person.psnCode;
comm.Parameters.Add("@LID", SqlDbType.NVarChar).Value = newLoan.ID;
comm.Parameters.Add("@lmoney", SqlDbType.NVarChar).Value = newLoan.lmoney;
comm.Parameters.Add("@term", SqlDbType.NVarChar).Value = newLoan.term;
comm.Parameters.Add("@lowmoney", SqlDbType.NVarChar).Value = newLoan.lowmoney;
comm.Parameters.Add("@loandate", SqlDbType.NVarChar).Value = newLoan.loandate;
comm.Parameters.Add("@reasonloa", SqlDbType.NVarChar).Value = newLoan.reasonLoan;
comm.Parameters.Add("@complete", SqlDbType.NVarChar).Value ="0";
comm.ExecuteNonQuery();
tr.Commit();
result = 1;
}
catch (Exception ex)
{
tr.Rollback();
conn.Close();
return result;
throw ex;
}
finally
{
conn.Close();
}
return result;
}
public List<Loan> getLoanBypsnCode(string _psnCode)
{
List<Loan> loans = new List<Loan>();
Loan loan = null;
try
{
conn = db.openConn();
sb = new StringBuilder();
sb.Remove(0, sb.Length);
sb.Append(" SELECT ID,psnCode,LID,lmoney,term,lowmoney,loandate,reasonloan,complete FROM tbRoan ");
sb.Append(" WHERE psnCode='" + _psnCode + "'");
sb.Append(" AND complete='0'");
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)
{
loan = new Loan();
loan.ID = drw["LID"].ToString();
Person p = new Person();
p.psnCode = drw["psnCode"].ToString();
loan.person = p;
loan.lmoney = Convert.ToInt32(drw["lmoney"].ToString());
loan.term = Convert.ToInt32(drw["term"].ToString());
loan.lowmoney = Convert.ToInt32(drw["lowmoney"].ToString());
loan.loandate = drw["loandate"].ToString();
loan.reasonLoan = drw["reasonloan"].ToString();
loan.complete = drw["complete"].ToString();
loans.Add(loan);
}
}
dr.Close();
}
catch (Exception ex)
{
dr.Close();
conn.Close();
return null;
throw ex;
}
finally
{
conn.Close();
}
return loans;
}