using System;
using CoreSavingLibrary;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Xml.Linq;
using CoreSavingLibrary.WcfNCommon;
using CoreSavingLibrary.WcfNShrlon;
using Sybase.DataWindow;
using System.Globalization;
using System.Web.Services.Protocols;
using DataLibrary;

namespace Saving.Applications.shrlon
{
    public partial class ws_sl_approve_req_to_contract : PageWebSheet, WebSheet
    {
        private n_shrlonClient shrlonService;
        private DwThDate thDwMaster;


        protected String jsgenReqDocNo = "";
        protected String postmember = "";
        protected String postloantype = "";
        protected String postallflag = "";
        protected string jsPostEntryid = "";
        protected string jspostdate = "";
        protected string jsPostSetFilter = "";
        static string apvlist = "";
        #region WebSheet Members

        void WebSheet.InitJsPostBack()
        {


            jsgenReqDocNo = WebUtil.JsPostBack(this, "jsgenReqDocNo");
            postmember = WebUtil.JsPostBack(this, "postmember");
            postloantype = WebUtil.JsPostBack(this, "postloantype");
            jsPostEntryid = WebUtil.JsPostBack(this, "jsPostEntryid");
            postallflag = WebUtil.JsPostBack(this, "postallflag");
            jspostdate = WebUtil.JsPostBack(this, "jspostdate");
            jsPostSetFilter = WebUtil.JsPostBack(this, "jsPostSetFilter");
            //thDwMaster = new DwThDate(dw_master, this);
            //thDwMaster.Add("loanrequest_date", "loanrequest_tdate");
            //thDwMaster.Add("loanrcvfix_date", "loanrcvfix_tdate");
        }

        void WebSheet.WebSheetLoadBegin()
        {
            try
            {
                shrlonService = wcf.NShrlon;
            }
            catch
            {
                LtServerMessage.Text = WebUtil.ErrorMessage("ติดต่อ Web Service ไม่ได้");
                return;
            }
            this.ConnectSQLCA();

            if (IsPostBack)
            {
                if (dw_cri.RowCount > 1)
                {
                    dw_cri.DeleteRow(dw_cri.RowCount);
                }
                dw_master.RestoreContext();
                dw_cri.RestoreContext();
            }
            else
            {
                Londbegin();
                // this.InitLnReqList();
            }
            if (dw_master.RowCount < 1)
            {
                this.InitLnReqList();

            }

        }

        private void Londbegin()
        {
            dw_cri.InsertRow(0);
            dw_cri.SetItemDecimal(1, "all_flag", 1);
            DwUtil.RetrieveDDDW(dw_cri, "loantype_code", "sl_approve_loan.pbl", null);
            dw_cri.SetItemString(1, "loantype_code", "");
            dw_cri.SetItemString(1, "member_no", "");
            dw_cri.SetItemString(1, "loanreq_start", "");
            dw_cri.SetItemString(1, "loanreq_end", "");
        }

        void WebSheet.CheckJsPostBack(string eventArg)
        {
            if (eventArg == "jsgenReqDocNo")
            {
                this.GenReqDocNo();
            }
            else if (eventArg == "postmember")
            {
                this.JsPostMember();
            }
            else if (eventArg == "postloantype")
            {
                this.JsPostLoantype();
            }
            else if (eventArg == "postallflag")
            {
                this.JsPostAllflag();
            }
            else if (eventArg == "jsPostEntryid")
            {
                JsPostEntryid();
            }
            else if (eventArg == "jspostdate")
            {
                this.JsPostLoanDate();
            }
            else if (eventArg == "jsPostSetFilter")
            {
                this.PostSetFilter();
            }
        }

