using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using CoreSavingLibrary;
using System.Data;
using DataLibrary;

namespace Saving.Applications.trading.dlg.w_dlg_regis_ctrl
{
    public partial class DsMain : DataSourceFormView
    {
        public DataSet1.STDEBTMASTERDataTable DATA { get; set; }
        public void InitdsMain(PageWeb pw)
        {
            css1.Visible = false;
            css2.Visible = false;
            DataSet1 ds = new DataSet1();
            this.DATA = ds.STDEBTMASTER;
            this.EventItemChanged = "OnDsMainChanged";
            this.EventClicked = "OnDsMainClicked";
            this.InitDataSource(pw, FormView1, this.DATA, "dsMain");
            this.Button.Add("b_save");
            this.Register();
        }

        public void RetrieveMain(string ls_debt_no, string ls_debtfromtypecode, string ls_debttypecode)
        {
            if (Sta.IS_MYSQL_MODE)
            {
                string sql = @"
                           SELECT    STDEBTMASTER.DEBT_NO,   
                                     STDEBTMASTER.COOP_ID,   
                                     trim(STDEBTMASTER.DEBTTYPE_CODE) DEBTTYPE_CODE,                                       
                                    case when trim(STDEBTMASTER.DEBTTYPE_CODE) = '002' 
                                    then 'ลูกหนี้' 
                                    else 'เจ้าหนี้' 
                                    end as debttypecode,    
                                     STDEBTMASTER.DEBT_NAME,   
                                     STDEBTMASTER.REFDEBT_NO,   
                                     STDEBTMASTER.DEBT_AMT,   
                                     STDEBTMASTER.CREDIT_AMT,   
                                     STDEBTMASTER.ENTRY_ID,   
                                       
                                     STDEBTMASTER.CREDITBALANCE_AMT,   
                                     STDEBTMASTER.DEBT_ADDR,   
                                     STDEBTMASTER.DEBT_TUMBON,   
                                     STDEBTMASTER.DEBT_AMPHUR,   
                                     STDEBTMASTER.DEBT_PROVINCE,   
                                     STDEBTMASTER.DEBT_POSTCODE,   
                                     STDEBTMASTER.DEBT_PHONE,   
                                     STDEBTMASTER.DEBT_TAX_NO,   
                                     STDEBTMASTER.DEBT_STATUS,   
                                     STDEBTMASTER.BFDEBT_AMT,   
                                     STDEBTMASTER.STORE_ID,   
                                     STDEBTMASTER.BF_FEEAMT  
                            FROM     STDEBTMASTER 
                            WHERE  ( STDEBTMASTER.DEBT_NO = {0} ) AND  
                                   ( STDEBTMASTER.coop_id = {1} ) AND  
                                   ( STDEBTMASTER.DEBTFROMTYPE_CODE = {2} ) AND  
                                   ( STDEBTMASTER.DEBTTYPE_CODE = {3} )  ";
                sql = WebUtil.SQLFormat(sql, ls_debt_no, state.SsCoopId, ls_debtfromtypecode, ls_debttypecode);
                DataTable dt = WebUtil.Query(sql);
                this.ImportData(dt);
            }
            else
            {
                string sql = @"
                           SELECT    STDEBTMASTER.DEBT_NO,   
                                     STDEBTMASTER.COOP_ID,   
                                     trim(STDEBTMASTER.DEBTTYPE_CODE) DEBTTYPE_CODE,                                      
                                    case when trim(STDEBTMASTER.DEBTTYPE_CODE) = '002' 
                                    then 'ลูกหนี้' 
                                    else 'เจ้าหนี้' 
                                    end as debttypecode,    
                                     STDEBTMASTER.DEBT_NAME,   
                                     STDEBTMASTER.REFDEBT_NO,   
                                     STDEBTMASTER.DEBT_AMT,   
                                     STDEBTMASTER.CREDIT_AMT,   
                                     STDEBTMASTER.ENTRY_ID,   
                                     STDEBTMASTER.ENTRY_DATE,   
                                     STDEBTMASTER.CREDITBALANCE_AMT,   
                                     STDEBTMASTER.DEBT_ADDR,   
                                     STDEBTMASTER.DEBT_TUMBON,   
                                     STDEBTMASTER.DEBT_AMPHUR,   
                                     STDEBTMASTER.DEBT_PROVINCE,   
                                     STDEBTMASTER.DEBT_POSTCODE,   
                                     STDEBTMASTER.DEBT_PHONE,   
                                     STDEBTMASTER.DEBT_TAX_NO,   
                                     STDEBTMASTER.DEBT_STATUS,   
                                     STDEBTMASTER.BFDEBT_AMT,   
                                     STDEBTMASTER.STORE_ID,   
                                     STDEBTMASTER.BF_FEEAMT  
                            FROM     STDEBTMASTER 
                            WHERE  ( STDEBTMASTER.DEBT_NO = {0} ) AND  
                                   ( STDEBTMASTER.coop_id = {1} ) AND  
                                   ( STDEBTMASTER.DEBTFROMTYPE_CODE = {2} ) AND  
                                   ( STDEBTMASTER.DEBTTYPE_CODE = {3} )  ";
                sql = WebUtil.SQLFormat(sql, ls_debt_no, state.SsCoopId, ls_debtfromtypecode, ls_debttypecode);
                DataTable dt = WebUtil.Query(sql);
                this.ImportData(dt);
            }
        }


