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;
        }
Exemple #2
0
        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);
            }
        }
Exemple #3
0
        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;
        }
Exemple #5
0
        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;
        }
Exemple #8
0
        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;
        }
Exemple #13
0
        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;
        }