        private void InitLnReqList()
        {
            try
            {
                //String reqListXML = shrlonService.Initlist_lnreqapv(state.SsWsPass);
                //dw_master.Reset();
                //DwUtil.ImportData(reqListXML, dw_master, null, FileSaveAsType.Xml);
                string sql = @"  SELECT MBUCFPRENAME.PRENAME_DESC,   
         MBMEMBMASTER.MEMB_NAME,   
         MBMEMBMASTER.MEMB_SURNAME,   
         MBMEMBMASTER.MEMBGROUP_CODE,   
         0 as choose_flag,   
         LNREQLOAN.COOP_ID,   
         LNREQLOAN.LOANREQUEST_DOCNO,   
         LNREQLOAN.MEMBER_NO,   
         LNREQLOAN.LOANTYPE_CODE,   
         LNREQLOAN.LOANREQUEST_DATE,   
         LNREQLOAN.REF_REGISTERNO,   
         LNREQLOAN.LOANREQUEST_AMT,   
         LNREQLOAN.PERIOD_PAYMENT,   
         LNREQLOAN.LOANREQUEST_STATUS,   
         LNREQLOAN.LOANCONTRACT_NO,   
         LNREQLOAN.ENTRY_ID,   
         LNREQLOAN.ENTRY_BYCOOPID  
    FROM MBMEMBMASTER,   
         MBUCFPRENAME,   
         LNREQLOAN  
   WHERE ( LNREQLOAN.MEMCOOP_ID = MBMEMBMASTER.COOP_ID ) and  
         ( LNREQLOAN.MEMBER_NO = MBMEMBMASTER.MEMBER_NO ) and  
         ( MBMEMBMASTER.PRENAME_CODE = MBUCFPRENAME.PRENAME_CODE ) and  
         ( ( lnreqloan.coop_id = {0}) AND  
         ( lnreqloan.loanrequest_status = 11 ) )";
                sql = WebUtil.SQLFormat(sql, state.SsCoopId);
                DwUtil.ImportData(sql, dw_master, null);

                dw_master.SetSort(" loanrequest_docno asc , loantype_code asc ,loancontract_no asc");
                dw_master.Sort();

            }

            catch (Exception ex)
            {
                LtServerMessage.Text = WebUtil.ErrorMessage("ไม่มีข้อมูลรอทำรายการ");
            }
        }

        private void PostSetFilter()
        {
            string ls_filter = "", ls_membno = "", ls_entryid = "", ls_loantype;
            ls_membno = dw_cri.GetItemString(1, "member_no");
            try { ls_entryid = dw_cri.GetItemString(1, "entry_id"); }
            catch { ls_entryid = ""; }
            ls_loantype = dw_cri.GetItemString(1, "loantype_code");

            if (ls_entryid != "")
            {
                ls_filter += "entry_id = '" + ls_entryid + "'";
            }
            if (ls_membno != "")
            {
                ls_membno = WebUtil.MemberNoFormat(ls_membno);
                dw_cri.SetItemString(1, "member_no", ls_membno);
                if (ls_filter == "")
                {
                    ls_filter += "member_no = '" + ls_membno + "'";
                }
                else
                {
                    ls_filter += "and member_no = '" + ls_membno + "'";
                }
            }
            if (ls_loantype != "")
            {
                if (ls_filter == "")
                {
                    ls_filter += "loantype_code = '" + ls_loantype + "'";
                }
                else
                {
                    ls_filter += "and loantype_code = '" + ls_loantype + "'";
                }
            }

            dw_master.SetFilter(ls_filter);
            dw_master.Filter();
            dw_master.SetSort("loanrequesc_docno asc");
            dw_master.Sort();

        }

