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; }