using System;
using CoreSavingLibrary;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using CoreSavingLibrary;
using DataLibrary;
namespace Saving.CriteriaIReport.Age_of_receivables
{
    public partial class Age_of_receivables : PageWebReport, WebReport
    {
        protected String app;
        protected String gid;
        protected String rid;
        [JsPostBack]
        public string PostMemberNo { get; set; }

        Decimal bfprincipal = 0;
        Int32 period = 0;
        public void InitJsPostBack()
        {
            dsMain.InitDsMain(this);
        }

        public void WebSheetLoadBegin()
        {
            //--- Page Arguments
            try
            {
                app = Request["app"].ToString();
            }
            catch { }
            if (app == null || app == "")
            {
                app = state.SsApplication;
            }
            try
            {
                gid = Request["gid"].ToString();
            }
            catch { }
            try
            {
                rid = Request["rid"].ToString();
            }
            catch { }

            //Report Name.
            try
            {
                Sta ta = new Sta(state.SsConnectionString);
                String sql = "";
                sql = @"SELECT REPORT_NAME  
                    FROM WEBREPORTDETAIL  
                    WHERE ( GROUP_ID = '" + gid + @"' ) AND ( REPORT_ID = '" + rid + @"' )";
                Sdt dt = ta.Query(sql);
                ReportName.Text = dt.Rows[0]["REPORT_NAME"].ToString();
                ta.Close();
            }
            catch
            {
                ReportName.Text = "[" + rid + "]";
            }


            if (!IsPostBack)
            {
                dsMain.DdCoopId();
                //dsMain.DdLoanTypeS();
                //dsMain.DdLoanTypeE();
                dsMain.DdMembgroup();
                dsMain.DATA[0].coop_id = state.SsCoopControl;
                dsMain.DATA[0].sdate = state.SsWorkDate;

                string sql = "select loantype_code from lnloantype";
                sql = WebUtil.SQLFormat(sql);
                Sdt dtIns = WebUtil.QuerySdt(sql);
                string type = "";
                while (dtIns.Next())
                {
                    type += dtIns.GetString("loantype_code") + ",";
                }
                dsMain.DATA[0].type_start = type.Substring(0, type.Length - 1);
                this.SetFocusScript("nummin");
                //   dsMain.DATA[0].sdate2 = dsMain.DATA[0].sdate;
            }
        }

        public void CheckJsPostBack(string eventArg)
        {

            if (eventArg == PostMemberNo)
            {
                this.SetFocusScript("end_date");
                //dsMain.DATA[0].member_no = memb_no;
                //dsMain.DATA[0].membname = dsList.DATA[0].fullname;
                //dsMain.DATA[0].entry_id = dsList.DATA[0].entry_id;
            }

        }