        private void GenReqDocNo()
        {
            //JsChklastDocno();
            int k = 0;
            int count = dw_master.RowCount;
            for (int i = 0; i < count; i++)
            {
                string req_coopid = dw_master.GetItemString(i + 1, "coop_id");

                String lncont_no = "";
                try { lncont_no = dw_master.GetItemString(i + 1, "loancontract_no"); }
                catch { lncont_no = ""; }

                String lncont_status = dw_master.GetItemString(i + 1, "loanrequest_status");
                if (lncont_status == "1")
                {
                    if (lncont_no == "")
                    {
                        String loantype_code = dw_master.GetItemString(i + 1, "loantype_code").Trim();
                        String newReqDocNo = shrlonService.of_gennewcontractno(state.SsWsPass, req_coopid, loantype_code);

                        k++;
                        if (k == 1)
                        {
                            Jschklastdocnonew(newReqDocNo, loantype_code);

                        }
                        dw_master.SetItemString(i + 1, "loancontract_no", newReqDocNo);
                        //  dw_master.SetItemDateTime(i + 1, "approve_date", state.SsWorkDate);
                    }
                }
            }

        }
        private int Jschklastdocnonew(string as_contno, string as_loantype_code)
        {

            try
            {
                string lncont_no = as_contno, last_condocno = "", ls_lastdocnonew = "";// dw_master.GetItemString(i, "loancontract_no");
                int lendocno = lncont_no.Substring(4, 6).Length;//lncont_no.Length - 4
                double lastdocno = Convert.ToDouble(lncont_no.Substring(4, Convert.ToInt16(lendocno)));
                string coop_id = state.SsCoopId;
                lastdocno--;
                string ls_lastdocno = "00000000000" + lastdocno.ToString();
                ls_lastdocnonew = ls_lastdocno.Substring(ls_lastdocno.Length - lendocno, lendocno);
                String documentcode = wcf.NBusscom.of_getattribloantype(state.SsWsPass, as_loantype_code, "document_code");
                //a.document_code,
                string ls_sql = @"select   max( a.document_code || substr(  b.loancontract_no,3,2)||' ' || substr( b.loancontract_no,5,6) ) as lastdocno
                        from lncontmaster b , lnloantype a 
                        where a.loantype_code = b.loantype_code and  a.coop_id = b.coop_id and a.coop_id = '" + coop_id + "' and a.document_code = '" + documentcode + "'";
                try
                {
                    Sdt dt2 = WebUtil.QuerySdt(ls_sql);

                    if (dt2.Next())
                    {
                        last_condocno = dt2.GetString("lastdocno");

                    }
                    // last_condocno = ls_lastdocno.Substring(ls_lastdocno.Length - lendocno, lendocno);
                    string lastcontno22 = lncont_no.Substring(0, 3) + ls_lastdocno + lncont_no.Substring(8, 2);
                    string last_condocno4 = last_condocno.Substring(last_condocno.Length - 4, 4);

                    if (Convert.ToDouble(last_condocno4) != lastdocno)
                    {
                        LtServerMessage.Text = WebUtil.WarningMessage("การอนุมัติเลขสัญญาเงินกู้ เลขสัญญา " + as_contno + " ไม่เป็นเลขต่อจากเลขที่ล่าสุดของก่อนหน้านั้น(เลขล่าสุด = " + last_condocno + " ) <br />กรุณาตรวจสอบด้วย");
                    }
                }
                catch
                {
                    LtServerMessage.Text = WebUtil.WarningMessage("การอนุมัติเลขสัญญาเงินกู้ เลขสัญญา " + as_contno + " ไม่พบเลขที่ล่าสุดของก่อนหน้านั้น <br />กรุณาตรวจสอบด้วย");
                }
            }
            catch
            {

            }
            return 1;
        }
        //เพิ่ม function
        private int JsChklastDocno()
        {
            int count = dw_master.RowCount;

            for (int i = 1; i <= count; i++)
            {

                String lncont_status = dw_master.GetItemString(i, "loanrequest_status");

                String lncont_no = "";
                try
                {
                    lncont_no = dw_master.GetItemString(i, "loancontract_no");
                }
                catch
                {
                }
                //if (lncont_status == "1" && lncont_no != "")
                //{
                //    int lendocno = lncont_no.Substring(3, 6).Length;//lncont_no.Length - 4
                //    double lastdocno = Convert.ToDouble(lncont_no.Substring(3, Convert.ToInt16(lendocno)));

                //    lastdocno--;
                //    string ls_lastdocno = "00000000000" + lastdocno.ToString();
                //    ls_lastdocno = ls_lastdocno.Substring(ls_lastdocno.Length - lendocno, lendocno);
                //    string lastcontno = lncont_no.Substring(0, 3) + ls_lastdocno + lncont_no.Substring(8, 2);

                //    string ls_sql = " select * from lncontmaster where loancontract_no = '" + lastcontno + "'";
                //    Sdt dt = WebUtil.QuerySdt(ls_sql);
                //    if (dt.GetRowCount() <= 0)
                //    {
                //        LtServerMessage.Text = WebUtil.WarningMessage("การอนุมัติเลขสัญญาเงินกู้ เลขสัญญาของคนแรก ไม่เป็นเลขต่อจากเลข่ที่ล่าสุดของก่อนหน้านั้น กรุณาตรวจสอด้วย");
                //        return 1;
                //    }
                //    else
                //    {
                //        return 1;
                //    }
                //}
            }
            return 1;
        }
        private void JsPostMember()
        {
            String member_no = WebUtil.MemberNoFormat(dw_cri.GetItemString(1, "member_no"));
            dw_master.SetFilter("member_no = '" + member_no + "'");
            dw_master.Filter();
            dw_cri.SetItemString(1, "member_no", member_no);
            dw_cri.SetItemString(1, "loantype_code", "");
            dw_cri.SetItemDecimal(1, "all_flag", 0);
        }
        private void JsPostLoanDate()
        {
            String member_no = WebUtil.MemberNoFormat(dw_cri.GetItemString(1, "member_no"));
            String loanreq_start = dw_cri.GetItemString(1, "loanreq_start");
            String loanreq_end = dw_cri.GetItemString(1, "loanreq_end");
            string[] arrloanstart = loanreq_start.Split('/');
            string[] arrloanend = loanreq_end.Split('/');
            string tmpstart = (Convert.ToDecimal(arrloanstart[2]) - 543) + arrloanstart[1] + arrloanstart[0];
            string tmpend = (Convert.ToDecimal(arrloanend[2]) - 543) + arrloanend[1] + arrloanend[0];
            string filter = "string(loanrequest_date,'yyyymmdd') >= '" + tmpstart + "'";
            filter += " AND string(loanrequest_date,'yyyymmdd') <= '" + tmpend + "'";
            dw_master.SetFilter(filter);
            dw_master.Filter();
            dw_cri.SetItemString(1, "member_no", member_no);
            dw_cri.SetItemString(1, "loantype_code", "");
            dw_cri.SetItemDecimal(1, "all_flag", 0);
            dw_cri.SetItemString(1, "loanreq_start", loanreq_start);
            dw_cri.SetItemString(1, "loanreq_end", loanreq_end);
        }
        private void JsPostEntryid()
        {
            String entry_id = dw_cri.GetItemString(1, "entry_id");
            dw_master.SetFilter("entry_id = '" + entry_id + "'");
            dw_master.Filter();
            dw_master.SetSort("loanrequesc_docno asc , loancontract_no asc ");
            dw_master.Sort();
            dw_cri.SetItemString(1, "loantype_code", "");
            dw_cri.SetItemString(1, "member_no", "");
            dw_cri.SetItemDecimal(1, "all_flag", 0);

        }
        private void JsPostLoantype()
        {
            String loantype_code = dw_cri.GetItemString(1, "loantype_code");
            dw_master.SetFilter("loantype_code = '" + loantype_code + "'");
            dw_master.Filter();
            dw_master.SetSort("loanrequesc_docno asc , loancontract_no asc ");
            dw_master.Sort();
            dw_cri.SetItemString(1, "loantype_code", loantype_code);
            dw_cri.SetItemString(1, "member_no", "");
            dw_cri.SetItemDecimal(1, "all_flag", 0);
        }
        private void JsPostAllflag()
        {
            dw_cri.SetItemDecimal(1, "all_flag", Convert.ToDecimal(HfAllFlag.Value));
            InitLnReqList();
            dw_cri.SetItemString(1, "member_no", "");
            dw_cri.SetItemString(1, "loantype_code", "");
        }

