private void frmReamMoneyPrint_Load(object sender, EventArgs e)
{
this.WindowState = FormWindowState.Maximized;
ReamMoney reamMoney = new ReamMoney();
reamMoney = reamMoneyService.getReamMoneyByID(rid);
List<ReamMoneyReport> reamMoneyReports = new List<ReamMoneyReport>();
ReamMoneyReport reamMoneyReport = new ReamMoneyReport();
Person p = new Person();
p = personService.getPersonBypsnCode(reamMoney.person.psnCode);
reamMoneyReport.fullName = p.psnPreFix + " " + p.psnName + " " + p.psnLastName;
reamMoneyReport.amount =Convert.ToString(reamMoney.amount);
reamMoneyReport.reamDate = reamMoney.reamdate;
reamMoneyReports.Add(reamMoneyReport);
ReportDocument rp = new ReportDocument();
string path = Application.StartupPath + @"\report\rptReamMoney.rpt";
rp.Load(path);
rp.SetDataSource(reamMoneyReports);
PV.ReportSource = rp;
}
private void cmdSave_Click(object sender, EventArgs e)
{
if (txtamount.Text.Trim() == "")
{
MessageBox.Show("กรุณาป้อนจำนวนโบนัส ก่อน !!!", "ผลการตรวจสอบ", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
txtamount.Focus();
return;
}
Bonus _bonus = new Bonus();
Person _p = new Person();
_p.psnCode = txtpsnCode.Text.Trim();
_bonus.person = _p;
_bonus.amount = Convert.ToInt32(txtamount.Text.Trim());
_bonus.bdate = DateTime.Now;
int result = bonusService.CreateBonus(_bonus);
if (result > -1)
{
Console.WriteLine("Insert Complete");
lblresult.Visible = true;
lblresult.Text = " บันทึกเรียบร้อย ";
//MessageBox.Show("บันทึกข้อมูล เรียบร้อยแล้ว !!!", "ผลการทำงาน", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
Console.WriteLine("Insert Not Complete");
lblresult.Visible = true;
lblresult.Text = " ไม่สามารถบันทึกข้อมูลได้";
//MessageBox.Show("ไม่สามารถบันทึกข้อมูล เรียบร้อยแล้ว !!!", "ผลการทำงาน", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
private void cmdSave_Click(object sender, EventArgs e)
{
if (txtmoney.Text.Trim() == "")
{
MessageBox.Show("กรุณาป้อนจำนวนที่ต้องจ่าย ก่อน !!!", "ผลการตรวจสอบ", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
txtmoney.Focus();
return;
}
Orther newOrther = new Orther();
newOrther.ID =0;
Person _p = new Person();
_p.psnCode = txtpsnCode.Text.Trim();
newOrther.person =_p;
newOrther.subject =txtsubject.Text.Trim();
newOrther.amount = Convert.ToInt32(txtmoney.Text.Trim());
int result = ortherService.CreateOrther(newOrther);
if (result > -1)
{
Console.WriteLine("Insert Complete");
lblresult.Visible = true;
lblresult.Text = " บันทึกเรียบร้อย ";
//MessageBox.Show("บันทึกข้อมูล เรียบร้อยแล้ว !!!", "ผลการทำงาน", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
Console.WriteLine("Insert Not Complete");
lblresult.Visible = true;
lblresult.Text = " ไม่สามารถบันทึกข้อมูลได้";
//MessageBox.Show("ไม่สามารถบันทึกข้อมูล เรียบร้อยแล้ว !!!", "ผลการทำงาน", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
private void frmLoanPrint_Load(object sender, EventArgs e)
{
this.WindowState = FormWindowState.Maximized;
Loan loan = loanService.getLoanByID(lid);
List<LoanReport> loanReports = new List<LoanReport>();
LoanReport loanReport = new LoanReport();
Person p = new Person();
p = personService.getPersonBypsnCode(loan.person.psnCode);
loanReport.fullName = p.psnPreFix + " " + p.psnName + " " + p.psnLastName;
loanReport.lmoney =Convert.ToString(loan.lmoney);
loanReport.term = Convert.ToString(loan.term);
loanReport.loanDate = loan.loandate;
loanReport.lowmoney = Convert.ToString(loan.lowmoney);
loanReport.reasonloan = loan.reasonLoan;
loanReports.Add(loanReport);
ReportDocument rp = new ReportDocument();
string path = Application.StartupPath + @"\report\rptLoan.rpt";
rp.Load(path);
rp.SetDataSource(loanReports);
PV.ReportSource = rp;
}
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 = " ไม่สามารถบันทึกข้อมูลได้";
}
}
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 = " ไม่สามารถบันทึกข้อมูลได้";
}
}
private void frmpaysalaryprint_Load(object sender, EventArgs e)
{
this.WindowState = FormWindowState.Maximized;
PaySalary paySalary = paySalaryService.getPaySalaryByPayId(payId);
List<PaySalipReport> paySalipReports = new List<PaySalipReport>();
PaySalipReport paySalipReport = new PaySalipReport();
paySalipReport.period = paySalary.period;
paySalipReport.psnCode = paySalary.psnCode;
Person person = new Person();
person = personService.getPersonBypsnCode(paySalary.psnCode);
paySalipReport.fullName = person.psnPreFix + " " + person.psnName + " " + person.psnLastName ;
paySalipReport.depName = person.psndpName;
paySalipReport.posName = person.psnpsName;
/* รายได้ */
paySalipReport.salary = paySalary.salary;
paySalipReport.pot = Convert.ToString(paySalary.pOt);
paySalipReport.pwork = Convert.ToString(paySalary.pWork);
/* รายการหัก*/
paySalipReport.ppso = Convert.ToString(paySalary.ppSo);
paySalipReport.psla = Convert.ToString(paySalary.psLa);
paySalipReport.pslast = Convert.ToString(paySalary.psLast);
paySalipReport.psless = Convert.ToString(paySalary.psLess);
paySalipReport.psream = Convert.ToString(paySalary.psReam);
paySalipReport.psins = Convert.ToString(paySalary.psIns);
paySalipReport.pspenalty = Convert.ToString(paySalary.psPenalty);
paySalipReport.revenceAll = paySalary.revenceAll;
paySalipReport.netincome = paySalary.netIncome;
paySalipReport.total = Convert.ToString(paySalary.total);
paySalipReport.payDate = paySalary.payDate;
paySalipReports.Add(paySalipReport);
ReportDocument rp = new ReportDocument();
string path = Application.StartupPath + @"\report\rptPaySalary.rpt";
rp.Load(path);
rp.SetDataSource(paySalipReports);
PV.ReportSource = rp;
}
private void cmdSave_Click(object sender, EventArgs e)
{
if (txtreson.Text.Trim() == "")
{
MessageBox.Show("กรุณาป้อนเหตุที่ลาออก ก่อน !!!", "ผลการตรวจสอบ", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
txtreson.Focus();
return;
}
Resign newResign = new Resign();
Person _p = new Person();
_p.psnCode = txtpsnCode.Text.Trim();
newResign.person = _p;
int year = Convert.ToInt32(dtpexpire.Value.Year.ToString("0000"));
string _dNow = dtpexpire.Value.Day.ToString("00") + "/" + dtpexpire.Value.Month.ToString("00") + "/" + (year + 543);
newResign.expire = _dNow;
if (rdbture.Checked == true)
{
newResign.format = "1"; //ถูกต้อง
newResign.psoReturn = 0;
}
else {
newResign.format = "0"; //ไม่ถูกต้อง
newResign.psoReturn = Convert.ToInt32(txtpsoReturn.Text.Trim());
}
newResign.reason = txtreson.Text.Trim();
int result = resignService.CreateResign(newResign);
if (result > -1)
{
Console.WriteLine("Insert Complete");
lblresult.Visible = true;
lblresult.Text = " บันทึกเรียบร้อย ";
}
else
{
Console.WriteLine("Insert Not Complete");
lblresult.Visible = true;
lblresult.Text = " ไม่สามารถบันทึกข้อมูลได้";
}
}
private void cmdSave_Click(object sender, EventArgs e)
{
if (txtamount.Text.Trim() == "")
{
MessageBox.Show("กรุณาป้อนจำนวนที่เบิกก่อน !!!", "ผลการตรวจสอบ", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
txtamount.Focus();
return;
}
ReamMoney newReamMoney = new ReamMoney();
int _year = Convert.ToInt32(DateTime.Now.Year.ToString("0000"));
_rid = DateTime.Now.Day.ToString("00") + "" + DateTime.Now.Month.ToString("00") + "" + _year.ToString() + DateTime.Now.ToShortTimeString();
newReamMoney.ID = _rid;
Person p = new Person();
p = personService.getPersonBypsnCode(txtpsnCode.Text.Trim());
newReamMoney.person = p;
newReamMoney.amount = Convert.ToInt32(txtamount.Text.Trim());
int year = Convert.ToInt32(dtpreamdate.Value.Year.ToString("0000"));
string _dNow = dtpreamdate.Value.Day.ToString("00") + "/" + dtpreamdate.Value.Month.ToString("00") + "/" + (year+543);
newReamMoney.reamdate = _dNow;
int result = reamMoneyService.CreateReamMoney(newReamMoney);
if (result > -1)
{
Console.WriteLine("Insert Complete");
lblresult.Visible = true;
lblresult.Text = " บันทึกเรียบร้อย ";
}
else
{
Console.WriteLine("Insert Not Complete");
lblresult.Visible = true;
lblresult.Text = " ไม่สามารถบันทึกข้อมูลได้";
}
}
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;
}
public ReamMoney getReamMoneyByID(string _rid)
{
ReamMoney reamMoney = null;
try
{
conn = db.openConn();
sb = new StringBuilder();
sb.Remove(0, sb.Length);
sb.Append(" SELECT ID,RID,psnCode,amount,reamdate FROM tbReamMoney ");
sb.Append(" WHERE RID='" + _rid + "'");
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)
{
reamMoney = new ReamMoney();
reamMoney.ID =drw["RID"].ToString();
Person p = new Person();
p.psnCode = drw["psnCode"].ToString();
reamMoney.person = p;
reamMoney.amount =Convert.ToInt32(drw["amount"].ToString());
reamMoney.reamdate = drw["reamdate"].ToString();
}
}
dr.Close();
}
catch (Exception ex)
{
dr.Close();
conn.Close();
return null;
throw ex;
}
finally
{
conn.Close();
}
return reamMoney;
}
public int CreatePerson(Person newPerson)
{
int result = -1;
try
{
conn = db.openConn();
tr = conn.BeginTransaction();
sb = new StringBuilder();
sb.Remove(0, sb.Length);
// tab 1
sb.Append("INSERT INTO tbPerson(psnCode,psnPrefix,psnName,psnLastName,psnNickName,psnSex,psnNation,psnBirthday,psnptName,psndpName,psnpsName,psnStatus,psnStartDate,");
// tab 2
sb.Append(" pdrNo,pdrMoo,pdrSoi,pdrRoad,pdrTombon,pdrAmphur,pdrProvince,pdrZip,pdrTel,pdrPhone,pdrEmail,");
// tab 3
sb.Append(" ppNo,ppMoo,ppSoi,ppRoad,ppTombon,ppAmphur,ppProvince,ppZip,");
// tab 4
sb.Append(" pcName,pcNo,pcMoo,pcSoi,pcRoad,pcTombon,pcAmphur,pcProvince,pcZip,pcTel,pcPhone,pcEmail,pcWork,pcRe,");
// tab 5
sb.Append(" perName,perNo,perMoo,persoi,perRoad,perTombon,perAmphur,perProvince,perZip,perTel,perPhone,perEmail,perWork,perRe,");
// tab 6
sb.Append(" pcri,pper,pacconnt,pssalary,pso,pot,");
// tab 7
sb.Append(" psoID,psoName1,psoName2,psoName3,psoExpire,penalty,penaltyNote,amountWage,amountSum)");
// value tab 1
sb.Append(" VALUES (@psnCode,@psnPrefix,@psnName,@psnLastName,@psnNickName,@psnSex,@psnNation,@psnBirthday,@psnptName,@psndpName,@psnpsName,@psnStatus,@psnStartDate,");
// value tab 2
sb.Append(" @pdrNo,@pdrMoo,@pdrSoi,@pdrRoad,@pdrTombon,@pdrAmphur,@pdrProvince,@pdrZip,@pdrTel,@pdrPhone,@pdrEmail,");
// value tab 3
sb.Append(" @ppNo,@ppMoo,@ppSoi,@ppRoad,@ppTombon,@ppAmphur,@ppProvince,@ppZip,");
// value tab 4
sb.Append(" @pcName,@pcNo,@pcMoo,@pcSoi,@pcRoad,@pcTombon,@pcAmphur,@pcProvince,@pcZip,@pcTel,@pcPhone,@pcEmail,@pcWork,@pcRe,");
// value tab 5
sb.Append(" @perName,@perNo,@perMoo,@persoi,@perRoad,@perTombon,@perAmphur,@perProvince,@perZip,@perTel,@perPhone,@perEmail,@perWork,@perRe,");
// value tab 6
sb.Append(" @pcri,@pper,@pacconnt,@pssalary,@pso,@pot,");
// value tab 7
sb.Append(" @psoID,@psoName1,@psoName2,@psoName3,@psoExpire,@penalty,@penaltyNote,@amountWage,@amountSum)");
string sqlsave;
sqlsave = sb.ToString();
comm = new SqlCommand();
comm.Connection = conn;
comm.Transaction = tr;
comm.CommandText = sqlsave;
comm.Parameters.Clear();
// tab 1
comm.Parameters.Add("@psnCode", SqlDbType.NVarChar).Value = newPerson.psnCode;
comm.Parameters.Add("@psnPrefix", SqlDbType.NVarChar).Value = newPerson.psnPreFix;
comm.Parameters.Add("@psnName", SqlDbType.NVarChar).Value = newPerson.psnName;
comm.Parameters.Add("@psnLastName", SqlDbType.NVarChar).Value = newPerson.psnLastName;
comm.Parameters.Add("@psnNickName", SqlDbType.NVarChar).Value = newPerson.psnNickName;
comm.Parameters.Add("@psnSex", SqlDbType.NVarChar).Value = newPerson.psnSex;
comm.Parameters.Add("@psnNation", SqlDbType.NVarChar).Value = newPerson.psnNation;
comm.Parameters.Add("@psnBirthday", SqlDbType.NVarChar).Value = newPerson.psnBirthDay;
comm.Parameters.Add("@psnptName", SqlDbType.NVarChar).Value = newPerson.psnptName;
comm.Parameters.Add("@psndpName", SqlDbType.NVarChar).Value = newPerson.psndpName;
comm.Parameters.Add("@psnpsName", SqlDbType.NVarChar).Value = newPerson.psnpsName;
comm.Parameters.Add("@psnStatus", SqlDbType.NVarChar).Value = newPerson.psnStatus;
comm.Parameters.Add("@psnStartDate", SqlDbType.DateTime).Value = newPerson.psnStartDate;
// tab 2
comm.Parameters.Add("@pdrNo", SqlDbType.NVarChar).Value = newPerson.pdrNo;
comm.Parameters.Add("@pdrMoo", SqlDbType.NVarChar).Value = newPerson.pdrMoo;
comm.Parameters.Add("@pdrSoi", SqlDbType.NVarChar).Value = newPerson.pdrSoi;
comm.Parameters.Add("@pdrRoad", SqlDbType.NVarChar).Value = newPerson.pdrRoad;
comm.Parameters.Add("@pdrTombon", SqlDbType.NVarChar).Value = newPerson.pdrTombon;
comm.Parameters.Add("@pdrAmphur", SqlDbType.NVarChar).Value = newPerson.pdrAmphur;
comm.Parameters.Add("@pdrProvince", SqlDbType.NVarChar).Value = newPerson.pdrProvince;
comm.Parameters.Add("@pdrZip", SqlDbType.NVarChar).Value = newPerson.pdrZip;
comm.Parameters.Add("@pdrTel", SqlDbType.NVarChar).Value = newPerson.pdrTel;
comm.Parameters.Add("@pdrPhone", SqlDbType.NVarChar).Value = newPerson.pdrPhone;
comm.Parameters.Add("@pdrEmail", SqlDbType.NVarChar).Value = newPerson.pdrEmail;
// tab 3
comm.Parameters.Add("@ppNo", SqlDbType.NVarChar).Value = newPerson.ppNo;
comm.Parameters.Add("@ppMoo", SqlDbType.NVarChar).Value = newPerson.ppMoo;
comm.Parameters.Add("@ppSoi", SqlDbType.NVarChar).Value = newPerson.ppSoi;
comm.Parameters.Add("@ppRoad", SqlDbType.NVarChar).Value = newPerson.ppRoad;
comm.Parameters.Add("@ppTombon", SqlDbType.NVarChar).Value = newPerson.ppTombon;
comm.Parameters.Add("@ppAmphur", SqlDbType.NVarChar).Value = newPerson.ppAmphur;
comm.Parameters.Add("@ppProvince", SqlDbType.NVarChar).Value = newPerson.ppProvince;
comm.Parameters.Add("@ppZip", SqlDbType.NVarChar).Value = newPerson.ppZip;
// tab 4
comm.Parameters.Add("@pcName", SqlDbType.NVarChar).Value = newPerson.pcName;
comm.Parameters.Add("@pcNo", SqlDbType.NVarChar).Value = newPerson.pcNo;
comm.Parameters.Add("@pcMoo", SqlDbType.NVarChar).Value = newPerson.pcMoo;
comm.Parameters.Add("@pcSoi", SqlDbType.NVarChar).Value = newPerson.pcSoi;
comm.Parameters.Add("@pcRoad", SqlDbType.NVarChar).Value = newPerson.pcRoad;
comm.Parameters.Add("@pcTombon", SqlDbType.NVarChar).Value = newPerson.pcTombon;
comm.Parameters.Add("@pcAmphur", SqlDbType.NVarChar).Value = newPerson.pcAmphur;
comm.Parameters.Add("@pcProvince", SqlDbType.NVarChar).Value = newPerson.pcProvince;
comm.Parameters.Add("@pcZip", SqlDbType.NVarChar).Value = newPerson.pcZip;
comm.Parameters.Add("@pcTel", SqlDbType.NVarChar).Value = newPerson.pcTel;
comm.Parameters.Add("@pcPhone", SqlDbType.NVarChar).Value = newPerson.pcPhone;
comm.Parameters.Add("@pcEmail", SqlDbType.NVarChar).Value = newPerson.pcEmail;
comm.Parameters.Add("@pcWork", SqlDbType.NVarChar).Value = newPerson.pcWork;
comm.Parameters.Add("@pcRe", SqlDbType.NVarChar).Value = newPerson.pcRe;
// tab 5
comm.Parameters.Add("@perName", SqlDbType.NVarChar).Value = newPerson.perName;
comm.Parameters.Add("@perNo", SqlDbType.NVarChar).Value = newPerson.perNo;
comm.Parameters.Add("@perMoo", SqlDbType.NVarChar).Value = newPerson.perMoo;
comm.Parameters.Add("@persoi", SqlDbType.NVarChar).Value = newPerson.persoi;
comm.Parameters.Add("@perRoad", SqlDbType.NVarChar).Value = newPerson.perRoad;
comm.Parameters.Add("@perTombon", SqlDbType.NVarChar).Value = newPerson.perTombon;
comm.Parameters.Add("@perAmphur", SqlDbType.NVarChar).Value = newPerson.perAmphur;
comm.Parameters.Add("@perProvince", SqlDbType.NVarChar).Value = newPerson.perProvince;
comm.Parameters.Add("@perZip", SqlDbType.NVarChar).Value = newPerson.perZip;
comm.Parameters.Add("@perTel", SqlDbType.NVarChar).Value = newPerson.perTel;
comm.Parameters.Add("@perPhone", SqlDbType.NVarChar).Value = newPerson.perPhone;
comm.Parameters.Add("@perEmail", SqlDbType.NVarChar).Value = newPerson.perEmail;
comm.Parameters.Add("@perWork", SqlDbType.NVarChar).Value = newPerson.perWork;
comm.Parameters.Add("@perRe", SqlDbType.NVarChar).Value = newPerson.perRe;
// tab 6
comm.Parameters.Add("@pcri", SqlDbType.NVarChar).Value = newPerson.pcri;
comm.Parameters.Add("@pper", SqlDbType.NVarChar).Value = newPerson.pper;
comm.Parameters.Add("@pacconnt", SqlDbType.NVarChar).Value = newPerson.pacconnt;
comm.Parameters.Add("@pssalary", SqlDbType.NVarChar).Value = newPerson.pssalary;
comm.Parameters.Add("@pso", SqlDbType.NVarChar).Value = newPerson.pso;
comm.Parameters.Add("@pot", SqlDbType.NVarChar).Value = newPerson.pot;
// tab 7
comm.Parameters.Add("@psoID", SqlDbType.NVarChar).Value = newPerson.psoID;
comm.Parameters.Add("@psoName1", SqlDbType.NVarChar).Value = newPerson.psoName1;
comm.Parameters.Add("@psoName2", SqlDbType.NVarChar).Value = newPerson.psoName2;
comm.Parameters.Add("@psoName3", SqlDbType.NVarChar).Value = newPerson.psoName3;
comm.Parameters.Add("@psoExpire", SqlDbType.NVarChar).Value = newPerson.psoExpire;
comm.Parameters.Add("@penalty", SqlDbType.NVarChar).Value = newPerson.penalty;
comm.Parameters.Add("@penaltyNote", SqlDbType.NVarChar).Value = newPerson.penaltyNote;
comm.Parameters.Add("@amountWage", SqlDbType.NVarChar).Value = newPerson.amountWage;
comm.Parameters.Add("@amountSum", SqlDbType.NVarChar).Value = newPerson.amountSum;
comm.ExecuteNonQuery();
tr.Commit();
result = 1;
}
catch (Exception ex)
{
tr.Rollback();
conn.Close();
return result;
throw ex;
}
finally
{
conn.Close();
}
return result;
}
private void tsOK_Click(object sender, EventArgs e)
{
// tab 1
if (txtpsCode.Text == "") {
MessageBox.Show("กรุณาป้อนข้อมูลให้ครบทุกช่อง !!!", "ผลการตรวจสอบ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
txtpsCode.Focus();
return;
}
// tab 2
if (txtpdrNo.Text == "")
{
MessageBox.Show("กรุณาป้อนข้อมูลให้ครบทุกช่อง !!!", "ผลการตรวจสอบ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
tabControl1.SelectedIndex = 1;
txtpdrNo.Focus();
return;
}
// tab 3
if (txtpdrNo.Text == "")
{
MessageBox.Show("กรุณาป้อนข้อมูลให้ครบทุกช่อง !!!", "ผลการตรวจสอบ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
tabControl1.SelectedIndex = 2;
txtpdrNo.Focus();
return;
}
// tab 4
if (txtpcName.Text == "")
{
MessageBox.Show("กรุณาป้อนข้อมูลให้ครบทุกช่อง !!!", "ผลการตรวจสอบ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
tabControl1.SelectedIndex = 3;
txtpcName.Focus();
return;
}
// tab 5
if (txtperName.Text == "")
{
MessageBox.Show("กรุณาป้อนข้อมูลให้ครบทุกช่อง !!!", "ผลการตรวจสอบ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
tabControl1.SelectedIndex = 4;
txtperName.Focus();
return;
}
// tab 6
if (cbopcri.Text == "")
{
MessageBox.Show("กรุณาป้อนข้อมูลให้ครบทุกช่อง !!!", "ผลการตรวจสอบ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
tabControl1.SelectedIndex = 5;
cbopcri.Focus();
return;
}
// tab 7
if (txtpsoID.Text == "")
{
MessageBox.Show("กรุณาป้อนข้อมูลให้ครบทุกช่อง !!!", "ผลการตรวจสอบ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
tabControl1.SelectedIndex = 6;
txtpsoID.Focus();
return;
}
Person person = new Person();
// ข้อมูลพนักงาน tab 1
person.psnCode = txtpsCode.Text.Trim();
person.psnPreFix = txtpsnPrefix.Text.Trim();
person.psnName = txtpsnName.Text.Trim();
person.psnLastName = txtpsnLastName.Text.Trim();
person.psnNickName = txtpsnNickName.Text.Trim();
if (rdbpso1.Checked ==true){
person.psnSex = "0";
}else if (rdbpso2.Checked ==true){
person.psnSex = "1";
}
person.psnNation = txtpsnNation.Text.Trim();
person.psnBirthDay = dtpBirthDay.Value.ToString();
person.psnptName = cbopsnptName.Text.Trim();
person.psndpName = cbopsndpName.Text.Trim();
person.psnpsName = cbopsnspName.Text.Trim();
person.psnStatus = cbopanStatus.Text.Trim();
person.psnStartDate = Convert.ToDateTime(dtppsnStartDate.Value.ToString());
// ข้อมูลที่อยู่ tab 2
person.pdrNo = txtpdrNo.Text.Trim();
person.pdrMoo = txtpdrMoo.Text.Trim();
person.pdrSoi = txtpdrSoi.Text.Trim();
person.pdrRoad = txtpdrRoad.Text.Trim();
person.pdrTombon = txtpdrTombon.Text.Trim();
person.pdrAmphur = txtpdrAmphur.Text.Trim();
person.pdrProvince = txtpdrProvince.Text.Trim();
person.pdrZip = txtpdrZip.Text.Trim();
person.pdrTel = txtpdrTel.Text.Trim();
person.pdrPhone = txtpdrPhone.Text.Trim();
person.pdrEmail = txtpdrEmail.Text.Trim();
// ข้อมูลที่อยู่ตามบัตรประชาชน tab 3
person.ppNo = txtppNo.Text.Trim();
person.ppMoo = txtppMoo.Text.Trim();
person.ppSoi = txtppSoi.Text.Trim();
person.ppRoad = txtppRoad.Text.Trim();
person.ppTombon = txtppTomBon.Text.Trim();
person.ppAmphur = txtppAumphur.Text.Trim();
person.ppProvince = txtppProvince.Text.Trim();
person.ppZip = txtppZip.Text.Trim();
// ข้อมูลผู้สามารถติดต่อได้ tab 4
person.pcName = txtpcName.Text.Trim();
person.pcNo = txtpcNo.Text.Trim();
person.pcMoo = txtpcMoo.Text.Trim();
person.pcSoi = txtpcSoi.Text.Trim();
person.pcRoad = txtpcRoad.Text.Trim();
person.pcTombon = txtpcTombon.Text.Trim();
person.pcAmphur = txtpcAmphur.Text.Trim();
person.pcProvince = txtpcProvince.Text.Trim();
person.pcZip = txtpcZip.Text.Trim();
person.pcTel = txtpcTel.Text.Trim();
person.pcPhone = txtpcPhone.Text.Trim();
person.pcEmail = txtpcEmail.Text.Trim();
person.pcWork = lbpcWork.Text.Trim();
person.pcRe = txtpcRe.Text.Trim();
//ข้อมูลบุคคลค้ำประกัน tab 5
person.perName = txtperName.Text.Trim();
person.perNo = txtperNo.Text.Trim();
person.perMoo = txtperMoo.Text.Trim();
person.persoi = txtperSoi.Text.Trim();
person.perRoad = txtpreRoad.Text.Trim();
person.perTombon = txtperTombon.Text.Trim();
person.perAmphur = txtperAmphur.Text.Trim();
person.perProvince = txtperProvince.Text.Trim();
person.perZip = txtperZip.Text.Trim();
person.perTel = txtperTel.Text.Trim();
person.perPhone = txtperPhone.Text.Trim();
person.perEmail = txtperEmail.Text.Trim();
person.perWork = lbperWork.Text.Trim();
person.perRe = txtperRe.Text.Trim();
//ข้อมูลเงินเดือน tab 6
person.pcri = cbopcri.Text.Trim();
person.pper = txtpper.Text.Trim();
person.pacconnt = txtpAccount.Text.Trim();
person.pssalary = txtpsalary.Text.Trim();
if (rdbpso1.Checked == true){
person.pso = "0";
}else if (rdbpso2.Checked == true){
person.pso = "1";
}
person.pot = txtpOt.Text.Trim();
//ข้อมูลประกันสังคม tab 7
person.psoID = txtpsoID.Text.Trim();
person.psoName1 = txtpsoName1.Text.Trim();
person.psoName2 = txtpsoName2.Text.Trim();
person.psoName3 = txtpsoName3.Text.Trim();
person.psoExpire = dtppsoExpire.Value.ToString();
person.penaltyNote = "";
try
{
if (ActionFlag == "ADD")
{
int result = personService.CreatePerson(person);
if (result > -1)
{
Console.WriteLine("Insert Complete");
MessageBox.Show("บันทึกข้อมูล เรียบร้อยแล้ว !!!", "ผลการทำงาน", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else {
Console.WriteLine("Insert Not Complete");
MessageBox.Show("ไม่สามารถบันทึกข้อมูล เรียบร้อยแล้ว !!!", "ผลการทำงาน", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
else if (ActionFlag == "EDIT") {
int result = personService.UpdatePerson(person);
if (result > -1)
{
Console.WriteLine("Update Complete");
MessageBox.Show("บันทึกข้อมูล เรียบร้อยแล้ว !!!", "ผลการทำงาน", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else {
Console.WriteLine("Update Not Complete");
MessageBox.Show("ไม่สามารถบันทึกข้อมูล เรียบร้อยแล้ว !!!", "ผลการทำงาน", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}
catch (Exception ex) {
throw ex;
}
ActionFlag = "";
bn.BindingSource = null;
bnRecord.Text = "0";
bnCount.Text = "0";
tsAdd.Enabled = true;
tsOK.Enabled = false;
tsCancel.Enabled = false;
bn.Enabled = true;
}
public Person getPersonBypsnCode(string _psnCode)
{
Person person = null;
try
{
conn = db.openConn();
sb = new StringBuilder();
sb.Remove(0, sb.Length);
// tab 1
sb.Append(" SELECT ID,psnCode,psnPrefix,psnName,psnLastName,psnNickName,psnSex,psnNation,psnBirthday,psnptName,psndpName,psnpsName,psnStatus,psnStartDate,");
// tab 2
sb.Append(" pdrNo,pdrMoo,pdrSoi,pdrRoad,pdrTombon,pdrAmphur,pdrProvince,pdrZip,pdrTel,pdrPhone,pdrEmail,");
// tab 3
sb.Append(" ppNo,ppMoo,ppSoi,ppRoad,ppTombon,ppAmphur,ppProvince,ppZip,");
// tab 4
sb.Append(" pcName,pcNo,pcMoo,pcSoi,pcRoad,pcTombon,pcAmphur,pcProvince,pcZip,pcTel,pcPhone,pcEmail,pcWork,pcRe,");
// tab 5
sb.Append(" perName,perNo,perMoo,persoi,perRoad,perTombon,perAmphur,perProvince,perZip,perTel,perPhone,perEmail,perWork,perRe,");
// tab 6
sb.Append(" pcri,pper,pacconnt,pssalary,pso,pot,");
// tab 7
sb.Append(" psoID,psoName1,psoName2,psoName3,psoExpire,penalty,penaltyNote,amountWage,amountSum");
sb.Append(" FROM tbPerson ");
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)
{
person = new Person();
// tab 1
person.ID = Convert.ToInt32(drw["ID"].ToString());
person.psnCode = drw["psnCode"].ToString();
person.psnPreFix = drw["psnPrefix"].ToString();
person.psnName = drw["psnName"].ToString();
person.psnLastName = drw["psnLastName"].ToString();
person.psnNickName = drw["psnNickName"].ToString();
person.psnSex = drw["psnSex"].ToString();
person.psnNation = drw["psnNation"].ToString();
person.psnBirthDay = drw["psnBirthday"].ToString();
person.psnptName = drw["psnptName"].ToString();
person.psndpName = drw["psndpName"].ToString();
person.psnpsName = drw["psnpsName"].ToString();
person.psnStatus = drw["psnStatus"].ToString();
person.psnStartDate = Convert.ToDateTime(drw["psnStartDate"].ToString());
// tab 2
person.pdrNo = drw["pdrNo"].ToString();
person.pdrMoo = drw["pdrMoo"].ToString();
person.pdrSoi = drw["pdrSoi"].ToString();
person.pdrRoad = drw["pdrRoad"].ToString();
person.pdrTombon = drw["pdrTombon"].ToString();
person.pdrAmphur = drw["pdrAmphur"].ToString();
person.pdrProvince = drw["pdrProvince"].ToString();
person.pdrZip = drw["pdrZip"].ToString();
person.pdrTel = drw["pdrTel"].ToString();
person.pdrPhone = drw["pdrPhone"].ToString();
person.pdrEmail = drw["pdrEmail"].ToString();
// tab 3
person.ppNo = drw["ppNo"].ToString();
person.ppMoo = drw["ppMoo"].ToString();
person.ppSoi = drw["ppSoi"].ToString();
person.ppRoad = drw["ppRoad"].ToString();
person.ppTombon = drw["ppTombon"].ToString();
person.ppAmphur = drw["ppAmphur"].ToString();
person.ppProvince = drw["ppProvince"].ToString();
person.ppZip = drw["ppZip"].ToString();
// tab 4
person.pcName = drw["pcName"].ToString();
person.pcNo = drw["pcNo"].ToString();
person.pcMoo = drw["pcMoo"].ToString();
person.pcSoi = drw["pcSoi"].ToString();
person.pcRoad = drw["pcRoad"].ToString();
person.pcTombon = drw["pcTombon"].ToString();
person.pcAmphur = drw["pcAmphur"].ToString();
person.pcProvince = drw["pcProvince"].ToString();
person.pcZip = drw["pcZip"].ToString();
person.pcTel = drw["pcTel"].ToString();
person.pcPhone = drw["pcPhone"].ToString();
person.pcEmail = drw["pcEmail"].ToString();
person.pcWork = drw["pcWork"].ToString();
person.pcRe = drw["pcRe"].ToString();
// tab 5
person.perName = drw["perName"].ToString();
person.perNo = drw["perNo"].ToString();
person.perMoo = drw["perMoo"].ToString();
person.persoi = drw["persoi"].ToString();
person.perRoad = drw["perRoad"].ToString();
person.perTombon = drw["perTombon"].ToString();
person.perAmphur = drw["perAmphur"].ToString();
person.perProvince = drw["perProvince"].ToString();
person.perZip = drw["perZip"].ToString();
person.perTel = drw["perTel"].ToString();
person.perPhone = drw["perPhone"].ToString();
person.perEmail = drw["perEmail"].ToString();
person.perWork = drw["perWork"].ToString();
person.perRe = drw["perRe"].ToString();
// tab 6
person.pcri = drw["pcri"].ToString();
person.pper = drw["pper"].ToString();
person.pacconnt = drw["pacconnt"].ToString();
person.pssalary = drw["pssalary"].ToString();
person.pso = drw["pso"].ToString();
person.pot = drw["pot"].ToString();
// tab 7
person.psoID = drw["psoID"].ToString();
person.psoName1 = drw["psoName1"].ToString();
person.psoName2 = drw["psoName2"].ToString();
person.psoName3 = drw["psoName3"].ToString();
person.psoExpire = drw["psoExpire"].ToString();
//
person.penalty = Convert.ToInt32(drw["penalty"].ToString());
person.penaltyNote = drw["penaltyNote"].ToString();
person.amountWage = Convert.ToInt32(drw["amountWage"].ToString());
person.amountSum = Convert.ToInt32(drw["amountSum"].ToString());
}
}
dr.Close();
}
catch (Exception ex)
{
dr.Close();
conn.Close();
return null;
throw ex;
}
finally
{
conn.Close();
}
return person;
}
public int UpdatePerson(Person _person)
{
int result = -1;
try
{
conn = db.openConn();
tr = conn.BeginTransaction();
sb = new StringBuilder();
sb.Remove(0, sb.Length);
sb.Append(" UPDATE tbPerson ");
// tab 1
sb.Append(" SET psnPrefix='" + _person.psnPreFix + "',");
sb.Append(" psnName='"+_person.psnName+"',");
sb.Append(" psnLastName ='"+_person.psnLastName+"',");
sb.Append(" psnNickName ='"+_person.psnNickName+"',");
sb.Append(" psnSex ='"+_person.psnSex+"',");
sb.Append(" psnNation ='"+_person.psnNation+"',");
sb.Append(" psnBirthday ='"+_person.psnBirthDay+"',");
sb.Append(" psnptName ='"+_person.psnptName+"',");
sb.Append(" psndpName ='"+_person.psndpName+"',");
sb.Append(" psnpsName ='"+_person.psnpsName+"',");
sb.Append(" psnStatus ='"+_person.psnStatus+"',");
sb.Append(" psnStartDate ='" + _person.psnStartDate + "',");
// tab 2
sb.Append(" pdrNo ='" + _person.pdrNo + "',");
sb.Append(" pdrMoo ='" + _person.pdrMoo + "',");
sb.Append(" pdrSoi ='" + _person.pdrSoi + "',");
sb.Append(" pdrRoad ='" + _person.pdrRoad + "',");
sb.Append(" pdrTombon ='" + _person.pdrTombon + "',");
sb.Append(" pdrAmphur ='" + _person.pdrAmphur + "',");
sb.Append(" pdrProvince ='" + _person.pdrProvince + "',");
sb.Append(" pdrZip ='" + _person.pdrZip + "',");
sb.Append(" pdrTel ='" + _person.pdrTel + "',");
sb.Append(" pdrPhone ='" + _person.pdrPhone + "',");
sb.Append(" pdrEmail ='" + _person.pdrEmail + "',");
// tab 3
sb.Append(" ppNo ='" + _person.ppNo + "',");
sb.Append(" ppMoo ='" + _person.ppMoo + "',");
sb.Append(" ppSoi ='" + _person.ppSoi + "',");
sb.Append(" ppRoad ='" + _person.ppRoad+ "',");
sb.Append(" ppTombon ='" + _person.ppTombon + "',");
sb.Append(" ppAmphur ='" + _person.ppAmphur + "',");
sb.Append(" ppProvince ='" + _person.ppProvince + "',");
sb.Append(" ppZip ='" + _person.ppZip + "',");
// tab 4
sb.Append(" pcName ='" + _person.pcName + "',");
sb.Append(" pcNo ='" + _person.pcNo + "',");
sb.Append(" pcMoo ='" + _person.pcMoo + "',");
sb.Append(" pcSoi ='" + _person.pcSoi + "',");
sb.Append(" pcRoad ='" + _person.pcRoad + "',");
sb.Append(" pcTombon ='" + _person.pcTombon + "',");
sb.Append(" pcAmphur ='" + _person.pcAmphur + "',");
sb.Append(" pcProvince ='" + _person.pcProvince + "',");
sb.Append(" pcZip ='" + _person.pcZip + "',");
sb.Append(" pcTel ='" + _person.pcTel + "',");
sb.Append(" pcPhone ='" + _person.pcPhone + "',");
sb.Append(" pcEmail ='" + _person.pcEmail + "',");
sb.Append(" pcWork ='" + _person.pcWork + "',");
sb.Append(" pcRe ='" + _person.pcRe + "',");
// tab 5
sb.Append(" perName ='" + _person.perName + "',");
sb.Append(" perNo ='" + _person.perNo + "',");
sb.Append(" perMoo ='" + _person.perMoo + "',");
sb.Append(" persoi ='" + _person.persoi + "',");
sb.Append(" perRoad ='" + _person.perRoad + "',");
sb.Append(" perTombon ='" + _person.perTombon + "',");
sb.Append(" perAmphur ='" + _person.perAmphur + "',");
sb.Append(" perProvince ='" + _person.perProvince + "',");
sb.Append(" perZip ='" + _person.perZip + "',");
sb.Append(" perTel ='" + _person.perTel + "',");
sb.Append(" perPhone ='" + _person.perPhone + "',");
sb.Append(" perEmail ='" + _person.perEmail + "',");
sb.Append(" perWork ='" + _person.perWork + "',");
sb.Append(" perRe ='" + _person.perRe + "',");
// tab 6
sb.Append(" pcri ='" + _person.pcri + "',");
sb.Append(" pper ='" + _person.pper + "',");
sb.Append(" pacconnt ='" + _person.pacconnt + "',");
sb.Append(" pssalary ='" + _person.pssalary + "',");
sb.Append(" pso ='" + _person.pso + "',");
sb.Append(" pot ='" + _person.pot + "',");
// tab 7
sb.Append(" psoID ='" + _person.psoID + "',");
sb.Append(" psoName1 ='" + _person.psoName1 + "',");
sb.Append(" psoName2 ='" + _person.psoName2 + "',");
sb.Append(" psoName3 ='" + _person.psoName3 + "',");
sb.Append(" psoExpire ='" + _person.psoExpire + "',");
sb.Append(" penalty ='" + _person.penalty + "',");
sb.Append(" penaltyNote ='" + _person.penaltyNote + "',");
sb.Append(" amountWage ='" + _person.amountWage + "',");
sb.Append(" amountSum ='" + _person.amountSum + "'");
sb.Append(" WHERE (psnCode='" + _person.psnCode + "')");
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<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;
}
public List<Working> getWorkingAll(string _startDate,string _endDate,string _psnCode)
{
Working working = null;
List<Working> workings = new List<Working>();
try
{
conn = db.openConn();
sb = new StringBuilder();
sb.Remove(0, sb.Length);
sb.Append(" SELECT ID,psnCode,dateWork,hurLast,hurLa,hurLacking,hurOt,Orther FROM tbWorking ");
sb.Append(" WHERE dateWork 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)
{
working = new Working();
working.ID = Convert.ToInt32(drw["ID"].ToString());
Person p = new Person();
p = personService.getPersonBypsnCode(drw["psnCode"].ToString());
working.psnCode = p.psnCode;
working.psnName = p.psnName;
working.dateWork = Convert.ToDateTime(drw["dateWork"].ToString());
working.hurLast = Convert.ToInt32(drw["hurLast"].ToString());
working.hurLa = Convert.ToInt32(drw["hurLa"].ToString());
working.hurLacking = Convert.ToInt32(drw["hurLacking"].ToString());
working.hurOt = Convert.ToInt32(drw["hurOt"].ToString());
working.orther = drw["Orther"].ToString();
workings.Add(working);
}
}
dr.Close();
}
catch (Exception ex)
{
dr.Close();
conn.Close();
return null;
throw ex;
}
finally
{
conn.Close();
}
return workings;
}
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;
}