using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Sybase.DataWindow;
using DataLibrary;

namespace GcoopServiceCs
{
    public class ShrlonService
    {
        private String connectionString;

        public ShrlonService(String connectionString)
        {
            this.connectionString = connectionString;
        }

        public StructLoanRequest InitMemberNo(String pbl, String memberNo, String xmlDwMain)
        {
            Sta ta = new Sta(connectionString);
            StructLoanRequest resu = new StructLoanRequest();
            try
            {
                //d_sl_loanrequest_master
                DataStore dwMain = new DataStore(pbl, "d_sl_loanrequest_master");
                dwMain.ImportString(xmlDwMain, FileSaveAsType.Xml);
                String sql = "select * from mbmembmaster where member_no='" + memberNo + "'";
                Sdt dt = ta.Query(sql);
                if (!dt.Next()) throw new Exception("ไม่มีข้อมูลสมาชิก " + memberNo);
                dwMain.SetItemString(1, "member_name", dt.GetString("memb_name") + " " + dt.GetString("memb_surname"));
                dwMain.SetItemDateTime(1, "birth_date", dt.GetDate("birth_date"));
                resu.xmlMain = dwMain.Describe("DataWindow.Data.XML");
            }
            catch (Exception ex)
            {
                try
                {
                    ta.Close();
                }
                catch { }
                throw ex;
            }
            return resu;
        }
        public String InitReqLoanSalary(String pbl, String reqLoanNo)
        { 
            String xmlReqLoan = "";
            Sta ta = new Sta(connectionString);
            try
            {
                //สร้าง DataStore ของ Main
                DataStore dwMain = new DataStore(pbl, "d_sl_loanrequest_salary");
                //คำสั่ง SQL 
                String sql = "select * from lnreqloansalary where loanrequest_docno = '" + reqLoanNo + "'";
                //ดึงข้อมูลจาก SQL 
                Sdt dt = ta.Query(sql);
                int rowCount =  dt.GetRowCount();
                dwMain.Reset();
                //วนเพื่อกำหนดค่าให้กับ DW 
                for (int i = 1; i <= rowCount; i++)
                {
                    dt.Next();
                    dwMain.InsertRow(0);
                    try { dwMain.SetItemString(i, "loanrequest_docno", dt.GetString("loanrequest_docno")); }
                    catch { }
                    try { dwMain.SetItemDecimal(i, "seq_no", dt.GetDecimal("seq_no")); }
                    catch  { }
                    try { dwMain.SetItemDecimal(i, "netsalary_amt", dt.GetDecimal("netsalary_amt")); }
                    catch { }
                    try { dwMain.SetItemDecimal(i, "use_flag", dt.GetDecimal("use_flag")); }
                    catch  { }
                    try { dwMain.SetItemString(i, "salary_period", dt.GetString("salary_period")); }
                    catch  { }
                }
                //กำหนดเป็นรูปแบบ XML เพื่อส่งค่ากลับ
                xmlReqLoan = dwMain.Describe("DataWindow.Data.XML");
            }
            catch (Exception ex)
            {
                try
                { ta.Close();}
                catch { }
                throw ex;
            }

            return xmlReqLoan;
        }
        public String  SearchReqNo(String contractNo)
        {
            String reqNo = "";
            Sta ta = new Sta(connectionString);
            try {
                //คำสั่ง SQL 
                String sql = "select loanrequest_docno from lnreqloan where loancontract_no = '" + contractNo + "'";
                //ดึงข้อมูลจาก SQL 
                Sdt dt = ta.Query(sql);
                if (!dt.Next()) throw new Exception("ไม่มีข้อมูลเลขที่สัญญา: " + contractNo);
                reqNo = dt.GetString("loanrequest_docno");
            }
            catch (Exception ex) 
            {
                try
                { ta.Close(); }
                catch { }
                throw ex;
            }
            return reqNo;
        }
        public String SearchRcvDate(String pbl)
        {
            String xmlRcvDate = "";
            //สร้าง DataStore ของ Main
            DataStore dwMain = new DataStore(pbl, "d_sl_set_rcvdate");
            Sta ta = new Sta(connectionString);
            try 
            {
                String sql = "select * from LNUCFLOANRCVSETDATE ";
                Sdt dt = ta.Query(sql);
                if (!dt.Next()) throw new Exception("ไม่มีข้อมูลวันจ่ายเงิน กรุณาบันทึกข้อมูลใหม่");
                int rowCount = dt.GetRowCount();
                dwMain.Reset();
                //วนเพื่อกำหนดค่าให้กับ DW 
                for (int i = 1; i <= rowCount; i++)
                {
                    dwMain.InsertRow(0);
                    try { dwMain.SetItemDate(i, "work_date", dt.GetDate("work_date")); }
                    catch { }
                    try { dwMain.SetItemDecimal(i, "seq_no", dt.GetDecimal("seq_no")); }
                    catch { }
                    try { dwMain.SetItemDate(i, "pay_date", dt.GetDate("pay_date")); }
                    catch { }
                    try { dwMain.SetItemDate(i, "payfuture_date", dt.GetDate("payfuture_date")); }
                    catch { }
                    try { dwMain.SetItemString(i, "setdate_desc", dt.GetString("setdate_desc")); }
                    catch { }
                    try { dwMain.SetItemString(i, "use_flag", dt.GetString("use_flag")); }
                    catch { }
                    //xmlRcvDate = dwMain.Describe("DataWindow.Data.XML");
                    dt.Next();
                }
                //กำหนดเป็นรูปแบบ XML เพื่อส่งค่ากลับ
                xmlRcvDate = dwMain.Describe("DataWindow.Data.XML");
            }
            catch (Exception ex)
            {
                try
                { ta.Close(); }
                catch { }
                throw ex;
            }
            return xmlRcvDate;
        }
        public String SearchUseRcvDate(String pbl)
        {
            String xmlRcvDate = "";
            //สร้าง DataStore ของ Main
            DataStore dwMain = new DataStore(pbl, "d_sl_set_rcvdate");
            Sta ta = new Sta(connectionString);
            try
            {
                String sql = "select * from LNUCFLOANRCVSETDATE where use_flag = '1'";
                Sdt dt = ta.Query(sql);
                if (!dt.Next()) throw new Exception("กรุณาตรวจสอบการกำหนดค่าวันที่จ่ายเงินกู้");
                int rowCount = dt.GetRowCount();
                if (rowCount > 1) throw new Exception("มีการกำหนดวันที่จ่ายเงินกู้มากกว่า 1 ชุด กรุณาตรวจสอบการกำหนดค่า");
                dwMain.Reset();
                //วนเพื่อกำหนดค่าให้กับ DW 
                for (int i = 1; i <= rowCount; i++)
                {
                    dwMain.InsertRow(0);
                    try { dwMain.SetItemDate(i, "work_date", dt.GetDate("work_date")); }
                    catch { }
                    try { dwMain.SetItemDecimal(i, "seq_no", dt.GetDecimal("seq_no")); }
                    catch { }
                    try { dwMain.SetItemDate(i, "pay_date", dt.GetDate("pay_date")); }
                    catch { }
                    try { dwMain.SetItemDate(i, "payfuture_date", dt.GetDate("payfuture_date")); }
                    catch { }
                    try { dwMain.SetItemString(i, "setdate_desc", dt.GetString("setdate_desc")); }
                    catch { }
                    try { dwMain.SetItemString(i, "use_flag", dt.GetString("use_flag")); }
                    catch { }
                    //xmlRcvDate = dwMain.Describe("DataWindow.Data.XML");
                    dt.Next();
                }
                //กำหนดเป็นรูปแบบ XML เพื่อส่งค่ากลับ
                xmlRcvDate = dwMain.Describe("DataWindow.Data.XML");
            }
            catch (Exception ex)
            {
                try
                { ta.Close(); }
                catch { }
                throw ex;
            }
            return xmlRcvDate;
            
        }
        public void DeleteRcvDate()
        {
            Sta ta = new Sta(connectionString);
            try {
                String sqlDel = "DELETE FROM LNUCFLOANRCVSETDATE";
                ta.Exe(sqlDel);

            }
            catch (Exception ex)
            {
                try
                { ta.Close(); }
                catch { }
                throw ex;
            }

        }
        public Decimal SearchSeqNo()
        {
            Decimal seqNo = 0;
            Sta ta = new Sta(connectionString);
            try
            {
                //คำสั่ง SQL 
                String sql = "select max(seq_no ) as seq_no from LNCONTKEYHISTORY ";
                //ดึงข้อมูลจาก SQL 
                Sdt dt = ta.Query(sql);
                if (!dt.Next()) throw new Exception();
                seqNo = dt.GetDecimal("seq_no");
            }
            catch (Exception ex)
            {
                try
                { ta.Close(); }
                catch { }
                throw ex;
            }
            return seqNo;
        }
        public String SearchContractNo(String reqNo)
        {
            //ค้นหาเลขที่สัญญา จากเลขใบคำขอกู้
            String contNo = "";
            Sta ta = new Sta(connectionString);
            try
            {
                //คำสั่ง SQL 
                String sql = "select loancontract_no  from lnreqloan where loanrequest_docno ='" + reqNo + "'";
                //ดึงข้อมูลจาก SQL 
                Sdt dt = ta.Query(sql);
                if (!dt.Next()) throw new Exception("ไม่มีข้อมูลเลขที่ใบคำขอกู้: " + reqNo);
                contNo = dt.GetString("loancontract_no");
            }
            catch (Exception ex)
            {
                try
                { ta.Close(); }
                catch { }
                throw ex;
            }
            return contNo;
        }
    }
   
}