using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using DataLibrary;
using CoreSavingLibrary;

namespace Saving.CriteriaIReport.Interest_on_fines2
{
    public partial class Interest_on_fines2 : PageWebReport, WebReport
    {
        protected String app;
        protected String gid;
        protected String rid;
     
        public void InitJsPostBack()
        {
            dsMain.InitDsMain(this);
         
            dsMain.DdMembgroup();
        }

        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.DdMembgroup();
                dsMain.DdCoopId();
                dsMain.DATA[0].coop_id = state.SsCoopControl;
                dsMain.DATA[0].adtm_sdate = state.SsWorkDate;
                Page.ClientScript.RegisterStartupScript(this.GetType(), "CallMyFunction", "MyFunction()", true);
                loantype();
            }
          
          
        }
        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()
        {
            delete_from_report_interest_on_fines();
            Insert_report_interest_on_fines();
        }
        private void delete_from_report_interest_on_fines() {

            string sql = "delete from report_interest_on_fines where coop_id={0}";
            sql = WebUtil.SQLFormat(sql, state.SsCoopId);
            WebUtil.QuerySdt(sql);
        }

        private void Insert_report_interest_on_fines()
        {
            try
            {
                string[] loantype = dsMain.DATA[0].start_lntype.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 sql = "";
                Sdt dt = null;
                if (dsMain.DATA[0].operation_flag == "1")
                {

                    sql = @"select ln.intyear_arrear,ln.fine_arrear,ln.principal_balance,ln.loancontract_no,mb.member_no,ln.principal_arrear,mbucfprename.prename_desc,mb.memb_name,mb.memb_surname,mb.membgroup_code,ln.interest_arrear,ln.fineyear_arrear from lncontmaster ln inner join mbmembmaster mb on ln.member_no=mb.member_no inner join mbucfprename on mb.prename_code = mbucfprename.prename_code where ln.contract_status=1 and ln.coop_id={0} and mb.member_no between {1} and {2} and ln.loantype_code in (" + format + ") and startcont_date <= {3}";
                    sql = WebUtil.SQLFormat(sql, state.SsCoopId, dsMain.DATA[0].member_s, dsMain.DATA[0].member_e, dsMain.DATA[0].adtm_sdate);
                      dt = WebUtil.QuerySdt(sql);
                }
                else {
                    sql = @"select ln.intyear_arrear,ln.fine_arrear,ln.principal_balance,ln.loancontract_no,mb.member_no,ln.principal_arrear,mbucfprename.prename_desc,mb.memb_name,mb.memb_surname,mb.membgroup_code,ln.interest_arrear,ln.fineyear_arrear from lncontmaster ln inner join mbmembmaster mb on ln.member_no=mb.member_no inner join mbucfprename on mb.prename_code = mbucfprename.prename_code where ln.contract_status=1 and ln.coop_id={0} and mb.membgroup_code between {1} and {2} and ln.loantype_code in (" + format + ") and startcont_date <= {3}";
                    sql = WebUtil.SQLFormat(sql, state.SsCoopId, dsMain.DATA[0].membgroup_start, dsMain.DATA[0].membgroup_end, dsMain.DATA[0].adtm_sdate);
                      dt = WebUtil.QuerySdt(sql);
                
                }

                string member_no = "";
                string loancontract_no = "";

                decimal principal_balance = 0;
                decimal principal_arrear = 0;
                decimal interest_new =0;
                decimal fine_new = 0;
                decimal fine_arrear = 0;
                string full_name = "";
                string membgroup_code = "";
                string name = "";
                string lastname = "";
                decimal intyear_arrear = 0;
                decimal fineyear_arrear = 0;
                decimal interest_arrear = 0;
           
                while (dt.Next())
                {
                    loancontract_no = dt.GetString("loancontract_no");
                    member_no = dt.GetString("member_no");
                    principal_arrear = dt.GetDecimal("principal_arrear");
                    name = dt.GetString("memb_name");
                    lastname = dt.GetString("memb_surname");
                    full_name = dt.GetString("prename_desc") + dt.GetString("memb_name") + "  " + dt.GetString("memb_surname"); 
                    membgroup_code = dt.GetString("membgroup_code");
                   
                  
                    interest_arrear = dt.GetDecimal("interest_arrear");
                    fineyear_arrear = dt.GetDecimal("fineyear_arrear");
                    principal_balance = dt.GetDecimal("principal_balance");
                    fine_arrear = dt.GetDecimal("fine_arrear");
                    interest_arrear = dt.GetDecimal("interest_arrear");
                    intyear_arrear = dt.GetDecimal("intyear_arrear");
                    if (fine_arrear == fineyear_arrear)
                    {
                        fine_arrear = 0;
                    }
                    if (interest_arrear == intyear_arrear)
                    {
                        interest_arrear = 0;
                    }
                    if (fine_arrear > fineyear_arrear)
                    {
                        fine_arrear = fine_arrear - fineyear_arrear;
                    }
                    if (interest_arrear > intyear_arrear)
                    {
                        interest_arrear = interest_arrear - intyear_arrear;
                    }


                    decimal principal_arrear2 = 0;
                    decimal principal_payment2 = 0;
                    decimal fineyear_arrear2 = 0;
                    decimal intyear_arrear2 = 0;
                    decimal interest_arrear2 = 0;
                    decimal fine_arrear2 = 0;

                    try
                    {
                        string sql_statement = @"select sum(principal_arrear) as principal_arrear,sum(principal_payment) as principal_payment ,sum(fineyear_arrear) as fineyear_arrear ,sum(intarr_payment) as intyear_arrear,sum(interest_arrear) as interest_arrear , sum(fine_arrear) as fine_arrear from lncontstatement where loancontract_no={0} and coop_id={1} and slip_date between {2} and {3} and loanitemtype_code not in ('RPX','RRC')";
                        sql_statement = WebUtil.SQLFormat(sql_statement, loancontract_no, state.SsCoopId, dsMain.DATA[0].adtm_sdate.AddDays(1), state.SsWorkDate);
                        Sdt dt2 = WebUtil.QuerySdt(sql_statement);

                        if (dt2.Next())
                        {
                            principal_arrear2 = dt2.GetDecimal("principal_arrear");
                            principal_payment2 = dt2.GetDecimal("principal_payment");
                            fineyear_arrear2 = dt2.GetDecimal("fineyear_arrear");
                            intyear_arrear2 = dt2.GetDecimal("intyear_arrear");
                            interest_arrear2 = dt2.GetDecimal("interest_arrear");
                            fine_arrear2 = dt2.GetDecimal("fine_arrear");
                        }
                    }
                    catch { }
           
                    principal_arrear += principal_arrear2;
                    principal_balance += principal_payment2;
                    
                   // interest_new = calinitnew2(loancontract_no, principal_balance);

                    if (loancontract_no == "158/10622")
                    {
                    
                    }

                    if (dsMain.DATA[0].adtm_sdate < state.SsWorkDate)  //ถ้าวัน ที่คำนวณดอกเบี้ยค่า แรับน้อยกว่า วัน SsWorkDate  ให้หาจาก statement 
                    {


                              interest_new = WebUtil.Calint2(loancontract_no, dsMain.DATA[0].adtm_sdate, state.SsCoopId);
                              fine_new = WebUtil.calfine_new2(loancontract_no, dsMain.DATA[0].adtm_sdate, state.SsCoopId);
                    }
                    else
                    {
                        interest_new = WebUtil.Calint(loancontract_no, dsMain.DATA[0].adtm_sdate, state.SsCoopId);
                        fine_new = WebUtil.calfine_new(loancontract_no, dsMain.DATA[0].adtm_sdate, state.SsCoopId);
                    }
                    //fine_new = calfine_new2(loancontract_no,Convert.ToDouble(principal_arrear));    
                   
                    interest_arrear += interest_arrear2;
                    fine_arrear += fine_arrear2;
                    intyear_arrear += intyear_arrear2;
                    fineyear_arrear += fineyear_arrear2;
               
                    string sql2 = "insert into report_interest_on_fines (coop_id,loancontract_no,member_no,principal_arrear,interest_new,fine_new,full_name,membgroup_code,name,lastname,interest_arrear,fineyear_arrear,principal_balance) values({0},{1},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11},{12})";
                    sql2 = WebUtil.SQLFormat(sql2, state.SsCoopId, loancontract_no, member_no, principal_arrear, (interest_new + interest_arrear), (fine_new + fine_arrear), full_name, membgroup_code, name, lastname, intyear_arrear, fineyear_arrear, principal_balance);
                    WebUtil.QuerySdt(sql2);
                    
                    //string sql2 = "insert into report_interest_on_fines (coop_id,loancontract_no,member_no,principal_arrear,interest_new,fine_new,full_name,membgroup_code,name,lastname,interest_arrear,fineyear_arrear,principal_balance) values({0},{1},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11},{12})";
                    //sql2 = WebUtil.SQLFormat(sql2, state.SsCoopId, loancontract_no, member_no, principal_arrear, (interest_new + interest_arrear), (fine_new + fine_arrear), full_name, membgroup_code, name, lastname, intyear_arrear, fineyear_arrear, principal_balance);
                    //WebUtil.QuerySdt(sql2);

                }
            }
            catch { }


           

         

            try
            {
                iReportArgument arg = new iReportArgument();
                arg.Add("coop_id", iReportArgumentType.String, state.SsCoopId);
                arg.Add("start_date", iReportArgumentType.Date, dsMain.DATA[0].adtm_sdate);
                iReportBuider report = new iReportBuider(this, arg);
                report.Retrieve();
            }
            catch (Exception ex)
            {
                LtServerMessage.Text = WebUtil.ErrorMessage(ex);
            }


        }
        public decimal calinitnew(String loancontract)// 
        {
            DateTime date_now = dsMain.DATA[0].adtm_sdate;//วันที่ทำการ
            Decimal intrate = 0;//อัตราดอกเบี้ย
            Decimal cal_int = 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, rdintdec_type = 0;
            string sql21 = @" 
                 select dayinyear,rdintdec_type from lnloanconstant where coop_id = '" + state.SsCoopControl + "'";
            sql21 = WebUtil.SQLFormat(sql21, loancontract);
            Sdt dt21 = WebUtil.QuerySdt(sql21);
            if (dt21.Next())
            {
                year = dt21.GetDecimal("dayinyear");
                rdintdec_type = dt21.GetDecimal("rdintdec_type");
            }

            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); // คำนวณวัน

                //คำนวณดอกเบี้ย กล้า
                 cal_int = (principal_balance * intrate * CalDay) / year; //   ด/บใหม่
                if (rdintdec_type == 0)//ไม่ปัด ทศนิยม
                {
                    //  Decimal calint = cal_int;
                    if (cal_int < 0)
                    {
                        cal_int = 0;
                    }
                }
                else //ปัด ทศนิยม
                {
                    int calints = Convert.ToInt32(cal_int);
                    if (calints < 0)
                    {
                        cal_int = 0;
                    }
                } 
            }
            return cal_int;
        }
        public decimal calinitnew2(String loancontract, decimal principal_balance)// 
        {
            DateTime date_now = dsMain.DATA[0].adtm_sdate;//วันที่ทำการ
            Decimal intrate = 0;//อัตราดอกเบี้ย
            Decimal cal_int = 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, rdintdec_type = 0;
            string sql21 = @" 
                 select dayinyear,rdintdec_type from lnloanconstant where coop_id = '" + state.SsCoopControl + "'";
            sql21 = WebUtil.SQLFormat(sql21, loancontract);
            Sdt dt21 = WebUtil.QuerySdt(sql21);
            if (dt21.Next())
            {
                year = dt21.GetDecimal("dayinyear");
                rdintdec_type = dt21.GetDecimal("rdintdec_type");
            }

            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");
                TimeSpan diff1 = date_now.Subtract(calint);
                Double TotalDay2 = diff1.TotalDays;
                int CalDay = Convert.ToInt32(TotalDay2); // คำนวณวัน

                //คำนวณดอกเบี้ย กล้า
                cal_int = (principal_balance * intrate * CalDay) / year; //   ด/บใหม่
                if (rdintdec_type == 0)//ไม่ปัด ทศนิยม
                {
                    //  Decimal calint = cal_int;
                    if (cal_int < 0)
                    {
                        cal_int = 0;
                    }
                }
                else //ปัด ทศนิยม
                {
                    int calints = Convert.ToInt32(cal_int);
                    if (calints < 0)
                    {
                        cal_int = 0;
                    }
                }
            }
            return cal_int;
        }


        public decimal calfine_new2(String loancontract, Double principal_arrear)// 
        {
            DateTime date_now = dsMain.DATA[0].adtm_sdate;//วันที่ทำการ

            DateTime lastcalfine_date = state.SsWorkDate;
            DateTime expireperiod_date = date_now;
       //     Double principal_arrear = 0;
            Double cal_int = 0; //ผลลัพธ์
            Double year = 0;
            Double cal_fineall = 0;
            // int calfine = 0;
            // get yaer จาก ค่าคงที่
            string sql2 = @" 
                 select dayinyear from lnloanconstant";
            sql2 = WebUtil.SQLFormat(sql2, loancontract);
            Sdt dt2 = WebUtil.QuerySdt(sql2);
            if (dt2.Next())
            {
                year = dt2.GetDouble("dayinyear");

            }
            string sql = @" 
                 select lastcalfine_date,principal_arrear,expireperiod_date,lastcalfine_date from lncontperiodpaydet 
                 where loancontract_no= {0}  and principal_arrear > 0 and expireperiod_date < {1}  order by period asc ";
            sql = WebUtil.SQLFormat(sql, loancontract, date_now);
            Sdt dt = WebUtil.QuerySdt(sql);
            while (dt.Next())
            {
                //    lastcalfine_date = dt.GetDate("lastcalfine_date"); //วันที่คิดล่าสุด
                expireperiod_date = dt.GetDate("expireperiod_date"); //วันที่ต้องจ่าย
             //   principal_arrear = dt.GetDouble("principal_arrear"); // ยอดคงเหลือ
                lastcalfine_date = dt.GetDate("lastcalfine_date");
                TimeSpan diff1 = date_now.Subtract(expireperiod_date); //ตรวจสอบ ว่า จ่ายเลยกำหนด
                Double TotalDay2 = diff1.TotalDays;

                if (TotalDay2 < 0)
                {
                    cal_int = 0;
                }
                else
                {

                    Double percen = 0.03;
                    TimeSpan diff2 = date_now.Subtract(lastcalfine_date); //ตรวจสอบ ว่า จ่ายเลยกำหนด
                    Double TotalDay3 = diff2.TotalDays;
                    int CalDay = Convert.ToInt32(TotalDay3); // คำนวณวัน
                    //Hart Code รอแก้ไข
                    String hart = "select loantype_code from lncontmaster where loancontract_no = '" + loancontract + "'";
                    Sdt sd = WebUtil.QuerySdt(hart);
                    if (sd.Next())
                    {
                        if (sd.GetString("loantype_code") == "1A" || sd.GetString("loantype_code") == "1B")
                        {
                            percen = 0;
                        }

                    }


                    cal_int = (principal_arrear * percen * CalDay) / year; //   ค่าปรับใหม่
                }
                cal_fineall += cal_int;
            }

            cal_fineall = Math.Round(cal_fineall, 2);

            double numberCheck = cal_fineall - Math.Floor(cal_fineall);
            numberCheck = Math.Round(numberCheck, 2);

            int calfine2 = Convert.ToInt32(cal_fineall);
            if (cal_fineall > calfine2) //ปัดทศนิยม // ห้ามเปลี่ยนสัด
            {
                calfine2 = calfine2 + 1;
            }
            if (calfine2 < 0)
            {
                calfine2 = 0;
            }


            return calfine2;
        }

        public  decimal calfine_new(String loancontract)// 
        {
            DateTime date_now = dsMain.DATA[0].adtm_sdate;//วันที่ทำการ

            DateTime lastcalfine_date = state.SsWorkDate;
            DateTime expireperiod_date = date_now;
            Double principal_arrear = 0;
            Double cal_int = 0; //ผลลัพธ์
            Double year = 0;
            Double cal_fineall = 0;
            // int calfine = 0;
            // get yaer จาก ค่าคงที่
            string sql2 = @" 
                 select dayinyear from lnloanconstant";
            sql2 = WebUtil.SQLFormat(sql2, loancontract);
            Sdt dt2 = WebUtil.QuerySdt(sql2);
            if (dt2.Next())
            {
                year = dt2.GetDouble("dayinyear");

            }
            string sql = @" 
                 select lastcalfine_date,principal_arrear,expireperiod_date,lastcalfine_date from lncontperiodpaydet 
                 where loancontract_no= {0}  and principal_arrear > 0 and expireperiod_date < {1}  order by period asc ";
            sql = WebUtil.SQLFormat(sql, loancontract, date_now);
            Sdt dt = WebUtil.QuerySdt(sql);
            while (dt.Next())
            {
                //    lastcalfine_date = dt.GetDate("lastcalfine_date"); //วันที่คิดล่าสุด
                expireperiod_date = dt.GetDate("expireperiod_date"); //วันที่ต้องจ่าย
                principal_arrear = dt.GetDouble("principal_arrear"); // ยอดคงเหลือ
                lastcalfine_date = dt.GetDate("lastcalfine_date");
                TimeSpan diff1 = date_now.Subtract(expireperiod_date); //ตรวจสอบ ว่า จ่ายเลยกำหนด
                Double TotalDay2 = diff1.TotalDays;

                if (TotalDay2 < 0)
                {
                    cal_int = 0;
                }
                else
                {

                    Double percen = 0.03;
                    TimeSpan diff2 = date_now.Subtract(lastcalfine_date); //ตรวจสอบ ว่า จ่ายเลยกำหนด
                    Double TotalDay3 = diff2.TotalDays;
                    int CalDay = Convert.ToInt32(TotalDay3); // คำนวณวัน
                    //Hart Code รอแก้ไข
                    String hart = "select loantype_code from lncontmaster where loancontract_no = '" + loancontract + "'";
                    Sdt sd = WebUtil.QuerySdt(hart);
                    if (sd.Next())
                    {
                        if (sd.GetString("loantype_code") == "1A" || sd.GetString("loantype_code") == "1B")
                        {
                            percen = 0;
                        }

                    }


                    cal_int = (principal_arrear * percen * CalDay) / year; //   ค่าปรับใหม่
                }
                cal_fineall += cal_int;
            }

            cal_fineall = Math.Round(cal_fineall, 2);

            double numberCheck = cal_fineall - Math.Floor(cal_fineall);
            numberCheck = Math.Round(numberCheck, 2);

            int calfine2 = Convert.ToInt32(cal_fineall);
            if (cal_fineall > calfine2) //ปัดทศนิยม // ห้ามเปลี่ยนสัด
            {
                calfine2 = calfine2 + 1;
            }
            if (calfine2 < 0)
            {
                calfine2 = 0;
            }
 

            return calfine2;
        }
        public void WebSheetLoadEnd()
        {

        }
    }
}