        void WebSheet.SaveWebSheet()
        {
            try
            {
                // Oh มงบอกว่าส่งหมดมันเยอะ XML คงเต็ม
                // เลย filter ให้เหลือน้อยๆก่อนแล้วส่งไป
                dw_master.SetFilter("loanrequest_status <> 8");
                dw_master.Filter();


                int count = dw_master.RowCount + 1;
                apvlist = "";

                string as_apvid = state.SsUsername;

                for (int i = 1; i < count; i++)
                {
                    Decimal choose_flag;
                    try { choose_flag = dw_master.GetItemDecimal(i, "choose_flag"); }
                    catch { choose_flag = 0; }
                    string loanrequest_docno;
                    try { loanrequest_docno = dw_master.GetItemString(i, "loanrequest_docno"); }
                    catch { loanrequest_docno = ""; }

                    if (choose_flag == 1)
                    {
                        string request_status = dw_master.GetItemString(i, "loanrequest_status");
                        if (request_status == "1" || request_status == "0")
                        {
                            try
                            {
                                // GenReqDocNo();
                                //เพิ่ม ที่ function  SaveWebSheet()  
                                //Edit By BankCM
                                if (i == 1)
                                {
                                    apvlist = "'" + dw_master.GetItemString(i, "loanrequest_docno").Trim() + "'";
                                }
                                else
                                {
                                    apvlist = apvlist + ",'" + dw_master.GetItemString(i, "loanrequest_docno").Trim() + "'";
                                }
                            }
                            catch (Exception ex)
                            {
                                LtServerMessage.Text = WebUtil.ErrorMessage(ex);
                                return;
                            }
                        }
                        //else if (request_status == "11")
                        //{
                        //    Decimal loanrequest = dw_master.GetItemDecimal(i, "loanrequest_amt");
                        //    string sql = "update lnreqloan set loanrequest_status='11',approve_id='" + state.SsUsername + "',loanapprove_amt='" + loanrequest + "'  where loanrequest_docno = '" + loanrequest_docno + "' ";
                        //    sql = WebUtil.SQLFormat(sql);
                        //    int sql_q = WebUtil.ExeSQL(sql);
                        //}
                    }
                }
                String ls_xml_main = dw_master.Describe("DataWindow.Data.XML");
                Int16 ls_xml = shrlonService.of_saveapv_lnreq(state.SsWsPass, ls_xml_main, as_apvid, state.SsCoopControl, state.SsWorkDate);

                LtServerMessage.Text = WebUtil.CompleteMessage("บันทึกสำเร็จ");
                InitLnReqList();

                // ตัด single quotes ตัวแรกและตัวสุดท้ายเพื่อส่งเป็น string argument ให้ ireport
                //apvlist = apvlist.Substring(1, apvlist.Length - 2);
            }
            catch (SoapException ex)
            {
                LtServerMessage.Text = WebUtil.ErrorMessage(WebUtil.SoapMessage(ex));
            }
        }