        public void RunReport()
        {
            try
            {
                String coop_id = dsMain.DATA[0].coop_id;
                int nummax = dsMain.DATA[0].nummax;
                int nummin = dsMain.DATA[0].nummin;
                String membgroup_code_start = dsMain.DATA[0].memgroup_start;
                String membgroup_code_end = dsMain.DATA[0].memgroup_End;
                string[] loantype = dsMain.DATA[0].type_start.Split(',');  // Split ออกมาใส่ไว้ใน Arrai string
                List<string> last = new List<string>(loantype);      // convert เป็น list  




                string format = "";
                for (int i = 0; i < last.Count; i++)
                {
                    format += string.Format("'{0}'", last[i]);
                    if (i != (last.Count - 1))
                    {
                        format += ",";
                    }
                }

                string format2 = format.Replace("'", "");

                //Code Create table
                //CREATE TABLE ISCOICOOPKSMK."REPORT_AGE_OF_RECEIVABLES" ("MEMBER_NO" VARCHAR2(10), "LOANCONTRACT_NO" VARCHAR2(10), "FULLNAME" VARCHAR2(50), "PRINCIPAL_BALANCE" NUMBER(10,2), "CREDIT_SHOULD_REST" NUMBER(10,2), "PRINCIPAL_ARREAR" NUMBER(10,2), "EXPIREPERIOD_DATE" DATE, "DATE_ARREAR" NUMBER(10,0), "YEAR_ARREAR" NUMBER(10,0), "INTEREST_ARREAR" NUMBER(10,2), "INTEREST_FIVE_ARREAR" NUMBER(10,2), "FINE_ARREAR" NUMBER(10,2), "FINE_FIVE_ARREAR" NUMBER(10,2), "COOP_ID" VARCHAR2(10)) ;


                getLoancontract_no(membgroup_code_start.Trim(), membgroup_code_end.Trim(), nummin, nummax, format);





                iReportArgument arg = new iReportArgument();

                arg.Add("coop_id", iReportArgumentType.String, coop_id);
                arg.Add("nummax", iReportArgumentType.Integer, nummax);
                arg.Add("nummin", iReportArgumentType.Integer, nummin);
                arg.Add("work_date", iReportArgumentType.Date, dsMain.DATA[0].sdate);
                if (dsMain.DATA[0].types == 1)
                {
                    arg.Add("loantype", iReportArgumentType.String, dsMain.DATA[0].type_start + "  (เฉพาะดอกเบี้ยค้าง)");
                }
                else
                {
                    arg.Add("loantype", iReportArgumentType.String, dsMain.DATA[0].type_start);
                }
                iReportBuider report = new iReportBuider(this, arg);
                report.Retrieve();
            }
            catch (Exception ex)
            {
                LtServerMessage.Text = WebUtil.ErrorMessage(ex);
            }

        }


