using System;
using CoreSavingLibrary;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Xml.Linq;
using CoreSavingLibrary.WcfNCommon;
using CoreSavingLibrary.WcfNShrlon;
using System.Globalization;
using Sybase.DataWindow;
using DataLibrary;
using System.Drawing;
namespace Saving.Applications.shrlon
{
    public partial class w_sheet_sl_loan_edit : PageWebSheet, WebSheet
    {
        private DwThDate tDwMain;
        private n_shrlonClient shrlonService;
        private n_commonClient commonService;
        //protected string jsPostmember;
        CultureInfo th = System.Globalization.CultureInfo.GetCultureInfo("th-TH");
        //*******ประกาศตัวเกี่ยวกับ  Javascript********//
        protected String jsGetMemberInfo;
        protected String jsClickLoancontract;
        protected String DeleteRowDetail;
        protected String InsertRowDetail;
        Sta ta;
        String pbl = "sl_member_detail.pbl";
        //String pbl_1 = "sl_member_detail.pbl";

        public void InitJsPostBack()
        {
            

            jsGetMemberInfo = WebUtil.JsPostBack(this, "jsGetMemberInfo");
            jsClickLoancontract = WebUtil.JsPostBack(this, "jsClickLoancontract");
           // jsPostmember = WebUtil.JsPostBack(this, "jsPostmember");
            DeleteRowDetail = WebUtil.JsPostBack(this, "DeleteRowDetail");
            InsertRowDetail = WebUtil.JsPostBack(this, "InsertRowDetail");
           
        }

        public void WebSheetLoadBegin()
        {
            try
            {
                shrlonService = wcf.NShrlon;
                commonService = wcf.NCommon;
            }
            catch
            {
                LtServerMessage.Text = WebUtil.ErrorMessage("ติดต่อ Web Service ไม่ได้");
                return;
            }

            this.ConnectSQLCA();
            ta = new Sta(sqlca.ConnectionString);
            sqlca = new DwTrans();
            sqlca.Connect();

             if (IsPostBack)
            {
                this.RestoreContextDw(dw_data_1);
                this.RestoreContextDw(dw_data_2);
                this.RestoreContextDw(dw_data_4);
            }


            if (dw_main.RowCount < 1)
            {
                dw_main.InsertRow(1);
                dw_data_1.InsertRow(1);
                dw_data_4.InsertRow(1);
                dw_list.InsertRow(1);
                dw_data_2.InsertRow(1);
                dw_data_3.InsertRow(1);
            }

        }
        public String selectMemNo(String member_no)
        {
            String member = "";
            String sqlStr2 = @"select member_no
                from mbmembmaster 
                where mbmembmaster.coop_id = {0}
	            and mbmembmaster.member_no like '%" + member_no + "' and ROWNUM <= 1  order by member_no desc";
            sqlStr2 = WebUtil.SQLFormat(sqlStr2, state.SsCoopControl);

            Sdt dt121 = WebUtil.QuerySdt(sqlStr2);

            if (dt121.Next())
            {
                member = dt121.GetString("member_no");
            }
            return member;
        }


       
        /// <summary>
        /// Check  PostBack Javascript
        /// </summary>
        /// <param name="eventArg"></param>
        public void CheckJsPostBack(string eventArg)
        {
            if (eventArg == "jsGetMemberInfo")
            {
                // clear_periodpayment();
                JsPostmember();

            }
            else if (eventArg == "DeleteRowDetail")
            {
                int row = Convert.ToInt32( Hiddenrow.Value);

                WebUtil.QuerySdt(" delete from lncontstatement where loancontract_no='" + dw_data_1.GetItemString(1, "loancontract_no") + "' and seq_no = "+dw_data_2.GetItemDecimal(row,"seq_no")+" ");

                DwUtil.RetrieveDataWindow(dw_data_2, "LOANCONTRACT_NO", null, dw_data_1.GetItemString(1, "loancontract_no"));

            }
            else if (eventArg == "InsertRowDetail")
            {
                dw_data_4.InsertRow(0);
                int rows = dw_data_4.RowCount;
                try
                {
                    String sqlStr2 = @"insert into lncontperiodpaydet(coop_id,loancontract_no,period,period_payment,lastcalfine_date,principal_arrear,expireperiod_date,prinpaid_amt,fine_arrear ) 
                                        values ({0},{1},{2},{3},null,{5},{6},{7}, 0)                                               ";
                    sqlStr2 = WebUtil.SQLFormat(sqlStr2, state.SsCoopId, dw_data_1.GetItemString(1, "loancontract_no"), rows, 0, state.SsWorkDate, 0, state.SsWorkDate, 0);
                    WebUtil.QuerySdt(sqlStr2);
                    DwUtil.RetrieveDataWindow(dw_data_4, "LOANCONTRACT_NO", null, dw_data_1.GetItemString(1, "loancontract_no"), state.SsCoopControl);
                }
                catch { }

            }
                
           /* else if (eventArg == "jsPostmember")
            {
                //    dw_printcont.InsertRow(0);
                //  RetreiveDDDW();
                // clear_periodpayment();
                JsPostmember(member_no);
                //   of_calperiodpayment();
                //  of_initclearshare();
                //    of_Setperiodpaydet();
                //    CalSumTotal();
                dw_main.SetItemString(2, "member_no", "");
                String h = dw_main.GetItemString(1, "member_no");


            }*/
            else if (eventArg == "jsClickLoancontract")
            {
               
                String row = HDcolumnnumber.Value;
                int rows = Convert.ToInt32(row);

                String member_no = "";
                member_no = HdMemberNo.Value;
                member_no = selectMemNo(member_no);
                String loancontract = dw_list.GetItemString(rows, "loancontract_no");
                DwUtil.RetrieveDDDW(dw_data_1, "loanobjective_code", pbl, null);

                DwUtil.RetrieveDataWindow(dw_list, "LOANCONTRACT_NO", null, loancontract);
                DwUtil.RetrieveDataWindow(dw_data_1, "LOANCONTRACT_NO", null, loancontract);
                DwUtil.RetrieveDataWindow(dw_data_2, "LOANCONTRACT_NO", null, loancontract);
                DwUtil.RetrieveDataWindow(dw_data_3, "LOANCONTRACT_NO", null, loancontract);
                DwUtil.RetrieveDataWindow(dw_data_4, "LOANCONTRACT_NO", null, loancontract , state.SsCoopControl);
                DwUtil.RetrieveDataWindow(dw_list, "member_no", null, member_no);
                setDate(loancontract);
                calinitnew(loancontract);
                set_checkinterest(loancontract);
                dw_list.SelectRow(0, false);
                dw_list.SelectRow(rows, true);
                dw_list.SetRow(rows);
                Page.ClientScript.RegisterStartupScript(this.GetType(), "a", "a(" + aa.Value + ")", true);
            }
        }
        public void set_checkinterest(string loancontract_no) {

            try
            {
                string sql = "select * from lncontmaster where loancontract_no ={0} and coop_id={1}";
                sql = WebUtil.SQLFormat(sql, loancontract_no, state.SsCoopId);
                Sdt dt121 = WebUtil.QuerySdt(sql);
                decimal contract_interest = 0;
                if (dt121.Next())
                {
                    contract_interest = dt121.GetDecimal("contract_interest");
                }

                if (contract_interest == -1)
                {
                    dw_data_1.SetItemDecimal(1, "check_interest", -1);
                }
                else
                {
                    dw_data_1.SetItemDecimal(1, "check_interest", 0);
                }
            }
            catch { }

        }