        public void DdDebtfromtype()
        {
            string sql = @"select debtfromtype_code,debtfromtype_desc  from stucfdebtfromtype where coop_id='" + state.SsCoopId + "' and item_status='1' order by debtfromtype_code";
            DataTable dt = WebUtil.Query(sql);
            this.DropDownDataBind(dt, "debtfromtype_code", "debtfromtype_desc", "debtfromtype_code");
        }
        public void Ddregis()
        {

            string sql = @"select debttype_code,debttype_desc from stucfdebttype where debttype_code != '000'  order by debttype_code,debttype_desc";
                DataTable dt = WebUtil.Query(sql);
                this.DropDownDataBind(dt, "debttypecode", "debttype_desc", "debttype_code");   
        }
       /* public void DdregisRE(string debbttypecode)
        {

            string sql = @"SELECT trim(debttype_code) debttype_code,debttype_desc debttype_desc, 1 as sorter
                            FROM stucfdebttype WHERE COOP_ID ={0} and trim(debttype_code) = {1}
                            union select '','-- เลือก --',0 from stucfdebttype order by sorter, debttype_code";
            sql = WebUtil.SQLFormat(sql, state.SsCoopControl, debbttypecode);
            DataTable dt = WebUtil.Query(sql);
            this.DropDownDataBind(dt, "debttypecode", "debttype_desc", "debttype_code");
        }*/
       
        public void DdDebtProvince()
        {
            if (Sta.IS_MYSQL_MODE)
            {
                string sql = @"select province_code,province_desc from mbucfprovince 
union select  '000' as province_code,'กรุณาเลือก' as province_desc order by province_code,province_desc";
                DataTable dt = WebUtil.Query(sql);
                this.DropDownDataBind(dt, "debt_province", "province_desc", "province_code");
            }
            else
            {
                string sql = @"select  '000' as province_code,'กรุณาเลือก' as province_desc from  dual union
select province_code,province_desc from mbucfprovince order by province_code,province_desc";
                DataTable dt = WebUtil.Query(sql);
                this.DropDownDataBind(dt, "debt_province", "province_desc", "province_code");
            }
        }


        public void DdDebtDistrict(string province_code)
        {
            if (Sta.IS_MYSQL_MODE)
            {
                string sql = @"select district_code,district_desc from mbucfdistrict where  province_code={0} 
union select  '0000' as district_code,'กรุณาเลือก' as district_desc 
order by district_code";
                sql = WebUtil.SQLFormat(sql, province_code);
                DataTable dt = WebUtil.Query(sql);
                this.DropDownDataBind(dt, "debt_amphur", "district_desc", "district_code");

            }
            else
            {
                string sql = @"
            select  '0000' as district_code,'กรุณาเลือก' as district_desc from  dual union
            select district_code,district_desc from mbucfdistrict where  province_code={0} order by district_code";
                sql = WebUtil.SQLFormat(sql, province_code);
                DataTable dt = WebUtil.Query(sql);
                this.DropDownDataBind(dt, "debt_amphur", "district_desc", "district_code");
            }
        }


        public void DdDebtTumbol(string debt_amphur)
        {
            if (Sta.IS_MYSQL_MODE)
            {
                string sql = @"
select tambol_code,tambol_desc from mbucftambol  where district_code = {0}
union select  '000000' as tambol_code,'กรุณาเลือก' as tambol_desc  
 order by tambol_code";
                sql = WebUtil.SQLFormat(sql, debt_amphur);
                DataTable dt = WebUtil.Query(sql);
                this.DropDownDataBind(dt, "debt_tumbon", "tambol_desc", "tambol_code");
            }
            else
            {
                string sql = @"
            select  '000000' as tambol_code,'กรุณาเลือก' as tambol_desc from  dual union
            select tambol_code,tambol_desc from mbucftambol  where district_code = {0} order by tambol_code";
                sql = WebUtil.SQLFormat(sql, debt_amphur);
                DataTable dt = WebUtil.Query(sql);
                this.DropDownDataBind(dt, "debt_tumbon", "tambol_desc", "tambol_code");
            }
        }


