using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using CoreSavingLibrary;
using DataLibrary;
//////////////////////////////////////////
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Configuration;
using System.Data.OracleClient;
using System.Globalization;
/////////////////////////////////////////
using System.IO;
using System.Text;
using OfficeOpenXml;
/////////////////////////////////////////


namespace Saving.Applications.divavg.ws_yrreq_excal_ctrl
{
    public partial class ws_yrreq_excal : PageWebSheet, WebSheet
    {
        String pbl = "dp_imptxt_reqdeposit.pbl";
        [JsPostBack]
        public string GetValues { get; set; }
        [JsPostBack]
        public string GetDetails { get; set; }
         [JsPostBack]
        public string ChangeAcc { get; set; }
         [JsPostBack]
         public string DeleteRowDetail { get; set; }

        [JsPostBack]
         public string JsAddDetail { get; set; }
        
         [JsPostBack]
        public string ChangeMemno { get; set; }
        

        [JsPostBack]
        public string PostMemberNo { get; set; }


        [JsPostBack]
        public string PostRecive { get; set; }


         [JsPostBack]
        public string PostReciveWindown { get; set; }
        

        public void InitJsPostBack()
        {
            dsMain.InitStatement(this);
            
        }

        public void WebSheetLoadBegin()
        {
            if (!IsPostBack)
            {

                getYear();


                //dsMain.
                dsMain.DATA[0].entry_date = state.SsWorkDate; 
                DwList.InsertRow(0);



                DwUtil.RetrieveDataWindow(DwList, pbl, null, dsMain.DATA[0].entry_date, dsMain.DATA[0].tofrom_accid.Trim(), state.SsCoopId, dsMain.DATA[0].entry_item);
            }
            else
            {
                 
                this.RestoreContextDw(DwList);
            }


        }
        public void getYear() {

            string sql = @"select current_year from yrcfconstant";
            sql = WebUtil.SQLFormat(sql);
            Sdt dtmb = WebUtil.QuerySdt(sql);
            if (dtmb.Next())
            {
                dsMain.DATA[0].tofrom_accid = dtmb.GetString("current_year");
        
            }
        }


        
        public void CheckJsPostBack(string eventArg)
        {

            if (eventArg == GetValues)
            {
                if (txtInput.FileName == null || txtInput.FileName == "")
                {
                    DwUtil.RetrieveDataWindow(DwList, pbl, null, dsMain.DATA[0].entry_date, dsMain.DATA[0].tofrom_accid.Trim(), state.SsCoopId,dsMain.DATA[0].entry_item);
                    
                     
                }
                else
                {

                    string delete = @"delete  from  DIVAVGADVANCEDW where  entry_date = {0} and  year ={1} and type2={2}";  //ลบ ก่อน insert
                    delete = WebUtil.SQLFormat(delete, dsMain.DATA[0].entry_date, dsMain.DATA[0].tofrom_accid, dsMain.DATA[0].entry_item);
                    WebUtil.QuerySdt(delete);


                    string tofrom_accid = "", cashtype_code = "TRN", itemtype_code = "";
                  
                    tofrom_accid = dsMain.DATA[0].tofrom_accid;
                  
                    itemtype_code = dsMain.DATA[0].recvtype_id;
                    if (itemtype_code == "")
                    {
                        itemtype_code = "DTS";
                    }
                    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
                                    {
                                        Int32 seq = 0;
                                        Sdt d = WebUtil.QuerySdt("select max(seq_no) as seq_no from DIVAVGADVANCEDW where member_no = '" + worksheet.Cells[i, 1].Text.ToString().Trim() + "'");
                                        if (d.Next())
                                        {
                                            seq = d.GetInt32("seq_no") + 1;
                                        }


                                        string fullname = "";
                                        string sqlmbmembmaster = @"select member_no,(memb_name ||'  ' || memb_surname)  as  fullname from mbmembmaster where  member_no={0}";
                                        sqlmbmembmaster = WebUtil.SQLFormat(sqlmbmembmaster, worksheet.Cells[i, 1].Text.ToString().Trim());
                                        Sdt dtmb = WebUtil.QuerySdt(sqlmbmembmaster);
                                        if (dtmb.Next())
                                        {
                                            fullname = dtmb.GetString("fullname");
                                        
                                        }

                                        if (worksheet.Cells[i, 1].Text.ToString().Trim() != "" && Convert.ToInt32(worksheet.Cells[i, 2].Text.ToString().Trim()) > 0 && dsMain.DATA[0].entry_item != "")
                                        {

                                        

                                            string sql = @"  insert into DIVAVGADVANCEDW (coop_id,member_no,amount,entry_date,entry_id,full_name,seq_no,year,item_status,type2) values(
                                                 {0},{1},{2} ,{3},{4}
                                                ,{5},{6},{7},{8},{9})";
                                            sql = WebUtil.SQLFormat(sql, state.SsCoopId, worksheet.Cells[i, 1].Text.ToString().Trim(), worksheet.Cells[i, 2].Text.ToString().Trim(), state.SsWorkDate, state.SsUsername, fullname.Trim(), seq, dsMain.DATA[0].tofrom_accid, "0", dsMain.DATA[0].entry_item);
                                            WebUtil.QuerySdt(sql);

                                        }

                                    }
                                    catch
                                    {
                                        error += worksheet.Cells[i, 1].Text.ToString().Trim() + ",";
                                    }
                                }
                            }
                            LtServerMessage.Text = WebUtil.CompleteMessage("บันทึกข้อมูลเสร็จสิ้น" + error);
                            DwUtil.RetrieveDataWindow(DwList, pbl, null, dsMain.DATA[0].entry_date, dsMain.DATA[0].tofrom_accid.Trim(), state.SsCoopId,dsMain.DATA[0].entry_item);
                         
                        }
                    }
                    else
                    {
                        LtServerMessage.Text = WebUtil.ErrorMessage("ต้องเป็น ไฟล์ .xlsx เท่านั้น");
                    }
                }
            }
            else if (eventArg == "JsAddDetail")
            {
                try
                {
                    DwList.InsertRow(Convert.ToInt32(rowcount.Value) + 1);
                }
                catch { }
            
            }
            else if (eventArg == "GetDetails")
            {
                try
                {


                    string li_waydept = dsMain.DATA[0].entry_item;


                    if (li_waydept=="")
                    {

                        LtServerMessage.Text = WebUtil.CompleteMessage("ผ่านรายการไม่สำเร็จ โปรดเลือกประเภทที่จะผ่าน รายการ");
                        return;
                    }

                    for (int i = 1; i <= DwList.RowCount; i++)
                    {

                        if (li_waydept == "1" && DwList.GetItemString(i, "item_status")=="1") // คืนซื้อ
                        {
                            string sql = @"update yrdivmaster set  shop1_amt={0} where   member_no={1} and div_year={2}";
                            sql = WebUtil.SQLFormat(sql, DwList.GetItemDecimal(i, "amount"), DwList.GetItemString(i, "member_no"), dsMain.DATA[0].tofrom_accid);
                            WebUtil.QuerySdt(sql);

                        }
                        else if (li_waydept == "2" && DwList.GetItemString(i, "item_status") == "1")// คืนน้ำมัน
                        {

                            string sql = @"update yrdivmaster set  oil_amt ={0} where   member_no={1} and div_year={2}";
                            sql = WebUtil.SQLFormat(sql, DwList.GetItemDecimal(i, "amount"), DwList.GetItemString(i, "member_no"), dsMain.DATA[0].tofrom_accid);
                            WebUtil.QuerySdt(sql);
                        }
                        else if (li_waydept == "3" && DwList.GetItemString(i, "item_status") == "1") // คืนข้าว
                        {
                            string sql = @"update yrdivmaster set  rice_amt ={0} where   member_no={1} and div_year={2}";
                            sql = WebUtil.SQLFormat(sql, DwList.GetItemDecimal(i, "amount"), DwList.GetItemString(i, "member_no"), dsMain.DATA[0].tofrom_accid);
                            WebUtil.QuerySdt(sql);
                        }
                        string sql2 = @"update DIVAVGADVANCEDW set  item_status=1 where   member_no={0} and entry_date={1} and type2 = {2}  and year = {3}";
                        sql2 = WebUtil.SQLFormat(sql2, DwList.GetItemString(i, "member_no"), dsMain.DATA[0].entry_date, li_waydept, dsMain.DATA[0].tofrom_accid);
                        WebUtil.QuerySdt(sql2);

                    }


                    LtServerMessage.Text = WebUtil.CompleteMessage("ผ่านรายการสำเร็จ");

                }
                catch (Exception ex) { LtServerMessage.Text = WebUtil.ErrorMessage(ex.ToString()); }
            }
            else if (eventArg == ChangeAcc)
            {
                ChangFnACC();
            }
            else if (eventArg == DeleteRowDetail)
            {
                int i = Convert.ToInt32(HdCheck.Value);
                try
                {
                    string acc = DwList.GetItemString(i, "member_no");
                    string seq = DwList.GetItemString(i, "seq_no");
                    WebUtil.QuerySdt("delete from DIVAVGADVANCEDW where member_no = '" + acc + "' and seq_no =" + seq + "");

                    DwUtil.RetrieveDataWindow(DwList, pbl, null, dsMain.DATA[0].entry_date, dsMain.DATA[0].tofrom_accid.Trim(), state.SsCoopId,dsMain.DATA[0].entry_item);
                                  
                }catch(Exception e){

                }
            }
            else if (eventArg == ChangeMemno)
            {
                string fullname = "";
                string sqlmbmembmaster = @"select member_no,(memb_name ||'  ' || memb_surname)  as  fullname from mbmembmaster where  member_no={0}";
                sqlmbmembmaster = WebUtil.SQLFormat(sqlmbmembmaster, memno.Value.ToString());
                Sdt dt= WebUtil.QuerySdt(sqlmbmembmaster);

                if(dt.Next()){
                    fullname = dt.GetString("fullname");
                }

                Int32 row = Convert.ToInt32(HdCheck.Value.ToString());

                DwList.SetItemString(row, "member_no", memno.Value.ToString());
                DwList.SetItemString(row, "full_name", fullname);
            }
            else if (eventArg == PostMemberNo)
            {

                Int32 row = Convert.ToInt32(HdCheck.Value.ToString());
                string member_no = Getmemno.Value.ToString(); 
                string mem_no = WebUtil.MemberNoFormat(member_no);
              


                string fullname = "";
                string sqlmbmembmaster = @"select member_no,(memb_name ||'  ' || memb_surname)  as  fullname from mbmembmaster where  member_no={0}";
                sqlmbmembmaster = WebUtil.SQLFormat(sqlmbmembmaster, mem_no);
                Sdt dtmb = WebUtil.QuerySdt(sqlmbmembmaster);
                if (dtmb.Next())
                {
                    fullname = dtmb.GetString("fullname");

                }

                DwList.SetItemString(row, "member_no", mem_no);
                DwList.SetItemString(row, "full_name", fullname);

            }
            else if (eventArg == PostRecive)
            {

                DwList.Reset();
                DwUtil.RetrieveDataWindow(DwList, pbl, null, dsMain.DATA[0].entry_date, dsMain.DATA[0].tofrom_accid.Trim(), state.SsCoopId, dsMain.DATA[0].entry_item);
            }
            else if (eventArg ==  PostReciveWindown)
            {


                DwList.Reset();
                DwUtil.RetrieveDataWindow(DwList, pbl, null, dsMain.DATA[0].entry_date, dsMain.DATA[0].tofrom_accid.Trim(), state.SsCoopId, dsMain.DATA[0].entry_item);
                
            
            }

            
        }

        public DataTable getMember()
        {

            Int32 row = Convert.ToInt32(HdCheck.Value.ToString());
            string memberno = DwList.GetItemString(row, "member_no");
            string sql = "select  member_no from mbmembmaster where member_no Like '%" + memberno + "'";
            DataTable dt = WebUtil.Query(sql);
            return dt;
        }

        private void ChangFnACC()
        {
            int i = Convert.ToInt32(HdCheck.Value);
            string dept = acc_no.Value;// DwList.GetItemString(i, "deptaccount_no");
            Sdt d = WebUtil.QuerySdt("select * from  dpdeptmaster where  deptaccount_no like '%" + dept + "'");
            if (d.Next())
            {
                DwList.SetItemString(i, "deptaccount_no", d.GetString("deptaccount_no"));
                DwList.SetItemString(i, "deptaccount_name", d.GetString("deptaccount_name"));
            }
        }

        public void SaveWebSheet()
        { 
                             try
                             {


                                 string delete = @"delete  from DIVAVGADVANCEDW where year = {0} and entry_date= {1} and coop_id = {2}   and  type2 = {3}";
                                 delete = WebUtil.SQLFormat(delete, dsMain.DATA[0].tofrom_accid.Trim(), dsMain.DATA[0].entry_date, state.SsCoopId, dsMain.DATA[0].entry_item);
                                 WebUtil.QuerySdt(delete);


                                for (int i = 1; i <= DwList.RowCount;   i++)
                                {

                                    Int32 seq = 0;
                                    Sdt d = WebUtil.QuerySdt("select max(seq_no) as seq_no from DIVAVGADVANCEDW where member_no = '" + DwList.GetItemString(i, "member_no").Trim() + "'");
                                    if (d.Next())
                                    {
                                        seq = d.GetInt32("seq_no") + 1;
                                    }


                                    string mem = DwList.GetItemString(i, "member_no");

                                    string sql = @"  insert into DIVAVGADVANCEDW (coop_id,member_no,amount,entry_date,entry_id,full_name,seq_no,year,item_status,type2) values(
                                                 {0},{1},{2} ,{3},{4}
                                                ,{5},{6},{7},{8},{9} )";
                                    sql = WebUtil.SQLFormat(sql, state.SsCoopId, DwList.GetItemString(i, "member_no"), DwList.GetItemDecimal(i, "amount"), state.SsWorkDate, state.SsUsername, DwList.GetItemString(i, "full_name").Trim(), seq, dsMain.DATA[0].tofrom_accid, DwList.GetItemString(i, "item_status"), dsMain.DATA[0].entry_item);
                                    WebUtil.QuerySdt(sql);

                                }
                              
                             
                            LtServerMessage.Text = WebUtil.CompleteMessage("บันทึกข้อมูลเสร็จสิ้น" );
                            DwList.Reset();
                            DwUtil.RetrieveDataWindow(DwList, pbl, null, dsMain.DATA[0].entry_date, dsMain.DATA[0].tofrom_accid.Trim(), state.SsCoopId,dsMain.DATA[0].entry_item);
                            }catch
                            {
                                        
                            }
                       


        }

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