        public void setDate(string loancontract_no) {

            String startcont_tdate = "";
            DateTime loanapprove_date = DateTime.Today;
            DateTime expirecont_date = DateTime.Today;
            DateTime lastaccess_date = DateTime.Today;
            DateTime lastcalint_date = DateTime.Today;
            DateTime lastpayment_date = DateTime.Today;
            String sqlStr2 = @"select * from lncontmaster where loancontract_no={0} and coop_id={1}";
            sqlStr2 = WebUtil.SQLFormat(sqlStr2, loancontract_no,state.SsCoopId);

            Sdt dt121 = WebUtil.QuerySdt(sqlStr2);

            if (dt121.Next())
            {
                startcont_tdate = dt121.GetString("startcont_date");
                loanapprove_date = dt121.GetDate("loanapprove_date");
                expirecont_date = dt121.GetDate("expirecont_date");
                lastaccess_date = dt121.GetDate("lastaccess_date");
                lastcalint_date = dt121.GetDate("lastcalint_date");
                lastpayment_date = dt121.GetDate("lastpayment_date");
            }

            DateTime date = Convert.ToDateTime(startcont_tdate).AddYears(543);
            loanapprove_date = Convert.ToDateTime(loanapprove_date).AddYears(543);
            expirecont_date = Convert.ToDateTime(expirecont_date).AddYears(543);
            lastaccess_date = Convert.ToDateTime(lastaccess_date).AddYears(543);
            lastpayment_date = Convert.ToDateTime(lastpayment_date).AddYears(543);
            lastcalint_date = Convert.ToDateTime(lastcalint_date).AddYears(543);
            dw_data_1.SetItemString(1, "startcont_tdate", date.ToString("dd/MM/yyyy", CultureInfo.InvariantCulture));
            dw_data_1.SetItemString(1, "loanapprove_tdate", loanapprove_date.ToString("dd/MM/yyyy", CultureInfo.InvariantCulture));
            dw_data_1.SetItemString(1, "expirecont_tdate", expirecont_date.ToString("dd/MM/yyyy", CultureInfo.InvariantCulture));
            dw_data_1.SetItemString(1, "lastaccess_tdate", lastaccess_date.ToString("dd/MM/yyyy", CultureInfo.InvariantCulture));
            dw_data_1.SetItemString(1, "lastcalint_tdate", lastcalint_date.ToString("dd/MM/yyyy", CultureInfo.InvariantCulture));
            dw_data_1.SetItemString(1, "lastpayment_tdate", lastpayment_date.ToString("dd/MM/yyyy", CultureInfo.InvariantCulture));
        }

