using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using CoreSavingLibrary;
using Sybase.DataWindow;
using System.Web.Services.Protocols;
using DataLibrary;
//////////////////////////////////////////
using System.IO;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Configuration;
using System.Data.OracleClient;
using System.Globalization;
/////////////////////////////////////////



namespace Saving.Applications.insurance
{
    public partial class w_sheet_ins_ppn_ppch_excal : PageWebSheet, WebSheet
    {


              
        #region EventWebSheet
        private void JspostNewClear()
        {
        }
        public void InitJsPostBack()
        {
                   
    
        }
        private void JsInstype_desc()
        {
            
        }
      
        public void WebSheetLoadBegin()
        {
            CultureInfo enUSCulture = CultureInfo.GetCultureInfo("en-US");
            
            if (!IsPostBack)
            {

                String yyyy = state.SsWorkDate.ToString("yyyy");
                string mm = state.SsWorkDate.ToString("MM");
                int yyyy_i = Convert.ToInt32(yyyy) + 543;
                //int yyyy_i2 = Convert.ToInt32(yyyy);
                int mm_i = Convert.ToInt32(mm);
                TextBox2.Text = yyyy_i.ToString();
                DropDownList1.SelectedValue = mm_i.ToString("00");
                
            }
            else
            {           
                
            }
        }
        public void CheckJsPostBack(string eventArg)
        {

        }
        public void SaveWebSheet()
        {
            try
            {
                ExecuteDataSource exe = new ExecuteDataSource(this);
                string strExcelConn = null;
                string FileName;
                int max_value = 0;
                string sql2 = null;

                string sql_value = @"select last_documentno as documentno_max from cmdocumentcontrol where coop_id = '" + state.SsCoopId + "' and document_code='INSCLDOCNO'";
                Sdt dt2 = WebUtil.QuerySdt(sql_value);
                if (dt2.Next())
                {
                    max_value = dt2.GetInt32("documentno_max");
                }
                if (txtInput.HasFile)
                {
                    String recv_string = TextBox2.Text + DropDownList1.SelectedValue;
                    FileInfo FileIn = new FileInfo(Server.MapPath("~/WSRPDF/") + txtInput.FileName);

                    string sqldel = ("update insgroupmaster set insmemb_status = -9 where extract (month from insreq_date) = '" + DropDownList1.SelectedValue.ToString() + "' and extract (year from insreq_date) = '" + (Convert.ToInt32(TextBox2.Text)-543).ToString() + "'");
                    Sta tadel = new Sta(state.SsConnectionString);
                    tadel.Exe(sqldel);

                    string FileExtention = System.IO.Path.GetExtension(txtInput.FileName);
                    if (FileExtention == ".xls")
                    {
                        txtInput.PostedFile.SaveAs(Server.MapPath("~/WSRPDF/") + txtInput.FileName);
                        strExcelConn = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=No'", Server.MapPath("~/WSRPDF/") + txtInput.FileName);
                        FileName = txtInput.FileName;
                    }
                    else if (FileExtention == ".xlsx")
                    {
                        txtInput.PostedFile.SaveAs(Server.MapPath("~/WSRPDF/") + txtInput.FileName);
                        strExcelConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath("~/WSRPDF/") + txtInput.FileName + ";Extended Properties='Excel 8.0;HDR=No'";
                        FileName = txtInput.FileName;
                    }
                    OleDbConnection connExcel = new OleDbConnection(strExcelConn);
                    connExcel.Open();
                    DataTable dtExcelSchema;
                    dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    connExcel.Close();
                    connExcel.Open();
                    string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
                    OleDbDataAdapter da = new OleDbDataAdapter("SELECT * From [" + SheetName + "]", strExcelConn);
                    DataTable dt = new DataTable();
                    da.Fill(dt);
                    string vdvdv = dt.Rows[1][0].ToString();
                    for (int i = 1; i <= dt.Rows.Count - 1; i++)
                    {

                        if (dt.Rows[1][0].ToString() != "")
                        {
                        max_value++;
                        string txt = "";
                        for (int j = 0; j <= dt.Columns.Count - 1; j++)
                        {
                            txt = txt + dt.Rows[i][j].ToString() + "\r";
                        }
                        sql2 = null;
                        string[] gettxt = txt.Split('\r');
                        sql2 = @"insert into insgroupmaster(
                                member_no,loancontract_no,instype_code,expense_code,insgroupdoc_no,level_code,loanrequest_docno,insgroup_id,periodpay_amt,loanreq_amt,inscost_blance,period,insperod_payment,inspayment_amt,inspayment_status,inspayment_arrear,inspayment_type,insarrear_month,insmemb_status,startsafe_date,endsafe_date,insreq_date) 
                         values ('" + WebUtil.MemberNoFormat(gettxt[0]) + "','','01','CSH','" + "RS" + max_value.ToString("00000000") + "',0,'','" + max_value.ToString() + "'," + gettxt[2] + "," + gettxt[1] + "," + gettxt[1] + ",1," + gettxt[2] + "," + gettxt[2] + ",8,0,2,0,1,to_date('" + Convert.ToDateTime(gettxt[3].ToString()).ToString("ddMM") + Convert.ToDateTime(gettxt[3].ToString()).Year + "','ddMMyyyy'),to_date('" + Convert.ToDateTime(gettxt[4].ToString()).ToString("ddMM") + Convert.ToDateTime(gettxt[4].ToString()).Year + "','ddMMyyyy'),to_date('" + state.SsWorkDate.ToString("ddMMyyyy") + "','ddMMyyyy'))";
                        WebUtil.Query(sql2);
                        }
                    }
                    string updatesql = @"update cmdocumentcontrol set last_documentno= '" + max_value.ToString() + "' where coop_id = '" + state.SsCoopId + "' and document_code='INSCLDOCNO'";
                    updatesql = WebUtil.SQLFormat(updatesql);
                    updatesql = WebUtil.SQLFormat(updatesql);
                    exe.SQL.Add(updatesql);
                    exe.Execute();
                }


                LtServerMessage.Text = WebUtil.CompleteMessage("บันทึกข้อมูลเสร็จสิ้น");

            }
            catch (Exception ex)
            {
                LtServerMessage.Text = WebUtil.ErrorMessage("บันทึกไม่สำเร็จ\n" + ex);
            }     
           
        }
        public void WebSheetLoadEnd()
        {

           
        }
        #endregion

        #region EventJsPostBack
       
        private void JsPostMemberNo()
        {

        }
        #endregion
    }
}