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;  //new common
using CoreSavingLibrary.WcfNDeposit; //new deposit
using System.Web.Services.Protocols;
using System.ServiceModel.Channels;
using System.Xml;
using System.IO;
using DataLibrary;
using System.Data.OleDb;
using OfficeOpenXml;

namespace Saving.Applications.ap_deposit
{
    public partial class w_sheet_mbclass_imptxt : PageWebSheet, WebSheet
    {
        String pbl = "dp_imptxt_reqdeposit.pbl";

        protected String postSelectType;
        protected String GetValuess;
        protected String GetDetails;
        private n_depositClient ndept; //new deposit
        private n_commonClient ncommon; // new common
        //  private DwThDate tDwMain;
        private DwThDate tDwHead;
        private String acc_id = "";


        #region EventWebSheet
        public void InitJsPostBack()
        {
             
            postSelectType = WebUtil.JsPostBack(this, "postSelectType");
            GetValuess = WebUtil.JsPostBack(this, "GetValuess");
            GetDetails = WebUtil.JsPostBack(this, "GetDetails");
        }

        public void WebSheetLoadBegin()
        {

            if (!IsPostBack)
            {
                try
                {
                    ndept = wcf.NDeposit; //new deposit
                    ncommon = wcf.NCommon; //new common
                }
                catch
                {
                    LtServerMessage.Text = WebUtil.ErrorMessage("ไม่สามารถติดต่อ WebService ได้");
                }
                
                DwList.InsertRow(0);


                DwUtil.RetrieveDataWindow(DwList, pbl, null, Convert.ToDecimal(year_no.SelectedValue));
                 
                
            }
            else
            {
                
                this.RestoreContextDw(DwList);
            }
        }

        public void CheckJsPostBack(string eventArg)
        {
            if (eventArg == "GetValuess")
            {
                //ExecuteDataSource exe = new ExecuteDataSource(this);
                //string strExcelConn = null;
                if (txtInput.FileName == null || txtInput.FileName == "")
                {
                    DwUtil.RetrieveDataWindow(DwList, pbl, null, Convert.ToDecimal(year_no.SelectedValue));
                  
                }
                else
                {

                   
                    
                    string error = " ---> ";
                    if (txtInput.HasFile)
                    {
                        if (System.IO.Path.GetExtension(txtInput.FileName) == ".xlsx")
                        {

                            ExecuteDataSource exe = new ExecuteDataSource(this);
                            string into = Server.MapPath("~/WSRPDF/") + DateTime.Now.ToString("ddMMyyyyHHmmss") + "_" + txtInput.FileName;
                            txtInput.PostedFile.SaveAs(into);
                            FileInfo excel = new FileInfo(into);
                            using (var package = new ExcelPackage(excel))
                            {
                                var workbook = package.Workbook;
                                var worksheet = workbook.Worksheets.First();
                                for (int i = 2; i <= worksheet.Dimension.End.Row; i++)
                                {
                                    try
                                    {



                                        string sql = @"  insert into mbclassimp(member_no,point,mb_class,int_rate,year_no ) values(
                                             {0},{1},{2} ,{3},{4} )";
                                        sql = WebUtil.SQLFormat(sql, worksheet.Cells[i, 1].Text.ToString().Trim(), worksheet.Cells[i, 2].Text.ToString().Trim(),
                                            worksheet.Cells[i, 3].Text.ToString().Trim(),   worksheet.Cells[i, 4].Text.ToString().Trim(), year_no.SelectedValue);
                                        WebUtil.QuerySdt(sql);

                                    }
                                    catch
                                    {
                                        error += worksheet.Cells[i, 1].Text.ToString().Trim() + ",";
                                    }
                                }
                            }
                            LtServerMessage.Text = WebUtil.CompleteMessage("บันทึกข้อมูลเสร็จสิ้น" + error);
                            DwUtil.RetrieveDataWindow(DwList, pbl, null, Convert.ToDecimal( year_no.SelectedValue));
                        }
                    }
                    else
                    {
                        LtServerMessage.Text = WebUtil.ErrorMessage("ต้องเป็น ไฟล์ .xlsx เท่านั้น");
                    }
                }
            }
            else if (eventArg == "GetDetails")
            {
                try
                {
                    //insert ลง mbmembclass คนที่ยังมีหนี้อยู่
                    Sdt d = WebUtil.QuerySdt(@" select year_no,memb_level,mbmembclass.member_no,int_rate,points,seq_no,mbmembclass.loancontract_no from mbmembclass inner join
 lncontmaster on mbmembclass.loancontract_no = lncontmaster.loancontract_no where principal_balance > 0 and year_no = "+(Convert.ToDecimal( year_no.SelectedValue)-1)+" ");
                    while (d.Next())
                    {
                        Sdt dt = WebUtil.QuerySdt(@" select * from mbclassimp   where year_no = " + year_no.SelectedValue + " and member_no = '" + d.GetString("member_no") + "' ");
                        if (dt.Next())
                        {

                            WebUtil.QuerySdt(@" insert into mbmembclass(coop_id,year_no,memb_level,member_no,int_rate,points,seq_no,loancontract_no) 
                        values('" + state.SsCoopId + "'," + year_no.SelectedValue + "," + dt.GetDecimal("mb_class") + ",'" + d.GetString("member_no") + "'," + dt.GetDecimal("int_rate") + "," + dt.GetDecimal("point") + "," + (d.GetDecimal("seq_no") + 1) + " ,'" + d.GetString("loancontract_no") + "' )");
                        }
                        dt.Clear();
                    }
                    d.Clear();
                    
                    WebUtil.QuerySdt(@"UPDATE mbmembmaster SET mblntype_level = (SELECT mbclassimp.mb_class
                     FROM mbclassimp WHERE mbclassimp.member_no = mbmembmaster.member_no and  mbclassimp.year_no = " + year_no.SelectedValue + @" )
WHERE EXISTS (SELECT mbclassimp.mb_class FROM mbclassimp WHERE mbclassimp.member_no = mbmembmaster.member_no and  mbclassimp.year_no =" + year_no.SelectedValue + @" )");

                    WebUtil.QuerySdt(@"UPDATE mbmembmaster SET incomeetc_amt = (SELECT mbclassimp.point
                     FROM mbclassimp WHERE mbclassimp.member_no = mbmembmaster.member_no and  mbclassimp.year_no = " + year_no.SelectedValue + @" )
WHERE EXISTS (SELECT mbclassimp.point FROM mbclassimp WHERE mbclassimp.member_no = mbmembmaster.member_no and  mbclassimp.year_no =" + year_no.SelectedValue + @" )");

                    WebUtil.QuerySdt(@"UPDATE mbmembmaster SET membtype_level  = mblntype_level ");

                    LtServerMessage.Text = WebUtil.CompleteMessage("ผ่านรายการสำเร็จ");
                    DwUtil.RetrieveDataWindow(DwList, pbl, null,  Convert.ToDecimal( year_no.SelectedValue));
                     
                }
                catch (Exception ex) { LtServerMessage.Text = WebUtil.ErrorMessage(ex.ToString()); }
            }
        }

        public void SaveWebSheet()
        {


        }
        public void Save()
        {
            try
            { 
            }
            catch (SoapException ex)
            {
                //LtServerMessage.Text = WebUtil.ErrorMessage(WebUtil.SoapMessage(ex));

            }

        }
        public void WebSheetLoadEnd()
        {
             
            DwList.SaveDataCache();
             
        }
        #endregion


    }
}