        private void select_contract_interest(string loancontract_no)
        {
            try
            {
                //decimal contract_interest = 0;
                //string sql = "select contract_interest from lncontmaster where loancontract_no={0} and coop_id={1}";
                //sql = WebUtil.SQLFormat(sql, loancontract_no, state.SsCoopId);
                //Sdt dt = WebUtil.QuerySdt(sql);

                //if (dt.Next())
                //{
                //    contract_interest = dt.GetDecimal("contract_interest");
                //}

                decimal interest = -1;
                try
                {
                    interest =  Convert.ToDecimal(Hdinterest.Value.ToString());
                }
                catch {
                    interest = -1;
                }

                string update_contract_interest = @"update lncontmaster set contract_interest={0} where loancontract_no={1} and coop_id={2}";
                update_contract_interest = WebUtil.SQLFormat(update_contract_interest, interest, loancontract_no, state.SsCoopId);
                WebUtil.QuerySdt(update_contract_interest);
            }
            catch { }
        }

        public void JsPostmember()
        {
            try
            {
                DataTable dt = getCountMember_no();
                if (dt.Rows.Count <= 1)
                {
                    String member_no = "";
                    member_no = HdMemberNo.Value;
                    //  member_no = Dw_main.GetItemString(1, "member_no");
                    member_no = selectMemNo(member_no);
                    DwUtil.RetrieveDataWindow(dw_main, "member_no", null, member_no);
                    // DwUtil.RetrieveDDDW(dw_main, "member_no", member_no);
                    DwUtil.RetrieveDataWindow(dw_list, "member_no", null, member_no);
                }
                else
                {
                    Page.ClientScript.RegisterStartupScript(this.GetType(), "CheckCountMemberNo", "CheckCountMemberNo(" + HdMemberNo.Value + ")", true);
                }
               // 
               
            }
            catch { 
            
            }
           
        }

        private DataTable getCountMember_no()
        {
            string memberno = HdMemberNo.Value;
            string sql = "select  member_no from mbmembmaster where member_no Like '%" + memberno + "'";
            DataTable dt = WebUtil.Query(sql);
            return dt;
        }

        public void calinitnew(String loancontract)// 
        {
           
            int k = 1;


            DateTime date_now = state.SsWorkDate;//วันที่ทำการ
           // loancontract =   dw_list.GetItemString(k, "loancontract_no");

       
       
            Decimal intrate = 0;//อัตราดอกเบี้ย
         
            string sql3 = @" select contract_interest from lncontmaster where loancontract_no={0}";
            sql3 = WebUtil.SQLFormat(sql3, loancontract);
            Sdt dt3 = WebUtil.QuerySdt(sql3);
            if (dt3.Next())
            {
                intrate = dt3.GetDecimal("contract_interest");

            }
            if (intrate == -1)// ถ้ามากกว่า 0 ให้ไปดึงดอกเบี้ยจาก lncontmaster
            {
                string sql2 = @" 
                                select lncfloanintratedet.interest_rate from lncfloanintratedet left join  lnloantype on lnloantype.inttabfix_code = lncfloanintratedet.loanintrate_code 
                left join lncontmaster on lncontmaster.loantype_code=lnloantype.loantype_code where lncontmaster.loancontract_no= {0}";
                sql2 = WebUtil.SQLFormat(sql2, loancontract);
                Sdt dt2 = WebUtil.QuerySdt(sql2);
                if (dt2.Next())
                {
                    intrate = dt2.GetDecimal("interest_rate");
                    intrate = intrate / 100;
                }

            }

            // get yaer จาก ค่าคงที่
            decimal year = 0;
            string sql21 = @" 
                 select dayinyear from lnloanconstant";
            sql21 = WebUtil.SQLFormat(sql21, loancontract);
            Sdt dt21 = WebUtil.QuerySdt(sql21);
            if (dt21.Next())
            {
                year = dt21.GetDecimal("dayinyear");

            }

            string sql = @" 
                 SELECT  principal_balance,lastcalint_date FROM lncontmaster  WHERE loancontract_no = {0}";
            sql = WebUtil.SQLFormat(sql, loancontract);
            Sdt dt = WebUtil.QuerySdt(sql);
            if (dt.Next())
            {
                DateTime calint = dt.GetDate("lastcalint_date");
                Decimal principal_balance = dt.GetDecimal("principal_balance"); // ยอดคงเหลือ
                TimeSpan diff1 = date_now.Subtract(calint);
                Double TotalDay2 = diff1.TotalDays;
                int CalDay = Convert.ToInt32(TotalDay2); // คำนวณวัน
                if (CalDay < 0) { CalDay = 0; }
                //คำนวณดอกเบี้ย กล้า
                Decimal cal_int = (principal_balance * intrate * CalDay) / year; //   ด/บใหม่
                //int calints = Convert.ToInt32(cal_int);
                //// Decimal calint = cal_int;
                //if (calints<0)
                //{
                //    calints = 0;
                //}


                cal_int = Math.Round(cal_int, 3);

                decimal numberCheck = cal_int - Math.Floor(cal_int);
                numberCheck = Math.Round(numberCheck, 3);
                double numbercheck2 = Convert.ToDouble(numberCheck);
                // if(numbercheck2 > 0.499){
                cal_int = Convert.ToInt32(cal_int);
                // }

                dw_data_1.SetItemDecimal(1, "interest_return", cal_int);
            }
        }
            