        private void getLoancontract_no(string membgroup_code_start, string membgroup_code_end, int min, int max, string format)
        { // ดึงเลขสัญญาที่เป็นหนี้ค้าง ออกมา

            string loancontract_no = "";
            DateTime expireperiod = state.SsWorkDate;
            DateTime sdate = state.SsWorkDate;
            int period = 0;
            Decimal pri_balance = 0;

            String year = @"select accstart_date 
                            from cmaccountyear where accstart_date <=  {0} and accend_date >= {0}";
            year = WebUtil.SQLFormat(year, dsMain.DATA[0].sdate);
            Sdt ds = WebUtil.QuerySdt(year);
            if (ds.Next())
            {
                sdate = ds.GetDate("accstart_date");


            }


            //else
            //{
            //    typess = perid;
            //}

            //           string sql = @"select l.loancontract_no from lncontmaster l inner join lncontperiodpaydet det on  l.loancontract_no= det.loancontract_no 
            //                        inner join mbmembmaster mb on l.member_no=mb.member_no
            //                        where  l.principal_arrear !=0 and contract_status =1 and mb.membgroup_code between {0} and {1} 
            //                        and (det.prinpaid_amt <> det.period_payment) and EXTRACT(YEAR FROM TO_DATE(det.expireperiod_date, 'DD-MON-RR')) between EXTRACT(YEAR FROM add_months(to_date(SYSDATE,'DD-MON-RR'), -12*{2} )) and EXTRACT(YEAR FROM add_months(to_date(SYSDATE,'DD-MON-RR'), -12*{3} ))+1
            //                        group by l.loancontract_no";

            //           string sql = @"select * from ( select l.loancontract_no from lncontmaster l inner join lncontperiodpaydet det on  l.loancontract_no= det.loancontract_no 
            //                        inner join mbmembmaster mb on l.member_no=mb.member_no
            //                        where  l.principal_arrear !=0 and contract_status =1 and mb.membgroup_code between {0} and {1} 
            //                        and (det.prinpaid_amt <> det.period_payment) and det.expireperiod_date < {2} group by l.loancontract_no
            //                        union
            //                        select lncontmaster.loancontract_no from lncontmaster inner join lncontstatement on lncontmaster.loancontract_no =
            //                        lncontstatement.loancontract_no where    slip_date <
            //                        {2}  and lncontstatement.principal_balance > 0 and
            //                         item_status =1 and seq_no= (select max(stm.seq_no) from  lncontstatement stm where
            //                         stm.slip_date < {2} and  stm.loancontract_no= lncontmaster.loancontract_no   )) group by loancontract_no";

            int typess = dsMain.DATA[0].types;
            if (typess == 1)
            {
                string sql = @"  select lncontmaster.loancontract_no ,lncontstatement.principal_payment,lncontstatement.bfintyeararr_amt from lncontmaster inner join lncontstatement on lncontmaster.loancontract_no =
lncontstatement.loancontract_no inner join mbmembmaster mb on lncontmaster.member_no=mb.member_no  where     seq_no= (select max(stm.seq_no) from  lncontstatement stm where
 stm.slip_date  between {3} and  {2} and  stm.loancontract_no= lncontmaster.loancontract_no and  item_status =1  )  and lncontstatement.bfprincipal_arrear = 0 and lncontstatement.bfintyeararr_amt >0
and mb.membgroup_code between {0} and {1} and lncontmaster.loantype_code in (" + format + ") ";

                try
                {
                    string delete = "delete from report_age_of_receivables where coop_id={0}"; //ลบข้อมูลก่อน Insert ใหม่
                    delete = WebUtil.SQLFormat(delete, state.SsCoopId);
                    WebUtil.QuerySdt(delete);
                }
                catch { }

                //  sql = WebUtil.SQLFormat(sql, membgroup_code_start, membgroup_code_end, max, min);
                //Sdt dt = WebUtil.QuerySdt(sql);

                sql = WebUtil.SQLFormat(sql, membgroup_code_start, membgroup_code_end, dsMain.DATA[0].sdate, sdate);
                Sdt dt = WebUtil.QuerySdt(sql);
                while (dt.Next())
                {
                    loancontract_no = dt.GetString("loancontract_no");

                    Insert_report_Age_of_receivables(loancontract_no, 0, 0, 0); // ประมวลผลรายงานก่อน ออกรายงาน
                }
            }
            else
            {
                string sql = @"  select lncontmaster.loancontract_no,lncontmaster.loanapprove_amt,  lncontstatement.bfprincipal_arrear,lncontstatement.principal_balance,lncontstatement.loanitemtype_code,lncontstatement.principal_payment from lncontmaster inner join lncontstatement on lncontmaster.loancontract_no =
lncontstatement.loancontract_no inner join mbmembmaster mb on lncontmaster.member_no=mb.member_no  where     seq_no= (select max(stm.seq_no) from  lncontstatement stm where
 stm.slip_date  between {3} and  {2} and  stm.loancontract_no= lncontmaster.loancontract_no and  item_status =1  )  and lncontstatement.bfprincipal_arrear > 0
and mb.membgroup_code between {0} and {1} and lncontmaster.loantype_code in (" + format + ") ";


                //  sql = WebUtil.SQLFormat(sql, membgroup_code_start, membgroup_code_end, max, min);
                //Sdt dt = WebUtil.QuerySdt(sql);

                sql = WebUtil.SQLFormat(sql, membgroup_code_start, membgroup_code_end, dsMain.DATA[0].sdate, sdate);
                Sdt dt = WebUtil.QuerySdt(sql);


                try
                {
                    string delete = "delete from report_age_of_receivables where coop_id={0}"; //ลบข้อมูลก่อน Insert ใหม่
                    delete = WebUtil.SQLFormat(delete, state.SsCoopId);
                    WebUtil.QuerySdt(delete);
                }
                catch { }
                int date = dsMain.DATA[0].sdate.Year;
                int startdate = dsMain.DATA[0].sdate.AddYears((-min) + 1).Year; //2015
                int enddate = dsMain.DATA[0].sdate.AddYears((-max) + 1).Year;// 2011
                while (dt.Next())
                {
                    loancontract_no = dt.GetString("loancontract_no");

                    period = 0;
                    // if (dt.GetString("loanitemtype_code") == "LPX")
                    // {
                    //     pri_balance = dt.GetDecimal("principal_balance") + dt.GetDecimal("principal_payment");

                    // }
                    // else
                    // {
                    //      pri_balance = dt.GetDecimal("principal_balance");

                    //}

                    //      bfprincipal = dt.GetDecimal("bfprincipal_arrear");


                    string sql2 = "";
                    if (dsMain.DATA[0].sdate >= state.SsWorkDate)
                    {
                        sql2 = @"select l.loancontract_no,det.expireperiod_date from lncontmaster l inner join lncontperiodpaydet det on  l.loancontract_no= det.loancontract_no 
                            inner join mbmembmaster mb on l.member_no=mb.member_no
                            where  l.principal_arrear !=0 and contract_status =1 and mb.membgroup_code between {0} and {1} 
                            and (det.prinpaid_amt <> det.period_payment) and det.expireperiod_date <= {2} and rownum<=1 and l.loancontract_no={3} order by period asc";

                        sql2 = WebUtil.SQLFormat(sql2, membgroup_code_start, membgroup_code_end, dsMain.DATA[0].sdate, loancontract_no);
                        Sdt dt2 = WebUtil.QuerySdt(sql2);
                        if (dt2.Next())
                        {
                            expireperiod = dt2.GetDate("expireperiod_date");
                        }
                        dt2.Clear();
                    }
                    else
                    {
                        Decimal payment = dt.GetDecimal("loanapprove_amt") - dt.GetDecimal("principal_balance");
                        String queryexp = @" select * from lncontperiodpaydet where loancontract_no = {0} and coop_id = {1} and expireperiod_date <= {2} order by period asc";
                        queryexp = WebUtil.SQLFormat(queryexp, loancontract_no, state.SsCoopControl, dsMain.DATA[0].sdate);
                        Sdt gt = WebUtil.QuerySdt(queryexp);

                        while (gt.Next())
                        {
                            if (payment >= 0)
                            {
                                payment = payment - gt.GetDecimal("period_payment");
                                expireperiod = gt.GetDate("expireperiod_date");
                                period = gt.GetInt32("period");

                            }
                            else
                            {


                            }

                        }

                        gt.Clear();
                        //                               sql2 = @"select det.loancontract_no,det.expireperiod_date from   lncontperiodpaydet det                              
                        //                            where     det.expireperiod_date <= {2} and rownum<=1 and det.loancontract_no={3} order by period asc";

                    }


                    date = expireperiod.Year;

                    int a = date.CompareTo(startdate);//  2015 <=2011   start <=date   <=0
                    int b = date.CompareTo(enddate);//    2011 >=2011   date >=enddate >=0
                    if (date.CompareTo(startdate) <= 0 && date.CompareTo(enddate) >= 0)
                    {
                        if (loancontract_no == "155/10296")
                        {


                        }

                        Insert_report_Age_of_receivables(loancontract_no, period, pri_balance, bfprincipal); // ประมวลผลรายงานก่อน ออกรายงาน

                    }

                }
            }

        }

        
private void Insert_report_Age_of_receivables(string loancontract_no, int perid, Decimal prin_balance, Decimal bfprin_balance)
        {
            try
            {


                string sql = "";


                int typess = dsMain.DATA[0].types;
                if (typess == 1)
                {
                    sql = @"select 
                            mb.member_no,(mbucfprename.prename_desc||  mb.memb_name || ' '|| mb.memb_surname) as fullname,ln.principal_balance,ln.principal_arrear
                            ,ln.intyear_arrear,ln.fineyear_arrear 
                            from lncontmaster ln, mbmembmaster mb,mbucfprename  where ln.member_no = mb.member_no and mb.prename_code= mbucfprename.prename_code and ln.loancontract_no={0} and ln.coop_id={1}";

                }
                else
                {


                    if (dsMain.DATA[0].sdate >= state.SsWorkDate)
                    {
                        if (loancontract_no == "155/10296")
                        {
                        
                        
                        }
                        sql = @"select 
                            mb.member_no,(mbucfprename.prename_desc||  mb.memb_name || ' '|| mb.memb_surname) as fullname,ln.principal_balance,ln.principal_arrear
                            ,ln.intyear_arrear,ln.fineyear_arrear,(select expireperiod_date from lncontperiodpaydet 
                            where loancontract_no={0} and coop_id={1} and prinpaid_amt <> period_payment and rownum <=1 
                            and expireperiod_date <= {2}) as expireperiod_date, ROUND((select ({2}-expireperiod_date)/365 
                            from lncontperiodpaydet where loancontract_no={0} 
                            and prinpaid_amt <> period_payment and rownum <=1 and expireperiod_date <= {2})) as year, 
                            ROUND((select ({2}-expireperiod_date) 
                            from lncontperiodpaydet where loancontract_no={0} 
                            and prinpaid_amt <> period_payment and rownum <=1 and expireperiod_date <= {2})) as date_arrear 
                            from lncontmaster ln, mbmembmaster mb,mbucfprename  where ln.member_no = mb.member_no and mb.prename_code= mbucfprename.prename_code and ln.loancontract_no={0} and ln.coop_id={1}";

                        //                    sql = @"  select 
                        //                            mb.member_no,(mbucfprename.prename_desc||  mb.memb_name || ' '|| mb.memb_surname) as fullname,ln.principal_balance,ln.principal_arrear
                        //                            ,ln.intyear_arrear,ln.fineyear_arrear,(select expireperiod_date from lncontperiodpaydet 
                        //                            where loancontract_no={0} and coop_id={1} and period = {3}) as expireperiod_date, ROUND((select ({2}-expireperiod_date)/365 
                        //                            from lncontperiodpaydet where loancontract_no={0} 
                        //                            and period = {4})) as year, 
                        //                            ROUND((select ({2}-expireperiod_date) 
                        //                            from lncontperiodpaydet where loancontract_no={0} 
                        //                            and period = {4})) as date_arrear
                        //                            from lncontmaster ln, mbmembmaster mb,mbucfprename   where   ln.member_no = mb.member_no and mb.prename_code= mbucfprename.prename_code and ln.loancontract_no={0} and ln.coop_id={1}";

                    }
                    else
                    {
                        sql = @"  select 
                            mb.member_no,(mbucfprename.prename_desc||  mb.memb_name || ' '|| mb.memb_surname) as fullname,ln.principal_balance,ln.principal_arrear
                            ,ln.intyear_arrear,ln.fineyear_arrear,(select expireperiod_date from lncontperiodpaydet 
                            where loancontract_no={0} and coop_id={1} and period = {3}) as expireperiod_date, ROUND((select ({2}-expireperiod_date)/365 
                            from lncontperiodpaydet where loancontract_no={0} 
                            and period = {4})) as year, 
                            ROUND((select ({2}-expireperiod_date) 
                            from lncontperiodpaydet where loancontract_no={0} 
                            and period = {4})) as date_arrear
                            from lncontmaster ln, mbmembmaster mb,mbucfprename   where   ln.member_no = mb.member_no and mb.prename_code= mbucfprename.prename_code and ln.loancontract_no={0} and ln.coop_id={1}";


                    }
                }
                decimal intyear_arrear5 = 0;
                decimal fineyear_arrear5 = 0;
                string member_no = "";
                string fullname = "";
                decimal principal_balance = 0;
                decimal credit_should_rest = 0;
                decimal principal_arrear = 0;
                DateTime expireperiod_date = state.SsWorkDate;
                int date_arrear = 0;
                int year_arrear = 0;
                decimal interest_arrear = 0;
                decimal interest_five_arrear = 0;
                decimal fine_arrear = 0;
                decimal fine_five_arrear = 0;
                sql = WebUtil.SQLFormat(sql, loancontract_no, state.SsCoopId, dsMain.DATA[0].sdate, perid, perid);
                Sdt dt = WebUtil.QuerySdt(sql);
                if (dt.Next())
                {
                    member_no = dt.GetString("member_no");
                    fullname = dt.GetString("fullname");

                    expireperiod_date = dt.GetDate("expireperiod_date");
                    if (typess == 1)
                    {
                        year_arrear = 0;
                        date_arrear = 0;

                    }
                    else
                    {

                        //if (dsMain.DATA[0].sdate >= state.SsWorkDate)
                        //{
                        //    year_arrear = dt.GetInt32("year");
                        //}
                        //else
                        //{
                        //    year_arrear = dt.GetInt32("year") + 1;
                        //}
                        date_arrear = dt.GetInt32("date_arrear");
                        year_arrear = Convert.ToInt32(Math.Round(Convert.ToDecimal(date_arrear / 365))) + 1;
                    }
                   
                    interest_arrear = dt.GetDecimal("intyear_arrear");
                    fine_arrear = dt.GetDecimal("fineyear_arrear");

                    //if (dsMain.DATA[0].sdate >= state.SsWorkDate)
                    //{
                    principal_balance = dt.GetDecimal("principal_balance");
                    principal_arrear = dt.GetDecimal("principal_arrear");
                    //}
                    //else
                    //{
                    //    principal_balance = prin_balance;
                    //    principal_arrear =  bfprin_balance;

                    //}

                    // credit_should_rest = dt.GetDecimal("credit_should_rest");

                }
                dt.Clear();
                //String sSql = "select sum(interest_arrear) as intarr,sum(principal_arrear) as prinarr,sum(principal_payment) as prinpay from lncontstatement where loancontract_no='" + loancontract_no + "' and loanitemtype_code='LPX' and slip_date >= {0} ";
                //sSql = WebUtil.SQLFormat(sSql, dsMain.DATA[0].sdate);
                //Sdt b = WebUtil.QuerySdt(sSql);
                //if(b.Next()){
                //    interest_arrear = interest_arrear + b.GetDecimal("intarr");
                //    //if (dsMain.DATA[0].sdate >= state.SsWorkDate)
                //    //{
                //        principal_arrear = principal_arrear + b.GetDecimal("prinarr");
                //        principal_balance = principal_balance + b.GetDecimal("prinpay");
                //   // }

                //}
                String sSql = "select (interest_arrear) as intarr,(bfprincipal_arrear) as prinarr,(principal_payment) as prinpay from lncontstatement where loancontract_no='" + loancontract_no + "' and loanitemtype_code='LPX' and slip_date > {0} ";
                sSql = WebUtil.SQLFormat(sSql, dsMain.DATA[0].sdate);
                Sdt b = WebUtil.QuerySdt(sSql);
                while (b.Next())
                {
                    interest_arrear = interest_arrear + b.GetDecimal("intarr");
                    //if (dsMain.DATA[0].sdate >= state.SsWorkDate)
                    //{
                    if (b.GetDecimal("prinarr") >= b.GetDecimal("prinpay"))
                    {
                        principal_arrear = principal_arrear + b.GetDecimal("prinpay");
                    }
                    else
                    {
                        principal_arrear = b.GetDecimal("prinarr");
                    }
                    principal_balance = principal_balance + b.GetDecimal("prinpay");
                    // }

                }

                b.Clear();
                try
                {
                    decimal five_bf = 0;
                    decimal five_lpx = 0;
                    decimal bfintyeararr_amt = 0;
                    decimal fineyear_arrear = 0;

                    //ดึง ปีบัญชีปัจจุบันออกมา โดยใช้วันที่จากหน้าจอ
                    DateTime accend_date_date = DateTime.Today;
                    DateTime accstart_date_date = DateTime.Today;
                    string accend_date = @"select accstart_date,accend_date from cmaccountyear where ({0}  between accstart_date and accend_date) and coop_id={1}";
                    accend_date = WebUtil.SQLFormat(accend_date, dsMain.DATA[0].sdate, state.SsCoopId);
                    Sdt accend_date_std = WebUtil.QuerySdt(accend_date);
                    if (accend_date_std.Next())
                    {
                        accend_date_date = accend_date_std.GetDate("accend_date");
                        accstart_date_date = accend_date_std.GetDate("accstart_date");
                    }


                    string five1 = @"select sum(interest_payment) as itm ,sum(fine_payment) as ftm  from lncontstatement where loancontract_no={0} and coop_id= {1} and loanitemtype_code in ('LPX','LRC') and slip_date <= {2}";
                    //DateTime yearstart = dsMain.DATA[0].sdate2.AddYears(-dsMain.DATA[0].nummax);
                    //DateTime yearend = dsMain.DATA[0].sdate2;

                    five1 = WebUtil.SQLFormat(five1, loancontract_no, state.SsCoopId, dsMain.DATA[0].sdate);
                    Sdt five1_std = WebUtil.QuerySdt(five1);
                    if (five1_std.Next())
                    {
                        five_lpx = five1_std.GetDecimal("itm");
                        fineyear_arrear = five1_std.GetDecimal("ftm");
                    }
                    five1_std.Clear();
                    string five2 = @"select sum(bfint_preiod) as bfint_preiod,sum(bffine_preiod) as bffine_preiod from   lncontstatement where loancontract_no={0} and
                                    loanitemtype_code in ('LPX','LTA','LRC') and coop_id={1} 
                                    and slip_date  < {2}   and item_status = 1   ";
                    five2 = WebUtil.SQLFormat(five2, loancontract_no, state.SsCoopId, dsMain.DATA[0].sdate.AddYears(-5), accend_date_date.AddYears(-1));

                    Sdt five2_std = WebUtil.QuerySdt(five2);
                    if (five2_std.Next())
                    {
                        five_bf = five2_std.GetDecimal("bfint_preiod");
                        bfintyeararr_amt = five2_std.GetDecimal("bffine_preiod");

                    }

                    five2_std.Clear();
                    if (five_bf > five_lpx)
                    {//ถ้าด/บพึง ย้อนหลัง 5 ปี มากกว่า ยอดที่ชำระทั้งหมด  

                        interest_five_arrear = five_bf - five_lpx;

                    }
                    else
                    {
                        interest_five_arrear = 0;
                    }
                    if (bfintyeararr_amt > fineyear_arrear)
                    {//ถ้าด/บพึง ย้อนหลัง 5 ปี มากกว่า ยอดที่ชำระทั้งหมด  

                        fine_five_arrear = bfintyeararr_amt - fineyear_arrear;

                    }
                    else
                    {
                        fine_five_arrear = 0;
                    }




                }
                catch { }



                string intarr_payment = @"select sum(intarr_payment) as intarr_payment,sum(fineyear_arrear) as fineyear_arrear  from lncontstatement where loancontract_no={0} and slip_date between {1} and {2} and coop_id={3}  and loanitemtype_code ='LPX'";
                intarr_payment = WebUtil.SQLFormat(intarr_payment, loancontract_no, dsMain.DATA[0].sdate, state.SsWorkDate, state.SsCoopId);
                Sdt intarr_payment_std = WebUtil.QuerySdt(intarr_payment);
                if (intarr_payment_std.Next())
                {
                    interest_arrear += intarr_payment_std.GetDecimal("intarr_payment");
                    fine_arrear += intarr_payment_std.GetDecimal("fineyear_arrear");
                }

                intarr_payment_std.Clear();
                if (dsMain.DATA[0].sdate >= state.SsWorkDate)
                {
                    string principal_arrear_2 = @"select sum(principal_arrear) as principal_arrear from lncontperiodpaydet where loancontract_no={0} and expireperiod_date >= {1} and coop_id={2} and principal_arrear > 0";
                    principal_arrear_2 = WebUtil.SQLFormat(principal_arrear_2, loancontract_no, dsMain.DATA[0].sdate, state.SsCoopId);
                    Sdt principal_arrear_2_std = WebUtil.QuerySdt(principal_arrear_2);
                    if (principal_arrear_2_std.Next())
                    {
                        principal_arrear -= principal_arrear_2_std.GetDecimal("principal_arrear");
                    }
                    principal_arrear_2_std.Clear();
                }

                ////// ดอกเบี้ยค้าง 5 ปี 


                try
                {
                    // ชำระ ดอกเบี้ยค้างอดีต จนถึงปัจจุบัน
                    string intarr_payment2 = @"select sum(intarr_payment) as intarr_payment,sum(fineyear_arrear) as fineyear_arrear from lncontstatement where  loancontract_no ={0}   and  slip_date <= {1}  and  loanitemtype_code =  'LPX' and item_status = 1 and coop_id ={2}";
                    intarr_payment2 = WebUtil.SQLFormat(intarr_payment2, loancontract_no, dsMain.DATA[0].sdate, state.SsCoopId);
                    Sdt intarr_payment2_std = WebUtil.QuerySdt(intarr_payment2);
                    if (intarr_payment2_std.Next())
                    {
                        interest_five_arrear = intarr_payment2_std.GetDecimal("intarr_payment");
                        fine_five_arrear = intarr_payment2_std.GetDecimal("fineyear_arrear");
                    }

                    // ดึงวันที่ 5ปี ย้อนหลัง
                    DateTime dateintyear5 = DateTime.Today;
                    string intyear5 = @"select ADD_MONTHS(accend_date,-6*12) as accend_date  from cmaccountyear where {0} between accstart_date and accend_date and coop_id={1}";
                    intyear5 = WebUtil.SQLFormat(intyear5, dsMain.DATA[0].sdate, state.SsCoopId);
                    Sdt intyear5_std = WebUtil.QuerySdt(intyear5);
                    if (intyear5_std.Next())
                    {
                        dateintyear5 = intyear5_std.GetDate("accend_date");
                    }

                    string interest_arrear5 = @"select sum(interest_arrear) as interest_arrear,sum(fine_arrear) as fine_arrear from ln_pipe where loancontract_no ={0} and slip_date <= {1}";
                    interest_arrear5 = WebUtil.SQLFormat(interest_arrear5, loancontract_no, dateintyear5);
                    Sdt interest_arrear5_std = WebUtil.QuerySdt(interest_arrear5);
                    if (interest_arrear5_std.Next())
                    {
                        intyear_arrear5 = interest_arrear5_std.GetDecimal("interest_arrear");
                        fineyear_arrear5 = interest_arrear5_std.GetDecimal("fine_arrear");
                    }
                    intyear_arrear5 -= interest_five_arrear;

                    if (intyear_arrear5 <= 0)
                    {
                        intyear_arrear5 = 0;

                    }



                    fineyear_arrear5 -= fine_five_arrear;

                    if (fineyear_arrear5 <= 0)
                    {
                        fineyear_arrear5 = 0;

                    }
                }
                catch { }


                if (typess == 1)//กรณี ออกแบบ เฉพาะดอกเบี้ยค้าง
                {
                    string sql2 = "insert into report_age_of_receivables(member_no,loancontract_no,fullname,principal_balance,credit_should_rest,principal_arrear,expireperiod_date,date_arrear,year_arrear,interest_arrear,interest_five_arrear,fine_arrear,fine_five_arrear,coop_id) values({0},{1},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11},{12},{13})";
                    sql2 = WebUtil.SQLFormat(sql2, member_no, loancontract_no, fullname, principal_balance, (principal_balance - principal_arrear), principal_arrear, expireperiod_date, date_arrear, 0, interest_arrear, intyear_arrear5, fine_arrear, fineyear_arrear5, state.SsCoopId);
                    WebUtil.QuerySdt(sql2);
                }
                else
                {
                  
                    if (principal_balance > 0 && principal_arrear > 0 && (year_arrear >= dsMain.DATA[0].nummin && year_arrear <= dsMain.DATA[0].nummax))
                    {
                        /*
                         int nummax = dsMain.DATA[0].nummax;
                         int nummin = dsMain.DATA[0].nummin;
                         */
                        string sql2 = "insert into report_age_of_receivables(member_no,loancontract_no,fullname,principal_balance,credit_should_rest,principal_arrear,expireperiod_date,date_arrear,year_arrear,interest_arrear,interest_five_arrear,fine_arrear,fine_five_arrear,coop_id) values({0},{1},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11},{12},{13})";
                        sql2 = WebUtil.SQLFormat(sql2, member_no, loancontract_no, fullname, principal_balance, (principal_balance - principal_arrear), principal_arrear, expireperiod_date, date_arrear, year_arrear, interest_arrear, intyear_arrear5, fine_arrear, fineyear_arrear5, state.SsCoopId);
                        WebUtil.QuerySdt(sql2);
                    }
                }

            }
            catch (Exception e) { LtServerMessage.Text = WebUtil.ErrorMessage(e.ToString()); }

        }
        public void WebSheetLoadEnd()
        {

        }
    }
}