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.gen_fine_date
{
    public partial class gen_fine_date : PageWebReport, WebReport
    {
        protected String app;
        protected String gid;
        protected String rid;
        public void InitJsPostBack()
        {
            dsMain.InitDsMain(this);
            dsMain.DdCoopId();

        }

        public void WebSheetLoadBegin()
        {
            //dsMain.DdCoopId();
            //--- 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)
            {
                loantype();
                dsMain.DATA[0].adtm_start = state.SsWorkDate;
                dsMain.DATA[0].adtm_end = state.SsWorkDate;
                dsMain.DdCoopId();
               // dsMain.DATA[0].coop_id = state.SsCoopControl;
            }


        }
        private void loantype()
        {
            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].start_lntype = type.Substring(0, type.Length - 1);
        }
        public void CheckJsPostBack(string eventArg)
        {

        }

        public void RunReport()
        {
           update_statement_LPX();
           /// จ่ายเงินกู้ภายใน ปี
          // update_lrc();
           //จ่าย เงินกู้ เฉพาะเมืองคง
          // updatestatement_kong();
        }
        public void updatestatement_kong()
        {
            string sql = @"select loancontract_no,expireperiod_date from lncontperiodpaydet  
                       where expireperiod_date between (select accstart_date from cmaccountyear where accsyscls_status =0) and (select accend_date-1 from cmaccountyear where accsyscls_status =0) and loancontract_no ='257/30171'";
       //     string sql = @"select loancontract_no,expireperiod_date from lncontperiodpaydet  
         //               where expireperiod_date between (select accstart_date from cmaccountyear where accsyscls_status =0) and (select accend_date-1 from cmaccountyear where accsyscls_status =0)";
            sql = WebUtil.SQLFormat(sql);
            Sdt dtIns2 = WebUtil.QuerySdt(sql);
            while (dtIns2.Next())
            {
                string update = "update lncontstatement set calfinefrom_date ={0} ,calfineto_date={1} where loancontract_no ={2} and slip_date =to_date('01042017','dd/MM/yyyy') and loanitemtype_code ='B/F'";
                update = WebUtil.SQLFormat(update, dtIns2.GetDate("expireperiod_date"), dtIns2.GetDate("expireperiod_date"), dtIns2.GetString("loancontract_no"));
                 WebUtil.QuerySdt(update);
            
            }
        }
        public void update_statement_LPX() {


            string sql = @"select * from lncontstatement where  loanitemtype_code ='B/F' and  slip_date =to_date('01042017','dd/MM/yyyy') and loancontract_no ='257/30171'";
            sql = WebUtil.SQLFormat(sql);
            Sdt slqdtIns2 = WebUtil.QuerySdt(sql);


            while (slqdtIns2.Next())
            {
                string paydet1 = @"select * from lncontperiodpaydet  where   loancontract_no ={0} and prinpaid_amt <> period_payment and  expireperiod_date <= to_date('31/03/2017','dd/MM/yyyy')";
                paydet1 = WebUtil.SQLFormat(paydet1);
                Sdt slqdtIns3 = WebUtil.QuerySdt(paydet1);
                if (slqdtIns3.Next())
                {
                    up_date_statment_lpxsub(slqdtIns3.GetString("loancontract_no"));
                }
                else {

                    up_date_statment_lpxsub2(slqdtIns3.GetString("loancontract_no"));
                
                }
            }
        
        }

        public void up_date_statment_lpxsub(string loancontract_no)
        {
            try
            {
                string update_date = @"update  lncontstatement set calfinefrom_date =to_date('31/03/2017','dd/MM/yyyy'), calfineto_date =to_date('31/03/2017','dd/MM/yyyy') where  slip_date =to_date('01042017','dd/MM/yyyy')  and loanitemtype_code ='B/F' and loancontract_no={0}";
                update_date = WebUtil.SQLFormat(update_date, loancontract_no);
                WebUtil.QuerySdt(update_date);
            }
            catch { }

            // string statement = @"select * from lncontstatement where slip_date =to_date('01042017','dd/MM/yyyy') and  loancontract_no ='159/ฉ0413'";
            string statement = @"select * from lncontstatement where slip_date =to_date('01042017','dd/MM/yyyy') and loanitemtype_code='B/F' and  loancontract_no={0}";
            statement = WebUtil.SQLFormat(statement, loancontract_no);
            Sdt dtIns = WebUtil.QuerySdt(statement);
            while (dtIns.Next())
            {
                string statement_loan = @"select * from lncontstatement where slip_date >= to_date('01042017','dd/MM/yyyy') and  loancontract_no ={0} and  loanitemtype_code not in ('RPX')";
                statement_loan = WebUtil.SQLFormat(statement_loan, dtIns.GetString("loancontract_no"));
                Sdt dtIns2 = WebUtil.QuerySdt(statement_loan);

                while (dtIns2.Next())
                {
                    if (dtIns2.GetString("loanitemtype_code") == "LPX")
                    {


                        if (dtIns2.GetDecimal("fine_period") > 0) //มีการจ่ายค่าปรับพึง
                        {
                            string sql = @"
                            select * from (
                            select * from lncontstatement where loancontract_no ={0} and slip_date >= to_date('01042017','dd/MM/yyyy')  and 
                              seq_no <{1} and  loanitemtype_code not in ('RPX') order by seq_no desc
                            ) where rownum<=1";
                            sql = WebUtil.SQLFormat(sql, dtIns2.GetString("loancontract_no"), dtIns2.GetString("seq_no"));
                            Sdt dtIns3 = WebUtil.QuerySdt(sql);

                            if (dtIns3.Next())
                            {
                                string up_date = @"update lncontstatement set calfinefrom_date ={0},calfineto_date = {1} where  loancontract_no={2} and seq_no={3}";
                                up_date = WebUtil.SQLFormat(up_date, dtIns3.GetDate("calfineto_date"), dtIns2.GetDate("slip_date"), dtIns2.GetString("loancontract_no"), dtIns2.GetString("seq_no"));
                                WebUtil.QuerySdt(up_date);
                            }
                        }
                        else
                        {

                            string sql = @"
                            select * from (
                            select * from lncontstatement where loancontract_no ={0} and slip_date >= to_date('01042017','dd/MM/yyyy')  and 
                              seq_no <{1} and  loanitemtype_code not in ('RPX') order by seq_no desc
                            ) where rownum<=1";
                            sql = WebUtil.SQLFormat(sql, dtIns2.GetString("loancontract_no"), dtIns2.GetString("seq_no"));
                            Sdt dtIns3 = WebUtil.QuerySdt(sql);

                            if (dtIns3.Next())
                            {
                                string up_date = @"update lncontstatement set calfinefrom_date ={0},calfineto_date = {1} where  loancontract_no={2} and seq_no={3}";
                                up_date = WebUtil.SQLFormat(up_date, dtIns3.GetDate("calfineto_date"), dtIns3.GetDate("calfineto_date"), dtIns2.GetString("loancontract_no"), dtIns2.GetString("seq_no"));
                                WebUtil.QuerySdt(up_date);
                            }


                        }

                    }

                }

            }
        
        }

        public void up_date_statment_lpxsub2(string loancontract_no)
        {

            DateTime date;
            string sqlupdate = @"select * from lncontperiodpaydet  where   loancontract_no ={0} and prinpaid_amt <> period_payment
                                 and  expireperiod_date > to_date('31/03/2017','dd/MM/yyyy') and rownum<=1";
            sqlupdate = WebUtil.SQLFormat(sqlupdate, loancontract_no);
            Sdt dtIns3 =  WebUtil.QuerySdt(sqlupdate);
            if (dtIns3.Next())
            {
               date = dtIns3.GetDate("expireperiod_date");
            
            }
            try
            {
                string update_date = @"update  lncontstatement set calfinefrom_date ={1}, calfineto_date ={1} where  slip_date =to_date('01042017','dd/MM/yyyy')  and loanitemtype_code ='B/F' and loancontract_no={0}";
                update_date = WebUtil.SQLFormat(update_date, loancontract_no, date);
                WebUtil.QuerySdt(update_date);
            }
            catch { }

            // string statement = @"select * from lncontstatement where slip_date =to_date('01042017','dd/MM/yyyy') and  loancontract_no ='159/ฉ0413'";
            string statement = @"select * from lncontstatement where slip_date =to_date('01042017','dd/MM/yyyy') and loanitemtype_code='B/F' and  loancontract_no={0}";
            statement = WebUtil.SQLFormat(statement, loancontract_no);
            Sdt dtIns = WebUtil.QuerySdt(statement);
            while (dtIns.Next())
            {
                string statement_loan = @"select * from lncontstatement where slip_date >= to_date('01042017','dd/MM/yyyy') and  loancontract_no ={0} and  loanitemtype_code not in ('RPX')";
                statement_loan = WebUtil.SQLFormat(statement_loan, dtIns.GetString("loancontract_no"));
                Sdt dtIns2 = WebUtil.QuerySdt(statement_loan);

                while (dtIns2.Next())
                {
                    if (dtIns2.GetString("loanitemtype_code") == "LPX")
                    {


                        if (dtIns2.GetDecimal("fine_period") > 0) //มีการจ่ายค่าปรับพึง
                        {
                            string sql = @"
                            select * from (
                            select * from lncontstatement where loancontract_no ={0} and slip_date >= to_date('01042017','dd/MM/yyyy')  and 
                              seq_no <{1} and  loanitemtype_code not in ('RPX') order by seq_no desc
                            ) where rownum<=1";
                            sql = WebUtil.SQLFormat(sql, dtIns2.GetString("loancontract_no"), dtIns2.GetString("seq_no"));
                            Sdt dtIns3 = WebUtil.QuerySdt(sql);

                            if (dtIns3.Next())
                            {
                                string up_date = @"update lncontstatement set calfinefrom_date ={0},calfineto_date = {1} where  loancontract_no={2} and seq_no={3}";
                                up_date = WebUtil.SQLFormat(up_date, dtIns3.GetDate("calfineto_date"), dtIns2.GetDate("slip_date"), dtIns2.GetString("loancontract_no"), dtIns2.GetString("seq_no"));
                                WebUtil.QuerySdt(up_date);
                            }
                        }
                        else
                        {

                            string sql = @"
                            select * from (
                            select * from lncontstatement where loancontract_no ={0} and slip_date >= to_date('01042017','dd/MM/yyyy')  and 
                              seq_no <{1} and  loanitemtype_code not in ('RPX') order by seq_no desc
                            ) where rownum<=1";
                            sql = WebUtil.SQLFormat(sql, dtIns2.GetString("loancontract_no"), dtIns2.GetString("seq_no"));
                            Sdt dtIns3 = WebUtil.QuerySdt(sql);

                            if (dtIns3.Next())
                            {
                                string up_date = @"update lncontstatement set calfinefrom_date ={0},calfineto_date = {1} where  loancontract_no={2} and seq_no={3}";
                                up_date = WebUtil.SQLFormat(up_date, dtIns3.GetDate("calfineto_date"), dtIns3.GetDate("calfineto_date"), dtIns2.GetString("loancontract_no"), dtIns2.GetString("seq_no"));
                                WebUtil.QuerySdt(up_date);
                            }


                        }

                    }

                }

            }

        }
        public void update_lrc() { 
          //ดึงจ่ายเงินกู้ ภายในปี ออกมา
           // string sql_lrc = @"select * from lncontstatement where slip_date > to_date('01042017','dd/MM/yyyy') and loanitemtype_code='LRC' and loancontract_no ='160/ฉ0021'";
          string sql_lrc = @"select * from lncontstatement where slip_date > to_date('01042017','dd/MM/yyyy') and loanitemtype_code='LRC'";
          sql_lrc = WebUtil.SQLFormat(sql_lrc);
          Sdt dtIns3 = WebUtil.QuerySdt(sql_lrc);


          while (dtIns3.Next())
          {
              string sql_paydet = @"select * from lncontperiodpaydet where  loancontract_no={0} and rownum<=1";
              sql_paydet = WebUtil.SQLFormat(sql_paydet, dtIns3.GetString("loancontract_no"));
              Sdt dtIns3_paydet = WebUtil.QuerySdt(sql_paydet);
              if (dtIns3_paydet.Next())
              {
                  string update_state = @"update lncontstatement set   calfinefrom_date={0},calfineto_date={1} where loancontract_no ={2} and loanitemtype_code ='LRC'";
                  update_state = WebUtil.SQLFormat(update_state, dtIns3_paydet.GetDate("expireperiod_date"), dtIns3_paydet.GetDate("expireperiod_date"),dtIns3.GetString("loancontract_no"));
                  WebUtil.QuerySdt(update_state);
                  update_statement_LRC(dtIns3.GetString("loancontract_no"));
              }
          }
        
        }
        public void update_statement_LRC(string loancontract_no)
        {

            string statement = @"select * from lncontstatement where   loancontract_no ={0} and loanitemtype_code  in ('LRC')";
           // string statement = @"select * from lncontstatement where slip_date =to_date('01042017','dd/MM/yyyy') and loanitemtype_code='B/F'";
            statement = WebUtil.SQLFormat(statement, loancontract_no);
            Sdt dtIns = WebUtil.QuerySdt(statement);
            while (dtIns.Next())
            {
                string statement_loan = @"select * from lncontstatement where slip_date >= {0} and  loancontract_no ={1} and  loanitemtype_code not in ('RPX')";
                statement_loan = WebUtil.SQLFormat(statement_loan, dtIns.GetDate("slip_date"), dtIns.GetString("loancontract_no"));
                Sdt dtIns2 = WebUtil.QuerySdt(statement_loan);

                while (dtIns2.Next())
                {
                    if (dtIns2.GetString("loanitemtype_code") == "LPX")
                    {


                        if (dtIns2.GetDecimal("fine_period") > 0) //มีการจ่ายค่าปรับพึง
                        {
                            string sql = @"
                            select * from (
                            select * from lncontstatement where loancontract_no ={0} and slip_date >= {2}  and 
                              seq_no <{1} and  loanitemtype_code not in ('RPX') order by seq_no desc
                            ) where rownum<=1";
                            sql = WebUtil.SQLFormat(sql, dtIns2.GetString("loancontract_no"), dtIns2.GetString("seq_no"), dtIns.GetDate("slip_date"));
                            Sdt dtIns3 = WebUtil.QuerySdt(sql);

                            if (dtIns3.Next())
                            {
                                string up_date = @"update lncontstatement set calfinefrom_date ={0},calfineto_date = {1} where  loancontract_no={2} and seq_no={3}";
                                up_date = WebUtil.SQLFormat(up_date, dtIns3.GetDate("calfineto_date"), dtIns2.GetDate("slip_date"), dtIns2.GetString("loancontract_no"), dtIns2.GetString("seq_no"));
                                WebUtil.QuerySdt(up_date);
                            }
                        }
                        else
                        {

                            string sql = @"
                            select * from (
                            select * from lncontstatement where loancontract_no ={0} and slip_date >= {2}  and 
                              seq_no <{1} and  loanitemtype_code not in ('RPX') order by seq_no desc
                            ) where rownum<=1";
                            sql = WebUtil.SQLFormat(sql, dtIns2.GetString("loancontract_no"), dtIns2.GetString("seq_no"), dtIns.GetDate("slip_date"));
                            Sdt dtIns3 = WebUtil.QuerySdt(sql);

                            if (dtIns3.Next())
                            {
                                string up_date = @"update lncontstatement set calfinefrom_date ={0},calfineto_date = {1} where  loancontract_no={2} and seq_no={3}";
                                up_date = WebUtil.SQLFormat(up_date, dtIns3.GetDate("calfineto_date"), dtIns3.GetDate("calfineto_date"), dtIns2.GetString("loancontract_no"), dtIns2.GetString("seq_no"));
                                WebUtil.QuerySdt(up_date);
                            }


                        }

                    }

                }

            }



        }
        public void WebSheetLoadEnd()
        {

        }
    }
}