        public void SaveWebSheet()
        {
           // throw new NotImplementedException();
            Update_statement();
            Update_lncontperiodpaydet();
            update_lncontmaster();

            string loancontract = dw_data_1.GetItemString(1, "loancontract_no");
            DwUtil.RetrieveDataWindow(dw_list, "LOANCONTRACT_NO", null, loancontract);
            DwUtil.RetrieveDataWindow(dw_data_1, "LOANCONTRACT_NO", null, loancontract);
            DwUtil.RetrieveDataWindow(dw_data_2, "LOANCONTRACT_NO", null, loancontract);
            DwUtil.RetrieveDataWindow(dw_data_3, "LOANCONTRACT_NO", null, loancontract);
            DwUtil.RetrieveDataWindow(dw_data_4, "LOANCONTRACT_NO", null, loancontract, state.SsCoopControl);
            DwUtil.RetrieveDataWindow(dw_list, "member_no", null, dw_main.GetItemString(1, "member_no"));

            select_contract_interest(loancontract); // update ดอกเบี้ยแต่ละสัญญา
            setDate(loancontract);
            calinitnew(loancontract);
            update_contract_interset(loancontract);

            try
            {
                // โค้ด insert เก็บประวัติการแก้ไข
                createtable();
                insert_history(loancontract);
            }
            catch { }
        }
        private void createtable()
        {
            try
            {
                string select_table = @"SELECT * FROM LOG_UPDATE WHERE 1 = 0";
                select_table = WebUtil.SQLFormat(select_table);
                WebUtil.QuerySdt(select_table);
            }
            catch {

                string select_table = @"CREATE TABLE LOG_UPDATE (COOP_ID VARCHAR2(6) NOT NULL, UPDATE_DATE DATE NOT NULL
                                    , USER_NAME VARCHAR2(100) NOT NULL, IPADDRESS VARCHAR2(20) NOT NULL, DETAILS VARCHAR2(255) NOT NULL
                                    , LOANCONTRACT_NO VARCHAR2(10)) ";
                select_table = WebUtil.SQLFormat(select_table);
                WebUtil.QuerySdt(select_table);
            
            }
        }
        private void insert_history(string loancontract_no)
        {
            
            string insert_history = @"insert into log_update(coop_id,update_date,user_name,ipaddress,details,loancontract_no)values({0},{1},{2},{3},{4},{5})";
            insert_history = WebUtil.SQLFormat(insert_history, state.SsCoopId,DateTime.Now, state.SsUsername, state.SsIsLocalIp, details_update.Value, loancontract_no);
            WebUtil.QuerySdt(insert_history);

        }
        private void update_contract_interset(string loancontract)
        {
            try
            {
                string interest = Hdcheck_interest.Value.ToString();

                if (interest == "0")
                {
                    string sql = "update lncontmaster set contract_interest={0} where loancontract_no ={1} and coop_id={2}";
                    sql = WebUtil.SQLFormat(sql, Hdinterest.Value.ToString(), loancontract, state.SsCoopId);
                    WebUtil.QuerySdt(sql);
                }
                else
                {

                    string sql = "update lncontmaster set contract_interest={0} where loancontract_no ={1} and coop_id={2}";
                    sql = WebUtil.SQLFormat(sql, -1, loancontract, state.SsCoopId);
                    WebUtil.QuerySdt(sql);

                }
            }
            catch { }
        
        }