        public string DdDebtPostCode(string debt_amphur)
        {
            string postcode = string.Empty;
            string sql = @"select postcode from mbucfdistrict where  district_code={0} ";
            sql = WebUtil.SQLFormat(sql, debt_amphur);
            Sdt dt = WebUtil.QuerySdt(sql);
            if (dt.Next())
            {
                postcode = dt.GetString("postcode");
            }
            return postcode;
        }




        public void DdDebtProvinceRetive(string debt_province)
        {
            string sql = string.Empty;
            if (debt_province == string.Empty)
            {
                if (Sta.IS_MYSQL_MODE)
                {
                    sql = @"
select province_code,province_desc from mbucfprovince 
union select '000' as province_code,'กรุณาเลือก' as province_desc  
order by province_code,province_desc";
                }
                else
                {
                    sql = @"
select '000' as province_code,'กรุณาเลือก' as province_desc from dual union
select province_code,province_desc from mbucfprovince order by province_code,province_desc";
                }
            }
            else
            {
                sql = @"select   province_code,province_desc from  mbucfprovince where  province_code ={0} union
select province_code,province_desc from mbucfprovince order by province_code,province_desc";
            }
            sql = WebUtil.SQLFormat(sql, debt_province);
            DataTable dt = WebUtil.Query(sql);
            this.DropDownDataBind(dt, "debt_province", "province_desc", "province_code");
        }

        public void DdDebtDistrictRetive(string province_code, string district_code)
        {
            string sql = string.Empty;
            if (province_code == string.Empty && district_code == string.Empty)
            {
                sql = @"
            select  '0000' as district_code,'กรุณาเลือก' as district_desc from dual union
            select district_code,district_desc from mbucfdistrict  order by district_code";
            }
            else if (province_code != string.Empty && district_code == string.Empty)
            {
                sql = @" 
        select  '0000' as district_code,'กรุณาเลือก' as district_desc from dual union
        select district_code,district_desc from mbucfdistrict where  province_code={0} order by district_code";
            }
            else if (province_code != string.Empty && district_code != string.Empty)
            {
                sql = @"
            select  district_code,district_desc from mbucfdistrict where  province_code ={0} and  district_code = {1} union
            select district_code,district_desc from mbucfdistrict where  province_code={0} order by district_code";
            }

            sql = WebUtil.SQLFormat(sql, province_code, district_code);
            DataTable dt = WebUtil.Query(sql);
            this.DropDownDataBind(dt, "debt_amphur", "district_desc", "district_code");
        }