        void WebSheet.WebSheetLoadEnd()
        {
            if (dw_cri.RowCount > 1)
            {
                dw_cri.DeleteRow(dw_cri.RowCount);
            }
            DwUtil.RetrieveDDDW(dw_master, "loantype_code", "sl_approve_loan.pbl", null);
            dw_master.SaveDataCache();
            dw_cri.SaveDataCache();

            dw_master.PageNavigationBarSettings.Visible = (dw_master.RowCount > 20);
        }

        #endregion

        protected void PrintCont_Click(object sender, EventArgs e)
        {
            //apvlist = "'Q580001066','Q580001814','Q580001017','Q580001028'";

            if (apvlist != "")
            {
                try
                {
                    //                    string sql = @"SELECT DISTINCT mp.PRENAME_DESC||''||mb.MEMB_NAME||' '||mb.MEMB_SURNAME as fullname,
                    //trunc(months_between(sysdate,mb.BIRTH_DATE)/12) as age,
                    //         lrq.MEMBER_NO,
                    //         lrq.LOANTYPE_CODE,
                    //         lrq.LOANCREDIT_AMT,
                    //         lrq.LOANPERMISS_AMT,
                    //         lrq.LOANREQUEST_AMT,
                    //ftreadtbaht(lrq.LOANREQUEST_AMT) as thbathloan,
                    //ftreadtbaht(lrq.PERIOD_PAYMENT) as thbathpayment,
                    //         lrq.PERIOD_PAYMENT,
                    //         lrq.LOANPAYMENT_TYPE,
                    //         mb.MEMB_NAME,
                    //         mb.MEMB_SURNAME,
                    //         mb.MEMBGROUP_CODE,
                    //         mg.MEMBGROUP_DESC,
                    //         cct.COOP_NAME,
                    //         cct.MANAGER,
                    //TO_CHAR(lrq.LOANREQUEST_DATE, 'dd MON yyyy', 'NLS_CALENDAR=''THAI BUDDHA'' NLS_DATE_LANGUAGE=THAI') as datethai,
                    //         lrq.LOANREQUEST_DATE,
                    //         lrq.LOANCONTRACT_NO,
                    //         lrq.PERIOD_LASTPAYMENT,
                    //         lrq.SALARY_AMT,
                    //         lrq.SHARE_LASTPERIOD,
                    //         lrq.SHARE_PERIODVALUE,
                    //         mb.ADDR_NO,
                    //         mb.ADDR_MOO,
                    //         mb.ADDR_SOI,
                    //         mb.ADDR_VILLAGE,
                    //         mb.ADDR_ROAD,
                    //         mut.DISTRICT_DESC,
                    //         mup.PROVINCE_DESC,
                    //         mtb.TAMBOL_DESC,
                    //         mb.TAMBOL_CODE,
                    //         mb.AMPHUR_CODE,
                    //         mb.PROVINCE_CODE,
                    //         mb.ADDR_POSTCODE,
                    //mb.POSITION_DESC,
                    //NVL(mb.POSITION_DESC,'   ') as POSDESC ,
                    //TO_CHAR(lrq.LOANREQUEST_DATE, 'Month', 'NLS_CALENDAR=''THAI BUDDHA'' NLS_DATE_LANGUAGE=THAI') as monththai,
                    //         lrq.PERIOD_PAYAMT,
                    //		mb.ADDR_PHONE,
                    //		lrq.PERIOD_LASTPAYMENT,
                    //		lir.INTEREST_RATE,
                    //		loj.LOANOBJECTIVE_DESC,
                    //		 FT_CALAGEMTH(mb.BIRTH_DATE,SYSDATE) AS BIRTH_DATE
                    //    FROM LNREQLOAN lrq,
                    //         MBMEMBMASTER mb,
                    //         MBUCFMEMBGROUP mg,
                    //         MBUCFPRENAME mp,
                    //         CMCOOPCONSTANT cct,
                    //		LNLOANTYPE lt,
                    //		LNUCFLOANOBJECTIVE loj,
                    //		LNCFLOANINTRATEDET lir,
                    //         MBUCFDISTRICT mut,
                    //         MBUCFPROVINCE mup,
                    //         MBUCFTAMBOL mtb
                    //   WHERE ( mup.province_code (+) = mut.province_code) and
                    //         ( mb.amphur_code = mut.district_code (+)) and
                    //         ( trim(mb.province_code) = mut.province_code (+)) and
                    //         ( mb.tambol_code = mtb.tambol_code (+)) and
                    //         ( mb.amphur_code = mtb.district_code (+)) and
                    //         ( lrq.COOP_ID = mb.COOP_ID ) and
                    //		(lrq.LOANTYPE_CODE = lt.LOANTYPE_CODE) and
                    //		(lrq.LOANOBJECTIVE_CODE = loj.LOANOBJECTIVE_CODE) and 
                    //(lrq.APPROVE_DATE between lir.EFFECTIVE_DATE and lir.EXPIRE_DATE ) and
                    //         ( mg.COOP_ID = mb.COOP_ID ) and
                    //         ( mg.MEMBGROUP_CODE = mb.MEMBGROUP_CODE ) and
                    //         ( mp.PRENAME_CODE = mb.PRENAME_CODE ) and
                    //         ( lrq.MEMBER_NO = mb.MEMBER_NO )  and
                    //		(lir.LOANINTRATE_CODE = lt.INTTABRATE_CODE )and
                    //		(lir.LOANINTRATE_CODE = lt.INTTABRATE_CODE ) and
                    //		(lrq.LOANREQUEST_STATUS=1) and
                    //		(lrq.COOP_ID = {0}) and
                    //		( lrq.LOANREQUEST_DOCNO in (" + apvlist+@") )
                    //    ORDER By lrq.LOANREQUEST_DOCNO";
                    string sql = @"SELECT distinct mn.PRENAME_DESC||''||mb.MEMB_NAME||' '||mb.MEMB_SURNAME as fullname,
trunc(months_between(sysdate,mb.BIRTH_DATE)/12) as age,
         lr.MEMBER_NO,
         lr.LOANTYPE_CODE,
         lr.LOANCREDIT_AMT,
         lr.LOANPERMISS_AMT,
         lr.LOANREQUEST_AMT,
ftreadtbaht(lr.LOANREQUEST_AMT) as thbathloan,
ftreadtbaht(lr.PERIOD_PAYMENT) as thbathpayment,
         lr.PERIOD_PAYMENT,
         lr.LOANPAYMENT_TYPE,
         mb.MEMB_NAME,
         mb.MEMB_SURNAME,
         mb.MEMBGROUP_CODE,
         mg.MEMBGROUP_DESC,
         cc.COOP_NAME,
         cc.MANAGER,
TO_CHAR(lr.LOANREQUEST_DATE, 'dd MON yyyy', 'NLS_CALENDAR=''THAI BUDDHA'' NLS_DATE_LANGUAGE=THAI') as datethai,
         lr.LOANREQUEST_DATE,
         lr.LOANCONTRACT_NO,
         lr.PERIOD_LASTPAYMENT,
         lr.SALARY_AMT,
         lr.SHARE_LASTPERIOD,
         lr.SHARE_PERIODVALUE,
         mb.ADDR_NO,
         mb.ADDR_MOO,
         mb.ADDR_SOI,
         mb.ADDR_VILLAGE,
         mb.ADDR_ROAD,
         md.DISTRICT_DESC,
         mp.PROVINCE_DESC,
         mt.TAMBOL_DESC,
         mb.TAMBOL_CODE,
         mb.AMPHUR_CODE,
         mb.PROVINCE_CODE,
         mb.ADDR_POSTCODE,
mb.POSITION_DESC,
NVL(mb.POSITION_DESC,'   ') as POSDESC ,
TO_CHAR(lr.LOANREQUEST_DATE, 'Month', 'NLS_CALENDAR=''THAI BUDDHA'' NLS_DATE_LANGUAGE=THAI') as monththai,
         lr.PERIOD_PAYAMT,
		mb.ADDR_PHONE,
		lr.PERIOD_LASTPAYMENT,
		li.INTEREST_RATE,
		lo.LOANOBJECTIVE_DESC,
		 FT_CALAGEMTH(mb.BIRTH_DATE,SYSDATE) AS BIRTH_DATE
    FROM LNREQLOAN lr,
         MBMEMBMASTER mb,
         MBUCFMEMBGROUP mg,
         MBUCFPRENAME mn,
         CMCOOPCONSTANT cc,
		LNLOANTYPE lt,
		LNUCFLOANOBJECTIVE lo,
		LNCFLOANINTRATEDET li,
         MBUCFDISTRICT md,
         MBUCFPROVINCE mp,
         MBUCFTAMBOL mt
   WHERE ( mp.province_code (+) = md.province_code) and
         ( mb.amphur_code = md.district_code (+)) and
         ( trim(mb.province_code) = md.province_code (+)) and
         ( mb.tambol_code = mt.tambol_code (+)) and
         ( mb.amphur_code = mt.district_code (+)) and
         ( lr.COOP_ID = mb.COOP_ID ) and
		(lr.LOANTYPE_CODE = lt.LOANTYPE_CODE) and
		(lr.LOANOBJECTIVE_CODE = lo.LOANOBJECTIVE_CODE) and
         ( mg.COOP_ID = mb.COOP_ID ) and
         ( mg.MEMBGROUP_CODE = mb.MEMBGROUP_CODE ) and
         ( mn.PRENAME_CODE = mb.PRENAME_CODE ) and
         ( lr.MEMBER_NO = mb.MEMBER_NO )  and
		(li.LOANINTRATE_CODE = lt.INTTABRATE_CODE )and
		(lr.APPROVE_DATE between li.EFFECTIVE_DATE and li.EXPIRE_DATE ) and
		(lr.LOANREQUEST_STATUS=1) and
		(lr.COOP_ID = {0}) and
		( lr.LOANREQUEST_DOCNO in (" + apvlist + @")  )
    ORDER By lr.LOANREQUEST_DOCNO";

                    LtServerMessage.Text = apvlist;

                    sql = WebUtil.SQLFormat(sql, state.SsCoopId);

                    iReportArgument args = new iReportArgument(sql);
                    //args.Add("as_coop_id", iReportArgumentType.String, state.SsCoopId);
                    //args.Add("as_loanreqdocno", iReportArgumentType.String, apvlist);
                    iReportBuider report = new iReportBuider(this, "กำลังสร้างใบปะหน้าพิจารณาการขอกู้");
                    report.AddCriteria("r_ln_print_loan_req_doc_gsb", "ใบปะหน้าพิจารณาการขอกู้", ReportType.pdf, args);
                    report.AutoOpenPDF = true;
                    report.Retrieve();

                }
                catch (Exception ex)
                {
                    LtServerMessage.Text = WebUtil.ErrorMessage(ex.Message);
                }
            }
        }

