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.loan_arrear_year
{
    public partial class loan_arrear_year : 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_loanarrearyear where coop_id={0}";
            sql = WebUtil.SQLFormat(sql, state.SsCoopId);
            WebUtil.QuerySdt(sql);
        }

        private void Insert_report_interest_on_fines()
        {
            string format = "";
            try
            {
                string[] loantype = dsMain.DATA[0].start_lntype.Split(',');  // Split ออกมาใส่ไว้ใน Arrai string
                List<string> last = new List<string>(loantype);      // convert เป็น list       

               
                for (int i = 0; i < last.Count; i++)
                {
                    format += string.Format("'{0}'", last[i]);
                    if (i != (last.Count - 1))
                    {
                        format += ",";
                    }
                }
            }
            catch (Exception ex)
            {
                LtServerMessage.Text = WebUtil.ErrorMessage(ex);
            }

            string loancontract_no = "";
            string loangroup_code = "";
            string member_no = "";
            string memb_name = "";
            string memb_surname = "";
            decimal bfprincipal_arrear = 0;
            decimal bffineyear_amt = 0;
            decimal bfintyeararr_amt = 0;
            DateTime startdate = DateTime.Today;

            try
            {
                string start_date = @"select  accstart_date  from cmaccountyear where {0} between accstart_date and accend_date and coop_id={1}";
                start_date = WebUtil.SQLFormat(start_date, dsMain.DATA[0].adtm_sdate, state.SsCoopId);
                Sdt dt4 = WebUtil.QuerySdt(start_date);
                if (dt4.Next())
                {
                    startdate = dt4.GetDate("accstart_date");
                
                }
            }
            catch { }
            try
            {
                string principal_arrear = @"select lncontstatement.loancontract_no,loangroup_code,mbmembmaster.member_no,mbmembmaster.memb_name,mbmembmaster.memb_surname,bfprincipal_arrear
                                        ,bffineyear_amt,bfintyeararr_amt from
                                         lncontstatement inner join lncontmaster on lncontstatement.loancontract_no =lncontmaster.loancontract_no inner join mbmembmaster on lncontmaster.member_no
                                        = mbmembmaster.member_no  inner join  lnloantype on   lncontmaster.loantype_code =lnloantype.loantype_code
                                        where loanitemtype_code ='B/F' and slip_date = {0}  
                                          and mbmembmaster.membgroup_code between {1} and {2}  
                                         and (bfprincipal_arrear <> 0 or bffineyear_amt <> 0 or bfintyeararr_amt <> 0) and lncontmaster.loantype_code in ("+ format +") order by lncontmaster.member_no,lncontmaster.loancontract_no";

                principal_arrear = WebUtil.SQLFormat(principal_arrear, startdate, dsMain.DATA[0].membgroup_start, dsMain.DATA[0].membgroup_end);
                Sdt dt3 = WebUtil.QuerySdt(principal_arrear);
                while (dt3.Next())
                {
                    loancontract_no = dt3.GetString("loancontract_no");
                    loangroup_code = dt3.GetString("loangroup_code");
                    member_no = dt3.GetString("member_no");
                    memb_name = dt3.GetString("memb_name");
                    memb_surname = dt3.GetString("memb_surname");
                    bfprincipal_arrear = dt3.GetDecimal("bfprincipal_arrear");
                    bffineyear_amt = dt3.GetDecimal("bffineyear_amt");
                    bfintyeararr_amt = dt3.GetDecimal("bfintyeararr_amt");




                    string statement = @"select sum(principal_arrear) as principal_arrear,sum(fineyear_arrear) as fineyear_arrear,sum(intarr_payment) as intarr_payment from lncontstatement where  
                                         loancontract_no ={0} and  item_status = 1
                                         and slip_date between {1} and  
                                         {2} and loanitemtype_code ='LPX' and coop_id = {3}";
                    statement = WebUtil.SQLFormat(statement, loancontract_no, startdate, dsMain.DATA[0].adtm_sdate,state.SsCoopId);
                    Sdt dt4 = WebUtil.QuerySdt(statement);
                    if (dt4.Next())
                    {
                        bfprincipal_arrear -= dt4.GetDecimal("principal_arrear");
                        bffineyear_amt -= dt4.GetDecimal("fineyear_arrear");
                        bfintyeararr_amt -= dt4.GetDecimal("intarr_payment");
                    }


                    string insert = @"insert into report_loanarrearyear (coop_id,member_no,loancontract_no,principal_balance1,principal_balance2,principal_balance3,int_arrear1
                            ,int_arrear2,int_arrear3,fine_arrear1,fine_arrear2,fine_arrear3) values({0},{1},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11})";
                    if (loangroup_code == "01") // สั้น
                    {

                        insert = WebUtil.SQLFormat(insert, state.SsCoopId, member_no, loancontract_no, bfprincipal_arrear, 0, 0, bfintyeararr_amt, 0, 0, bffineyear_amt, 0, 0);
                        WebUtil.QuerySdt(insert);
                    }
                    else if (loangroup_code == "02") //ปานกลาง
                    {
                        insert = WebUtil.SQLFormat(insert, state.SsCoopId, member_no, loancontract_no, 0, bfprincipal_arrear, 0, 0, bfintyeararr_amt, 0, 0, bffineyear_amt, 0);
                        WebUtil.QuerySdt(insert);
                    }
                    else
                    { // ยาว

                        insert = WebUtil.SQLFormat(insert, state.SsCoopId, member_no, loancontract_no, 0, 0, bfprincipal_arrear, 0, 0, bfintyeararr_amt, 0, 0, bffineyear_amt);
                        WebUtil.QuerySdt(insert);
                    }
                }

            }
            catch { 
            
            }
        


            try
            {
                iReportArgument arg = new iReportArgument();
                arg.Add("coop_id", iReportArgumentType.String, state.SsCoopId);
                arg.Add("work_date", iReportArgumentType.Date, dsMain.DATA[0].adtm_sdate);
                arg.Add("loantype", iReportArgumentType.String, dsMain.DATA[0].start_lntype);
                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()
        {

        }
    }
}