        public void DdDebtTambolRetive(string district_code, string tambol_code)
        {
            string sql = string.Empty;
            if (district_code == string.Empty && tambol_code == string.Empty)
            {
                sql = @"select  '000000' as tambol_code,'กรุณาเลือก' as tambol_desc from  dual union
            select tambol_code,tambol_desc from mbucftambol order by tambol_code";
            }
            else if (district_code != string.Empty && tambol_code == string.Empty)
            {
                sql = @"select tambol_code,tambol_desc from mbucftambol where  district_code = {0}  union
            select tambol_code,tambol_desc from mbucftambol  where  district_code not in ({0})  order by tambol_code";
            }
            else if (district_code != string.Empty && tambol_code != string.Empty)
            {
                sql = @"select tambol_code,tambol_desc from mbucftambol where  district_code = {0} and tambol_code={1}  union
            select tambol_code,tambol_desc from mbucftambol  where  tambol_code not in ({0})  order by tambol_code";
            }
            sql = WebUtil.SQLFormat(sql, district_code, tambol_code);
            DataTable dt = WebUtil.Query(sql);
            this.DropDownDataBind(dt, "debt_tumbon", "tambol_desc", "tambol_code");
        }
        public void Retrievepostback(string debt_no)
        {
            string sql = string.Empty;
            sql = @"select 
debt_addr,
debt_tumbon,
debt_amphur,
debt_province,
debt_postcode,
debt_phone,
debt_tax_no,
bfdebt_amt,
bf_feeamt,
credit_term,
creditbalance_amt,
credit_amt,
debt_amt,
refdebt_no
 from stdebtmaster where debt_no = {0} and coop_id = {1}";
            sql = WebUtil.SQLFormat(sql, debt_no, state.SsCoopControl);
            Sdt dtoth = new Sdt();
            dtoth = WebUtil.QuerySdt(sql);
            if (dtoth.Next())
            {
                this.DATA[0].DEBT_ADDR = dtoth.GetString("debt_addr");
                this.DATA[0].DEBT_TUMBON = dtoth.GetString("debt_tumbon");
                this.DATA[0].DEBT_AMPHUR = dtoth.GetString("debt_amphur");
                this.DATA[0].DEBT_PROVINCE = dtoth.GetString("debt_province");
                this.DATA[0].DEBT_PHONE = dtoth.GetString("debt_phone");
                this.DATA[0].DEBT_POSTCODE = dtoth.GetString("debt_postcode");
                this.DATA[0].DEBT_TAX_NO = dtoth.GetString("debt_tax_no");
                this.DATA[0].BFDEBT_AMT = dtoth.GetDecimal("bfdebt_amt");
                this.DATA[0].BF_FEEAMT = dtoth.GetDecimal("bf_feeamt");
                this.DATA[0].CREDITBALANCE_AMT = dtoth.GetDecimal("creditbalance_amt");
                this.DATA[0].CREDIT_AMT = dtoth.GetDecimal("credit_amt");
                this.DATA[0].DEBT_AMT = dtoth.GetDecimal("debt_amt");
                this.DATA[0].REFDEBT_NO = dtoth.GetString("refdebt_no");
            }
        }
        public void loadbeginprovince(string province_code)
        {
            if (Sta.IS_MYSQL_MODE)
            {
                string sql = @"select province_code,province_desc from mbucfprovince where province_code={0}
                union select province_code, province_desc from mbucfprovince order by province_code,province_desc";
                sql = WebUtil.SQLFormat(sql, province_code);
                DataTable dt = WebUtil.Query(sql);
                this.DropDownDataBind(dt, "debt_province", "province_desc", "province_code");
            }
        }
        public void loadbeginreamphur(string am_phur, string province)
        {
            if (Sta.IS_MYSQL_MODE)
            {
                string sql = @"select district_code,district_desc from mbucfdistrict where  district_code={0} 
union select district_code, district_desc from mbucfdistrict where province_code ={1}
order by district_code";
                sql = WebUtil.SQLFormat(sql, am_phur, province);
                DataTable dt = WebUtil.Query(sql);
                this.DropDownDataBind(dt, "debt_amphur", "district_desc", "district_code");

            }
        }
        public void loadbeginretambol(string am_phur)
        {
            if (Sta.IS_MYSQL_MODE)
            {
                string sql = @"select tambol_code,tambol_desc from mbucftambol where district_code ={0}
union select '000' as tambol_code,'กรุณาเลือก' tambol_desc  order by tambol_code";
                sql = WebUtil.SQLFormat(sql, am_phur);
                DataTable dt = WebUtil.Query(sql);
                this.DropDownDataBind(dt, "debt_tumbon", "tambol_desc", "tambol_code");

            }
        }
        public string REdescprovince(string province_code)
        {
            string sql = string.Empty;
            string province = string.Empty;
            sql = @"select province_desc from mbucfprovince where province_code = {0}";
            sql = WebUtil.SQLFormat(sql, province_code);
            Sdt st = new Sdt();
            st = WebUtil.QuerySdt(sql);
            if (st.Next())
            {
                province = st.GetString("province_desc");
            }
            return province;
        }
        public string REdesctumbon(string tambol_code)
        {
            string sql = string.Empty;
            string tumbon = string.Empty;
            sql = @"select tambol_desc from mbucftambol where tambol_code = {0}";
            sql = WebUtil.SQLFormat(sql, tambol_code);
            Sdt st = new Sdt();
            st = WebUtil.QuerySdt(sql);
            if (st.Next())
            {
                tumbon = st.GetString("tambol_desc");
            }
            return tumbon;
        }

        public string REdescamphur(string district_code)
        {
            string sql = string.Empty;
            string amphur = string.Empty;
            sql = @"select district_desc from mbucfdistrict where district_code = {0}";
            sql = WebUtil.SQLFormat(sql, district_code);
            Sdt st = new Sdt();
            st = WebUtil.QuerySdt(sql);
            if (st.Next())
            {
                amphur = st.GetString("district_desc");
            }
            return amphur;
        }
    }
}