        protected void PrintColl_Click(object sender, EventArgs e)
        {
            //apvlist = "'Q580000207','Q580000208'";
            if (apvlist != "")
            {
                try
                {
                    //                    string sql = @"select ft_getmbname(lr.coop_id,lr.member_no) as full_name,
                    //lr.member_no,
                    //ln.loancontract_no,
                    //ln.loanapprove_amt,
                    //ftreadtbaht(ln.loanapprove_amt) as loanapprove_tbaht,
                    //ft_getmbname(lc.coop_id,trim(lc.ref_collno)) as coll_name ,
                    //lc.ref_collno,
                    //ft_memgrp(lc.coop_id,mb.membgroup_code) as membgroup_desc,
                    //lc.collactive_amt,
                    //ftreadtbaht(lc.collactive_amt) as collactive_tbaht
                    //from
                    //lnreqloan lr,
                    //lncontmaster ln,
                    //lncontcoll  lc,
                    //mbmembmaster mb
                    //where lr.loancontract_no = ln.loancontract_no
                    //and lr.coop_id = ln.coop_id
                    //and lr.loancontract_no = lc.loancontract_no
                    //and lr.coop_id = lc.coop_id
                    //and trim(lc.ref_collno) = mb.member_no
                    //and lr.loanrequest_docno in (" + apvlist + @")
                    //and lr.coop_id={0}
                    //and lc.loancolltype_code='01'
                    //";
                    string sql = @"select ft_getmbname(lc.coop_id,lr.member_no) as full_name,
mb.member_no,
ln.startcont_date,
TO_CHAR(ln.startcont_date,'dd') as day,
TO_CHAR(ln.startcont_date,'fmMonth') as month,
TO_CHAR(ln.startcont_date,'yyyy')+543 as year,
mb.card_person,
mb.Addr_No,
mb.Addr_Moo,
mb.Addr_Soi,
mb.Addr_Village,
mb.Addr_Road,
mb.Tambol_Code,
mb.Amphur_Code,
mb.Province_Code,
mb.Addr_Postcode,
mb.Addr_Mobilephone,
ln.loancontract_no,
ln.loanapprove_amt,
ftreadtbaht(ln.loanapprove_amt) as loanapprove_tbaht,
ft_getmbname(lc.coop_id,trim(lc.ref_collno)) as coll_name ,
lc.ref_collno,
ft_memgrp(lc.coop_id,mb.membgroup_code) as membgroup_desc,
lc.collactive_amt,
ftreadtbaht(lc.collactive_amt) as collactive_tbaht,
trunc(Ft_Calage( birth_date , sysdate , 4 )) as birth,
pr.province_desc
from
lnreqloan lr,
lncontmaster ln,
lncontcoll  lc,
mbmembmaster mb,
mbucfprovince pr
where lr.loancontract_no = ln.loancontract_no
and lr.coop_id = ln.coop_id
and lr.loancontract_no = lc.loancontract_no
and '0'||mb.province_code = pr.province_code
and lr.coop_id = lc.coop_id
and trim(lc.ref_collno) = mb.member_no
and lr.loanrequest_docno in (" + apvlist + @")
and lr.coop_id = {0}
and lc.loancolltype_code='01'";
                    sql = WebUtil.SQLFormat(sql, state.SsCoopId);
                    iReportArgument args = new iReportArgument(sql);
                    //args.Add("as_coop_id", iReportArgumentType.String, state.SsCoopId);
                    //args.Add("as_loanrequest_docno", iReportArgumentType.String, apvlist);

                    iReportBuider report = new iReportBuider(this, "กำลังสร้างใบปะหน้าพิจารณาการขอกู้");
                    report.AddCriteria("r_ln_print_loan_coll_doc_gsb", "ใบปะหน้าพิจารณาการขอกู้", ReportType.pdf, args);
                    report.AutoOpenPDF = true;
                    report.Retrieve();

                }
                catch (Exception ex)
                {
                    LtServerMessage.Text = WebUtil.ErrorMessage(ex.Message);
                }
            }
        }