        private void update_lncontmaster()
        {
            try
            {

                string ls_loantype = "";
                DateTime expirecont_tdate;
                DateTime startcont_tdate;
                DateTime loanapprove_tdate;
                decimal loanrequest_amt = 0;
                decimal loanapprove_amt = 0;
                decimal contcount_end = 0;
                decimal contcount = 0;
                decimal withdrawable_amt = 0;
                DateTime lastaccess_tdate;
                decimal principal_balance= 0;
                DateTime lastcalint_tdate;
                decimal principal_arrear = 0;
                DateTime lastpayment_tdate ;
                decimal intyear_arrear = 0 ;
                decimal interest_arrear = 0;
                decimal fineyear_arrear = 0;
                decimal fine_arrear = 0;
                decimal contract_status = 1;
                string loanobjective_code = "";
                decimal intcompensate_flag = 0;
                try
                {
                    ls_loantype = dw_data_1.GetItemString(1, "loantype_code");  //pom
                }
                catch { }
                try
                {
                    expirecont_tdate = DateTime.ParseExact(dw_data_1.GetItemString(1, "expirecont_tdate"), "dd/MM/yyyy", th);
                }
                catch {
                    expirecont_tdate = state.SsWorkDate;
                }
                try
                {
                    startcont_tdate = DateTime.ParseExact(dw_data_1.GetItemString(1, "startcont_tdate"), "dd/MM/yyyy", th);
                }
                catch {
                    startcont_tdate = state.SsWorkDate;
                }
                try
                {
                    loanapprove_tdate = DateTime.ParseExact(dw_data_1.GetItemString(1, "loanapprove_tdate"), "dd/MM/yyyy", th);
                }
                catch { loanapprove_tdate = state.SsWorkDate; }
                try
                {
                    loanrequest_amt = dw_data_1.GetItemDecimal(1, "loanrequest_amt");  //pom
                }
                catch { loanrequest_amt = 0; }

                try
                {
                    intcompensate_flag = dw_data_1.GetItemDecimal(1, "intcompensate_flag");  //pom
                }
                catch { intcompensate_flag = 0; }

                
                try
                {
                    loanapprove_amt = dw_data_1.GetItemDecimal(1, "loanapprove_amt");  //pom
                }
                catch { loanapprove_amt = 0;  }
                try
                {
                    contcount_end = dw_data_1.GetItemDecimal(1, "contcount_end");  //pom
                }
                catch {  contcount_end = 0; }
                try
                {
                    contcount = dw_data_1.GetItemDecimal(1, "contcount");  //pom
                }
                catch { contcount = 0; }
                try
                {
                    withdrawable_amt = dw_data_1.GetItemDecimal(1, "withdrawable_amt");  //pom
                }
                catch { }
                try
                {
                    lastaccess_tdate = DateTime.ParseExact(dw_data_1.GetItemString(1, "lastaccess_tdate"), "dd/MM/yyyy", th);
                }
                catch { lastaccess_tdate = state.SsWorkDate; }
                try{
                 principal_balance = dw_data_1.GetItemDecimal(1, "principal_balance");  //pom
                }catch{  principal_balance = 0;}
                try
                {
                    lastcalint_tdate = DateTime.ParseExact(dw_data_1.GetItemString(1, "lastcalint_tdate"), "dd/MM/yyyy", th);
                }
                catch { lastcalint_tdate = state.SsWorkDate; }
                try
                {
                    principal_arrear = dw_data_1.GetItemDecimal(1, "principal_arrear");  //pom
                }
                catch { principal_arrear = 0; }
                try
                {
                    lastpayment_tdate = DateTime.ParseExact(dw_data_1.GetItemString(1, "lastpayment_tdate"), "dd/MM/yyyy", th);  //pom
                }
                catch { lastpayment_tdate = state.SsWorkDate; }

                 try
                {
                    fineyear_arrear = dw_data_1.GetItemDecimal(1, "fineyear_arrear");  //pom
                }
                 catch { fineyear_arrear = 0; }

                try
                 {
                     contract_status = dw_data_1.GetItemDecimal(1, "contract_status");  //pom
                 }
                catch { contract_status = 1; }
                

                 try
                 {
                     fine_arrear = dw_data_1.GetItemDecimal(1, "fine_arrear");  //pom
                 }
                 catch { fine_arrear = 0; }


                   try
                {
                    intyear_arrear = dw_data_1.GetItemDecimal(1, "intyear_arrear");  //pom
                }
                catch { intyear_arrear = 0; }

                try
                {
                       interest_arrear = dw_data_1.GetItemDecimal(1, "interest_arrear");  //pom
                }
                catch { interest_arrear = 0; }

                if (interest_arrear != 0)
                {
                    interest_arrear = interest_arrear + intyear_arrear;
                }
                else
                {

                    interest_arrear = intyear_arrear;

                }


                if (fine_arrear != 0)
                {
                    fine_arrear = fine_arrear + fineyear_arrear;
                }
                else
                {

                    fine_arrear = fineyear_arrear;

                }


                try
                {
                    loanobjective_code = dw_data_1.GetItemString(1, "loanobjective_code");  //pom
                }
                catch { loanobjective_code = ""; }
                
                //decimal contract_interest = dw_main.GetItemDecimal(1, "contract_interest");  //pom
                //decimal intyear_arrear = dw_main.GetItemDecimal(1, "intyear_arrear");  //pom
                //decimal fineyear_arrear = dw_main.GetItemDecimal(1, "fineyear_arrear");  //pom

                string sql = @"update lncontmaster set loantype_code={0},expirecont_date={1},startcont_date={2},loanapprove_date={3},loanrequest_amt={4}
                    ,loanapprove_amt={5},withdrawable_amt={6},lastaccess_date={7},principal_balance={8},lastcalint_date={9},principal_arrear={10}
                    ,lastpayment_date={11}, contcount_end={14},contcount={15},intyear_arrear={16},interest_arrear={17},fine_arrear={18},fineyear_arrear={19},contract_status={20} ,loanobjective_code={21},intcompensate_flag={22} where loancontract_no={12} and coop_id={13}";
                sql = WebUtil.SQLFormat(sql, ls_loantype, expirecont_tdate, startcont_tdate, loanapprove_tdate
                              , loanrequest_amt, loanapprove_amt, withdrawable_amt
                              , lastaccess_tdate, principal_balance, lastcalint_tdate, principal_arrear
                              , lastpayment_tdate, dw_data_1.GetItemString(1, "loancontract_no"), state.SsCoopId, contcount_end, contcount, intyear_arrear, interest_arrear, fine_arrear, fineyear_arrear, contract_status, loanobjective_code, intcompensate_flag);
                WebUtil.QuerySdt(sql);


                ////update ครั้งที่ contcount    , ชุดที่    contcount_end
                //string sqlUpdateContcount = @"update lnloanconstant set contcount= {0} ,contcount_end ={1} where coop_id={2}";

                //sqlUpdateContcount = WebUtil.SQLFormat(sqlUpdateContcount, contcount, contcount_end, state.SsCoopId);
                //Sdt d = WebUtil.QuerySdt(sqlUpdateContcount);
                //update ครั้งที่ percent    , ชุดที่    cost_amt
                string sqlUpdateContcountreq = @"update lnreqloan set cost_percent = {0} , cost_amt ={1} where coop_id={2},loanobjective_code={4} and loancontract_no={3}";

                sqlUpdateContcountreq = WebUtil.SQLFormat(sqlUpdateContcountreq, contcount, contcount_end, state.SsCoopId, dw_data_1.GetItemString(1, "loancontract_no"), loanobjective_code);
                 WebUtil.QuerySdt(sqlUpdateContcountreq);
            }
            catch { }


        }
        private void Update_statement()
        {
            //try
            //{
            //    string operate_tdate = dw_data_2.GetItemString(1, "operate_tdate");
            //    string calintfrom_tdate = dw_data_2.GetItemString(1, "calintfrom_tdate");
            //    string calintto_tdate = dw_data_2.GetItemString(1, "calintto_tdate");
            //    string item_status = dw_data_2.GetItemString(1, "item_status");
            //}
            //catch { }

            decimal principal_payment = 0;

            decimal fineyear_arrear = 0; // ปรับค้างปี
            decimal intarr_payment = 0; // ดอกเบี้ยค้างปี
            DateTime operate_tdate ;
            DateTime calintfrom_tdate ;
            DateTime calintto_tdate ;
            string item_status ;
            decimal fine_arrear_2_1 = 0; //ปรับขาด
            decimal fine_period = 0; //ปรับใหม่
            decimal interest_arrear2 = 0; // ดอกเบี้ยขาด
           
            decimal bffineyear_amt = 0; //ปรับค้างยกมา
            decimal bffine_arrear = 0; // ปรับขาดยกมา
            decimal bffine_preiod = 0;// ค่าปรับพึง
            decimal bfintyeararr_amt = 0;// ดอกเบี้ยค้างปียกมา


            decimal bfintarrear_amt = 0; //ดอกเบี้ยขาดยกมา

            decimal interest_period = 0;//ดอกเบี้ยใหม่
            decimal bfprincipal_arrear = 0; // ต้นค้างยกมา

            decimal fine_return = 0;
            decimal interest_return = 0;// ดอกเบี้ยคืน
            decimal interest_payment = 0;
            decimal seq_no = 0;
            decimal principal_balance = 0;
            decimal bfint_preiod = 0;
            decimal principalpay_period = 0;
        
            try
            {
                Int32 count = dw_data_2.RowCount;
                for (Int32 i = 1; i <= count; i++)
                {
                  //  string dfg = dw_data_2.GetItemString(i, "operate_tdate");
                         operate_tdate = DateTime.ParseExact(dw_data_2.GetItemString(i, "operate_tdate"), "dd/MM/yyyy", th);
                         calintfrom_tdate = DateTime.ParseExact(dw_data_2.GetItemString(i, "calintfrom_tdate"), "dd/MM/yyyy", th);
                         calintto_tdate = DateTime.ParseExact(dw_data_2.GetItemString(i, "calintto_tdate"), "dd/MM/yyyy", th);
                          item_status = dw_data_2.GetItemString(i, "item_status");
                          try
                          {
                              principal_payment = dw_data_2.GetItemDecimal(i, "principal_payment");
                          }
                          catch {
                              principal_payment = 0;
                          }
                          try
                          {
                              seq_no = dw_data_2.GetItemDecimal(i, "seq_no");
                          }
                          catch
                          {
                              seq_no = 0;
                          }
                          try
                          {
                              fineyear_arrear = dw_data_2.GetItemDecimal(i, "fineyear_arrear");
                          }
                          catch {
                              fineyear_arrear = 0;
                          }
                          try
                          {
                              intarr_payment = dw_data_2.GetItemDecimal(i, "intarr_payment");
                          }
                          catch {
                              intarr_payment = 0;
                          }
                          try
                          {
                              interest_payment = dw_data_2.GetItemDecimal(i, "interest_payment_1");
                          }
                          catch
                          {
                              interest_payment = 0;
                          }
                           try
                          {
                              fine_arrear_2_1 = dw_data_2.GetItemDecimal(i, "fine_arrear_2_1");
                          }
                          catch {
                              fine_arrear_2_1 = 0;
                          }
                         try
                          {
                              fine_period = dw_data_2.GetItemDecimal(i, "fine_period");
                          }
                          catch {
                              fine_period = 0;
                          }
                         try
                          {
                              interest_arrear2 = dw_data_2.GetItemDecimal(i, "interest_arrear");
                          }
                          catch {
                              interest_arrear2 = 0;
                          }
                       
                          try
                          {
                              bffineyear_amt = dw_data_2.GetItemDecimal(i, "bffineyear_amt");
                          }
                          catch {
                              bffineyear_amt = 0;
                          }

                          try
                          {
                              bffine_arrear = dw_data_2.GetItemDecimal(i, "bffine_arrear");
                          }
                          catch {
                              bffine_arrear = 0;
                          }
                           try
                          {
                              bffine_preiod = dw_data_2.GetItemDecimal(i, "bffine_preiod");
                          }
                          catch {
                              bffine_preiod = 0;
                          }
                          try
                          {
                              bfintyeararr_amt = dw_data_2.GetItemDecimal(i, "bfintyeararr_amt");
                          }
                          catch {
                              bfintyeararr_amt = 0;
                          }
                             try
                          {
                              bfintarrear_amt = dw_data_2.GetItemDecimal(i, "bfintarrear_amt");
                          }
                          catch {
                              bfintarrear_amt = 0;
                          }
                         try
                          {
                              interest_period = dw_data_2.GetItemDecimal(i, "interest_period");
                          }
                          catch {
                              interest_period = 0;
                          }
                          try
                          {
                              bfprincipal_arrear = dw_data_2.GetItemDecimal(i, "bfprincipal_arrear");
                          }
                          catch {
                              bfprincipal_arrear = 0;
                          }
                          try
                          {
                              fine_return = dw_data_2.GetItemDecimal(i, "fine_return");
                          }
                          catch {
                              fine_return = 0;
                          }

                      try
                          {
                              interest_return = dw_data_2.GetItemDecimal(i, "interest_return");
                          }
                          catch {
                              interest_return = 0;
                          }
                     try
                          {
                              principal_balance = dw_data_2.GetItemDecimal(i, "PRINCIPAL_BALANCE");
                          }
                          catch {
                              principal_balance = 0;
                          }
                    try
                          {
                              bfint_preiod = dw_data_2.GetItemDecimal(i, "bfint_preiod");
                          }
                          catch {
                              bfint_preiod = 0;
                          }
                    
                    try
                          {
                              principalpay_period = dw_data_2.GetItemDecimal(i, "principalpay_period_1");
                          }
                          catch {
                              principalpay_period = 0;
                          }

                    
                      try
                      {
                          String sqlStr2 = @"update lncontstatement set 
                                         OPERATE_DATE ={0},CALINT_FROM ={1}, CALINT_TO= {2},        
                                             PRINCIPAL_PAYMENT ={3},INTEREST_PAYMENT={4}  , BFINTARREAR_AMT ={5} , INTEREST_PERIOD ={6} ,      
                                             INTEREST_ARREAR ={7} ,     INTEREST_RETURN ={8}, FINE_RETURN ={9}  ,ITEM_STATUS ={10},         
                                          FINEYEAR_ARREAR ={11}, FINE_PERIOD ={12},  BFFINE_ARREAR={13} 
                                        , BFPRINCIPAL_ARREAR={14} , BFFINEYEAR_AMT={15} , BFINTYEARARR_AMT ={16},bffine_preiod={17},intarr_payment ={18},fine_payment={21},fine_arrear={22},principal_balance = {24},bfint_preiod = {25},principalpay_period = {26}
                                        where loancontract_no={19} and coop_id={20} and seq_no={23}";
                          sqlStr2 = WebUtil.SQLFormat(sqlStr2, operate_tdate, calintfrom_tdate, calintto_tdate, principal_payment
                              , ((interest_arrear2 + interest_period + intarr_payment)), bfintarrear_amt, interest_period
                              , interest_arrear2, interest_return, fine_return, item_status, fineyear_arrear,
                              fine_period, bffine_arrear, bfprincipal_arrear, bffineyear_amt, bfintyeararr_amt,
                              bffine_preiod, intarr_payment, dw_data_1.GetItemString(1, "loancontract_no"),
                              state.SsCoopId, (fine_period + fineyear_arrear + fine_arrear_2_1), fine_arrear_2_1, seq_no, principal_balance, bfint_preiod, principalpay_period);
                          WebUtil.QuerySdt(sqlStr2);
                      }
                      catch { }

                }
            }
            catch { }

        }

