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.u_cri_rdate
{
    public partial class u_cri_rdate : 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()
        {
            try
            {

                DateTime adtm_start = dsMain.DATA[0].adtm_start;
                DateTime adtm_end = dsMain.DATA[0].adtm_end;
                //String as_start_type = dsMain.DATA[0].as_start_type;
                //String as_end_type = dsMain.DATA[0].as_end_type;
                String coop_id = state.SsCoopId;
                string[] loantype = dsMain.DATA[0].start_lntype.Split(',');  // Split ออกมาใส่ไว้ใน Arrai string
                List<string> last = new List<string>(loantype);      // convert เป็น list  



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


//                string sql = @"create or replace view lnloantype2 as SELECT  nvl(LNUCFLOANOBJECTIVE.LOANOBJECTIVE_DESC,'อื่นๆ')as LOANOBJECTIVE_DESC ,           LNLOANTYPE.LOANTYPE_DESC ,
//                                     sum( objective_amt + payoutclr_amt ) as sum_receive,           count( slslippayout.loancontract_no ) as count_cont,
//                                 LNLOANTYPE.LOANGROUP_CODE ,           LNLOANTYPE.LOANTYPE_CODE  ,cmcoopmaster.coop_name
//                             FROM slslippayout  left join      LNCONTMASTER on  slslippayout.LOANCONTRACT_NO = LNCONTMASTER.LOANCONTRACT_NO left join
//                                LNCONTOBJECTIVE  on    LNCONTOBJECTIVE.LOANCONTRACT_NO = LNCONTMASTER.LOANCONTRACT_NO left join
//                                     LNUCFLOANOBJECTIVE on trim(LNCONTOBJECTIVE.LOANOBJECTIVE_CODE) = trim(LNUCFLOANOBJECTIVE.LOANOBJECTIVE_CODE)   left join
//                                    LNLOANTYPE  on LNCONTMASTER.LOANTYPE_CODE = LNLOANTYPE.LOANTYPE_CODE left join   cmcoopmaster on
//                            slslippayout.coop_id = cmcoopmaster.coop_id
//                            WHERE
//                              slslippayout.coop_id = {0} and
//                            ( ( slslippayout.slip_date between {1} and  {2} ) and
//                                 ( slslippayout.slip_status = 1 ) ) and LNUCFLOANOBJECTIVE.loanobjective_code not  in  ('20')
//                            and  LNCONTMASTER.loantype_code in ($P{loantype}) GROUP BY LNLOANTYPE.LOANGROUP_CODE ,
//                                     LNLOANTYPE.LOANTYPE_CODE ,           LNUCFLOANOBJECTIVE.LOANOBJECTIVE_DESC , cmcoopmaster.coop_name ,
//                                 LNLOANTYPE.LOANTYPE_DESC  ORDER BY LNLOANTYPE.LOANGROUP_CODE          ASC,           LNLOANTYPE.LOANTYPE_CODE
//                                ASC,           LNUCFLOANOBJECTIVE.LOANOBJECTIVE_DESC          ASC,           LNLOANTYPE.LOANTYPE_DESC          ASC";



                string sql2 = @"create or replace view lnloantype3 as SELECT  MBMEMBMASTER.MEMBER_NO ,
	                              MBMEMBMASTER.MEMBGROUP_CODE ,
	                              SLSLIPPAYIN.MONEYTYPE_CODE ,
	                              SLSLIPPAYINDET.LOANCONTRACT_NO ,
	                              SLSLIPPAYINDET.PERIOD ,
	                              NVL(SLSLIPPAYINDET.PRINCIPAL_PAYAMT ,0) as PRINCIPAL_PAYAMT,
	                              NVL(SLSLIPPAYINDET.INTEREST_PAYAMT ,0) as INTEREST_PAYAMT,
	                              SLSLIPPAYIN.ENTRY_ID ,
	                              SLSLIPPAYIN.ENTRY_DATE ,
	                              SLSLIPPAYIN.SHARESTK_VALUE ,
	                              SLSLIPPAYINDET.SHRLONTYPE_CODE ,
	                              SLSLIPPAYIN.DOCUMENT_NO ,
	                              SLSLIPPAYIN.SLIPTYPE_CODE ,
	                              SLSLIPPAYIN.SLIP_DATE ,
	                              NVL(SLSLIPPAYINDET.ITEM_PAYAMT ,0) as ITEM_PAYAMT,
	                              SLSLIPPAYIN.SHARESTKBF_VALUE ,
	                              SLSLIPPAYIN.OPERATE_DATE ,
	                              SLSLIPPAYINDET.STM_ITEMTYPE ,
	                              SLSLIPPAYINDET.SLIPITEMTYPE_CODE ,
	                              SLSLIPPAYINDET.SEQ_NO ,
	                              MBUCFMEMBGROUP.MEMBGROUP_CONTROL ,
	                              MBMEMBMASTER.COOP_ID ,
	                              SLSLIPPAYINDET.SLIPITEM_DESC ,
	                              MBUCFMEMBGROUP.MEMBGROUP_DESC ,
	                              MBUCFPRENAME.PRENAME_DESC || MBMEMBMASTER.MEMB_NAME || '  ' || MBMEMBMASTER.MEMB_SURNAME as mb_name,
	                              NVL(SLSLIPPAYIN.SLIP_AMT,0) as SLIP_AMT,
	                              NVL(SLSLIPPAYINDET.INTARREAR_PAYAMT ,0) as INTARREAR_PAYAMT ,
	                              NVL(SLSLIPPAYINDET.INTEREST_PERIOD,0) as INTEREST_PERIOD,
	                              NVL(SLSLIPPAYINDET.BFINTARR_AMT ,0) as BFINTARR_AMT,
                              SLSLIPPAYINDET.FINE_PERIOD ,
                            NVL(SLSLIPPAYINDET.FINEYEAR_ARREAR,0) as FINEYEAR_ARREAR ,
                            SLSLIPPAYINDET.FINE_ARREAR ,SLSLIPPAYINDET.INYEAR_ARREAR ,
                             0 as int_compensate ,
                                        LNLOANTYPE.LOANTYPE_CODE ,
	                              LNLOANTYPE.LOANGROUP_CODE,
	                              CMCOOPMASTER.COOP_NAME
                            FROM MBMEMBMASTER ,
                                 MBUCFPRENAME ,
                                 SLSLIPPAYIN ,
                                 SLSLIPPAYINDET ,
                                 MBUCFMEMBGROUP ,
                                 LNLOANTYPE ,
                                 LNCONTMASTER,
                                 CMCOOPMASTER
                            WHERE ( SLSLIPPAYIN.PAYINSLIP_NO = SLSLIPPAYINDET.PAYINSLIP_NO ) and
                                  ( MBMEMBMASTER.MEMBER_NO = SLSLIPPAYIN.MEMBER_NO ) and
                                  ( MBMEMBMASTER.PRENAME_CODE = MBUCFPRENAME.PRENAME_CODE ) and
                                  ( MBMEMBMASTER.MEMBGROUP_CODE = MBUCFMEMBGROUP.MEMBGROUP_CODE ) and
                                  ( SLSLIPPAYIN.COOP_ID = SLSLIPPAYINDET.COOP_ID ) and
                                  ( LNLOANTYPE.LOANTYPE_CODE = LNCONTMASTER.LOANTYPE_CODE ) and
                                  ( LNCONTMASTER.LOANCONTRACT_NO = SLSLIPPAYINDET.LOANCONTRACT_NO ) and
                                  ( ( SLSLIPPAYIN.SLIP_DATE between {1} and {2}  ) And
                                  ( SLSLIPPAYIN.SLIP_STATUS = 1 ) and SLSLIPPAYINDET.SLIPITEMTYPE_CODE ='LON' and
                                  ( SLSLIPPAYIN.SLIPTYPE_CODE in ( 'PX ','CLC', 'PMF','PMP','PCF' ,'TSL') ) and
                                  ( SLSLIPPAYIN.COOP_ID = {0} and LNLOANTYPE.loantype_code in ($P{loantype})))
                            ORDER BY SLSLIPPAYIN.DOCUMENT_NO";


                sql2 = sql2.Replace("$P{loantype}", format2);
                sql2 = WebUtil.SQLFormat(sql2, state.SsCoopId, adtm_start, adtm_end);
                Sdt dtIns = WebUtil.QuerySdt(sql2);

                arg.Add("adtm_start", iReportArgumentType.Date, adtm_start);
                arg.Add("adtm_end", iReportArgumentType.Date, adtm_end);
               
                arg.Add("coop_id", iReportArgumentType.String, coop_id);
                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 void WebSheetLoadEnd()
        {

        }
    }
}