        protected void PrintIns_Click(object sender, EventArgs e)
        {
            if (apvlist != "")
            {
                try
                {
                    string sql = @"SELECT mp.PRENAME_DESC||''||mb.MEMB_NAME||' '|| mb.MEMB_SURNAME as fullname,
         lr.MEMBER_NO,
TO_CHAR(lr.LOANREQUEST_DATE, 'dd MON yyyy', 'NLS_CALENDAR=''THAI BUDDHA'' NLS_DATE_LANGUAGE=THAI') as datethai,
         lr.LOANTYPE_CODE,
         lr.LOANREQUEST_DOCNO,
         lr.LOANPERMISS_AMT,
         lr.LOANREQUEST_AMT,
         lr.LOANPAYMENT_TYPE,
         lr.PERIOD_PAYMENT,
         mb.MEMB_NAME,
         mb.MEMB_SURNAME,
         mb.MEMBGROUP_CODE,
         mg.MEMBGROUP_DESC,
         cc.COOP_NAME,
         cc.MANAGER,
         lr.LOANREQUEST_DATE,
         lr.LOANCONTRACT_NO,
         mb.MEMBER_NO,
         mb.SALARY_ID,
         mb.POSITION_DESC,
TO_CHAR(lr.APPROVE_DATE, 'dd MON yyyy', 'NLS_CALENDAR=''THAI BUDDHA'' NLS_DATE_LANGUAGE=THAI') as dateAPP,
         lr.APPROVE_DATE,
         lr.LOANAPPROVE_AMT,
ftreadtbaht( lr.LOANAPPROVE_AMT) as LOANAPPROVE_TBAHT
    FROM LNREQLOAN lr,
         MBMEMBMASTER mb,
         MBUCFMEMBGROUP mg,
         MBUCFPRENAME mp,
         CMCOOPCONSTANT cc,
         LNLOANTYPE lt,
         LNUCFLOANOBJECTIVE lo,
         LNCFLOANINTRATEDET li
   WHERE ( lr.COOP_ID = mb.COOP_ID ) and
         ( lr.LOANTYPE_CODE = lt.LOANTYPE_CODE ) and
         ( lr.LOANOBJECTIVE_CODE = lo.LOANOBJECTIVE_CODE ) and
         ( mg.COOP_ID = mb.COOP_ID ) and
         ( mg.MEMBGROUP_CODE = mb.MEMBGROUP_CODE ) and
         ( mp.PRENAME_CODE = mb.PRENAME_CODE ) and
         ( lr.MEMBER_NO = mb.MEMBER_NO ) and
         ( li.LOANINTRATE_CODE = lt.INTTABRATE_CODE ) and
	(lr.APPROVE_DATE between li.EFFECTIVE_DATE and li.EXPIRE_DATE) and
	(lo.LOANTYPE_CODE  =lr.LOANTYPE_CODE ) and
         ( lr.COOP_ID = {0}) AND
         ( lr.LOANREQUEST_DOCNO in (" + apvlist + @"))
    ORDER By lr.LOANREQUEST_DOCNO";

                    sql = WebUtil.SQLFormat(sql, state.SsCoopId);
                    iReportArgument args = new iReportArgument(sql);
                    //args.Add("as_coop_id", iReportArgumentType.String, state.SsCoopId);
                    //args.Add("as_loanrequest_docno", iReportArgumentType.String, apvlist);

                    iReportBuider report = new iReportBuider(this, "");
                    report.AddCriteria("r_ln_print_loan_ins_doc_gsb", "ใบปะหน้าพิจารณาการขอกู้", ReportType.pdf, args);
                    report.AutoOpenPDF = true;
                    report.Retrieve();

                }
                catch (Exception ex)
                {
                    LtServerMessage.Text = WebUtil.ErrorMessage(ex.Message);
                }
            }
        }
    }
}