        public void WebSheetLoadEnd()
        {
            
        }
        private void Update_lncontperiodpaydet() { 
           decimal period_payment  =0;
           decimal principal_arrear = 0;
           decimal prinpaid_amt = 0;
           DateTime expireperiod_tdate = state.SsWorkDate; ;
           DateTime lastcalfine_tdate = state.SsWorkDate; ;
           decimal period = 0;
           try
           {
               WebUtil.QuerySdt(" delete from lncontperiodpaydet where coop_id = '" + state.SsCoopControl + "' and loancontract_no='" + dw_data_1.GetItemString(1, "loancontract_no") + "' ");
          
               Int32 count = dw_data_4.RowCount;
               for (Int32 i = 1; i <= count; i++)
               {
                   try
                   {
                       period_payment = dw_data_4.GetItemDecimal(i, "period_payment");
                   }
                   catch
                   {
                       period_payment = 0;
                   }

                   try
                   {
                       principal_arrear = dw_data_4.GetItemDecimal(i, "principal_arrear");
                   }
                   catch
                   {
                       principal_arrear = 0;
                   }
                   try
                   {
                       prinpaid_amt = dw_data_4.GetItemDecimal(i, "prinpaid_amt");
                   }
                   catch
                   {
                       prinpaid_amt = 0;
                   }

                   try
                   {
                       expireperiod_tdate = DateTime.ParseExact(dw_data_4.GetItemString(i, "expireperiod_tdate"), "dd/MM/yyyy", th);
                    
                   }
                   catch
                   {
                    
                   }
                   try
                   {
                       lastcalfine_tdate = DateTime.ParseExact(dw_data_4.GetItemString(i, "lastcalfine_tdate"), "dd/MM/yyyy", th);
                   }
                   catch
                   {
                 
                   }


                   try
                   {
                       period = dw_data_4.GetItemDecimal(i, "period");
                   }
                   catch
                   {
                       period = 0;
                   }

                   try
                   {
                       String sqlStr2 = @"insert into lncontperiodpaydet(coop_id,loancontract_no,period,period_payment,lastcalfine_date,principal_arrear,expireperiod_date,prinpaid_amt,fine_arrear ) 
                                        values ({0},{1},{2},{3},{4},{5},{6},{7}, 0)                                               ";
                       sqlStr2 = WebUtil.SQLFormat(sqlStr2, state.SsCoopId, dw_data_1.GetItemString(1, "loancontract_no"), i, period_payment, lastcalfine_tdate, principal_arrear, expireperiod_tdate, prinpaid_amt);
                       WebUtil.QuerySdt(sqlStr2);
                   }
                   catch { }

               }
           }

           catch { }
        
        
        
        }


    }
}