using MMoneyCoderSharp.DB;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using static MMoneyCoderSharp.DBDefine;

namespace MMoneyCoderSharp.Data.Request
{
    internal class IndexYield : ADataObject
    {

        /// <summary>
        /// 
        /// </summary>
        /// <param name="recvStockName"></param>
        /// <param name="recvDataLength"></param>
        public IndexYield(데이터기간 recvDataLength, 국내지수종류 recvIndexType)
        {
            this.mDataLength = recvDataLength;
            this.mIndexType = recvIndexType;
        }

        private 데이터기간 mDataLength;

        private 국내지수종류 mIndexType;

        //const string queryKOSPI = @"select 
        //        substr(f_data_time, 1,8) data_day, 
        //        f_part_idx/100 curr_price, 
        //        f_chg_type chg_type, 
        //        F_PART_CHG/100 net_chg, 
        //        round((f_part_chg / decode(f_chg_type, '+', (f_part_idx - f_part_chg), '-', (f_part_idx + f_part_chg),1)) * 100, 2) rate 
        //    from t_index_his 
        //    where f_part_code = '001' 
        //        and Rtrim(f_data_time) in( 
        //            select open_day || '1535'  
        //            from ( 
        //                select open_day 
        //                from v_open_day 
        //                where open_day <> to_char(sysdate, 'yyyymmdd') 
        //                minus 
        //                select substr(f_data_time, 1, 8) data_day 
        //                from t_index 
        //                where f_part_code = '001' 
        //                order by open_day desc 
        //            ) 
        //            where rownum < {0}
        //        ) 
        //    UNION 
        //    select 
        //        substr(f_data_time, 1, 8) data_day, 
        //        f_part_idx / 100 curr_price, 
        //        f_chg_type chg_type, 
        //        F_PART_CHG / 100 net_chg, 
        //        round((f_part_chg / decode(f_chg_type, '+', (f_part_idx - f_part_chg), '-', (f_part_idx + f_part_chg),1)) * 100, 2) rate 
        //    from t_index 
        //    where f_part_code = '001' 
        //    order by data_day desc";

        const string queryKOSPI = @"select 
                f_data_day data_day, 
                f_part_idx/100 curr_price, 
                f_chg_type chg_type, 
                F_PART_CHG/100 net_chg, 
                round((f_part_chg / decode(f_chg_type, '+', (f_part_idx - f_part_chg), '-', (f_part_idx + f_part_chg),1)) * 100, 2) rate 
            from t_index_his_day 
            where f_part_code = '001' 
              and Rtrim(f_data_day) in( 
                    select open_day
                    from ( 
                        select open_day 
                        from v_open_day 
                        where open_day <> to_char(sysdate, 'yyyymmdd') 
                        minus 
                        select substr(f_data_time, 1, 8) data_day 
                        from t_index 
                        where f_part_code = '001' 
                        order by open_day desc 
                    ) 
                    where rownum < {0}
                ) 
            UNION 
            select 
                substr(f_data_time, 1, 8) data_day, 
                f_part_idx / 100 curr_price, 
                f_chg_type chg_type, 
                F_PART_CHG / 100 net_chg, 
                round((f_part_chg / decode(f_chg_type, '+', (f_part_idx - f_part_chg), '-', (f_part_idx + f_part_chg),1)) * 100, 2) rate 
            from t_index 
            where f_part_code = '001' 
            order by data_day desc";

        //const string queryKOSDAQ = @"select 
        //        substr(f_data_time, 1,8) data_day, 
        //        f_part_idx/100 curr_price, 
        //        f_chg_type chg_type, 
        //        F_PART_CHG/100 net_chg, 
        //        round((f_part_chg / decode(f_chg_type, '+', (f_part_idx - f_part_chg), '-', (f_part_idx + f_part_chg),1)) * 100, 2) rate 
        //    from t_kosdaq_index_his 
        //    where f_part_code = '001' 
        //        and Rtrim(f_data_time) in ( 
        //            select open_day || '1535' 
        //            from ( 
        //                select open_day from v_open_day 
        //                where open_day <> to_char(sysdate, 'yyyymmdd') 
        //                minus 
        //                select substr(f_data_time, 1, 8) data_day 
        //                from t_kosdaq_index 
        //                where f_part_code = '001' 
        //                order by open_day desc 
        //            ) 
        //            where rownum < {0} 
        //    ) 
        //    UNION 
        //    select 
        //        substr(f_data_time, 1, 8) data_day, 
        //        f_part_idx / 100 curr_price, 
        //        f_chg_type chg_type, 
        //        F_PART_CHG / 100 net_chg, 
        //        round((f_part_chg / decode(f_chg_type, '+', (f_part_idx - f_part_chg), '-', (f_part_idx + f_part_chg),1)) * 100, 2) rate 
        //    from t_kosdaq_index 
        //    where f_part_code = '001' 
        //    order by data_day desc";
        const string queryKOSDAQ = @"select 
                 f_data_day data_day, 
                 f_part_idx/100 curr_price, 
                 f_chg_type chg_type, 
                 F_PART_CHG/100 net_chg, 
                 round((f_part_chg / decode(f_chg_type, '+', (f_part_idx - f_part_chg), '-', (f_part_idx + f_part_chg),1)) * 100, 2) rate 
            from t_kosdaq_index_his_day 
            where f_part_code = '001' 
                and Rtrim(f_data_day) in ( 
                    select open_day
                    from ( 
                        select open_day from v_open_day 
                        where open_day <> to_char(sysdate, 'yyyymmdd') 
                        minus 
                        select substr(f_data_time, 1, 8) data_day 
                        from t_kosdaq_index 
                        where f_part_code = '001' 
                        order by open_day desc 
                    ) 
                    where rownum < {0} 
            ) 
            UNION 
            select 
                substr(f_data_time, 1, 8) data_day, 
                f_part_idx / 100 curr_price, 
                f_chg_type chg_type, 
                F_PART_CHG / 100 net_chg, 
                round((f_part_chg / decode(f_chg_type, '+', (f_part_idx - f_part_chg), '-', (f_part_idx + f_part_chg),1)) * 100, 2) rate 
            from t_kosdaq_index 
            where f_part_code = '001' 
            order by data_day desc";

        //const string queryKOSPI200 = @"select 
        //        substr(f_data_time, 1,8) data_day, 
        //        f_part_idx/100 curr_price, 
        //        f_chg_type chg_type, 
        //        F_PART_CHG/100 net_chg, 
        //        round((f_part_chg / decode(f_chg_type, '+', (f_part_idx - f_part_chg), '-', (f_part_idx + f_part_chg),1)) * 100, 2) rate 
        //    from T_200_INDEX_his 
        //    where f_part_code = '029' 
        //        and Rtrim(f_data_time) in ( 
        //            select open_day || '1535' 
        //            from ( 
        //                select open_day from v_open_day 
        //                where open_day <> to_char(sysdate, 'yyyymmdd') 
        //                minus 
        //                select substr(f_data_time, 1, 8) data_day 
        //                from t_200_index 
        //                where f_part_code = '029' 
        //                order by open_day desc 
        //            ) 
        //            where rownum < {0} 
        //    ) 
        //    UNION 
        //    SELECT 
        //        substr(f_data_time, 1, 8) data_day, 
        //        f_part_idx / 100 part_idx, 
        //        f_chg_type chg_type, 
        //        f_part_chg / 100 part_chg, 
        //        round((f_part_chg / decode(f_chg_type, '+', (f_part_idx - f_part_chg), '-', (f_part_idx + f_part_chg),1)) * 100, 2) rate 
        //    FROM T_200_INDEX 
        //    WHERE f_part_code = '029' 
        //    order by data_day desc";
        const string queryKOSPI200 = @"select 
                 f_data_day data_day, 
                 f_part_idx/100 curr_price, 
                 f_chg_type chg_type, 
                 F_PART_CHG/100 net_chg, 
                 round((f_part_chg / decode(f_chg_type, '+', (f_part_idx - f_part_chg), '-', (f_part_idx + f_part_chg),1)) * 100, 2) rate 
            from T_200_INDEX_his_day 
            where f_part_code = '029' 
              and Rtrim(f_data_day) in ( 
                    select open_day
                    from ( 
                        select open_day from v_open_day 
                        where open_day <> to_char(sysdate, 'yyyymmdd') 
                        minus 
                        select substr(f_data_time, 1, 8) data_day 
                        from t_200_index 
                        where f_part_code = '029' 
                        order by open_day desc 
                    ) 
                    where rownum < {0} 
            ) 
            UNION 
            SELECT 
                substr(f_data_time, 1, 8) data_day, 
                f_part_idx / 100 part_idx, 
                f_chg_type chg_type, 
                f_part_chg / 100 part_chg, 
                round((f_part_chg / decode(f_chg_type, '+', (f_part_idx - f_part_chg), '-', (f_part_idx + f_part_chg),1)) * 100, 2) rate 
            FROM T_200_INDEX 
            WHERE f_part_code = '029' 
            order by data_day desc";

        //const string queryKRX100 = @"select 
        //        substr(f_data_time, 1,8) data_day, 
        //        f_part_idx/100 curr_price, 
        //        f_chg_type chg_type, 
        //        F_PART_CHG/100 net_chg, 
        //        round((f_part_chg / decode(f_chg_type, '+', (f_part_idx - f_part_chg), '-', (f_part_idx + f_part_chg),1)) * 100, 2) rate 
        //    from T_KRX100_INDEX_his 
        //    where f_part_code = '043' 
        //        and Rtrim(f_data_time) in ( 
        //            select open_day || '1535' 
        //            from ( 
        //                select open_day from v_open_day 
        //                where open_day <> to_char(sysdate, 'yyyymmdd') 
        //                minus 
        //                select substr(f_data_time, 1, 8) data_day 
        //                from t_krx100_index 
        //                where f_part_code = '043' 
        //                order by open_day desc 
        //            ) 
        //            where rownum < {0} 
        //        ) 
        //    UNION 
        //    SELECT 
        //        substr(f_data_time, 1, 8) data_day, 
        //        f_part_idx / 100 part_idx, 
        //        f_chg_type chg_type, 
        //        f_part_chg / 100 part_chg, 
        //        round((f_part_chg / decode(f_chg_type, '+', (f_part_idx - f_part_chg), '-', (f_part_idx + f_part_chg),1)) * 100, 2) rate 
        //    FROM T_KRX100_INDEX 
        //    WHERE f_part_code = '043' 
        //    order by data_day desc";

        const string queryKRX100 = @"select 
                f_data_day data_day, 
                f_part_idx/100 curr_price, 
                f_chg_type chg_type, 
                F_PART_CHG/100 net_chg, 
                round((f_part_chg / decode(f_chg_type, '+', (f_part_idx - f_part_chg), '-', (f_part_idx + f_part_chg),1)) * 100, 2) rate 
            from T_KRX100_INDEX_his_day 
            where f_part_code = '043' 
                and f_data_day in ( 
                    select open_day
                    from ( 
                        select open_day from v_open_day 
                        where open_day <> to_char(sysdate, 'yyyymmdd') 
                        minus 
                        select substr(f_data_time, 1, 8) data_day 
                        from t_krx100_index 
                        where f_part_code = '043' 
                        order by open_day desc 
                    ) 
                    where rownum < {0} 
                ) 
            UNION 
            SELECT 
                substr(f_data_time, 1, 8) data_day, 
                f_part_idx / 100 part_idx, 
                f_chg_type chg_type, 
                f_part_chg / 100 part_chg, 
                round((f_part_chg / decode(f_chg_type, '+', (f_part_idx - f_part_chg), '-', (f_part_idx + f_part_chg),1)) * 100, 2) rate 
            FROM T_KRX100_INDEX 
            WHERE f_part_code = '043' 
            order by data_day desc";

        public override IDataRequest buildData()
        {
            string bufQuery  = string.Empty;

            if (mIndexType == 국내지수종류.KOSPI)
            {
                bufQuery = String.Format(queryKOSPI, this.mDataLength.GetStringValue());
            }
            else if (mIndexType == 국내지수종류.KOSDAQ)
            {
                bufQuery = String.Format(queryKOSDAQ, this.mDataLength.GetStringValue());
            }
            else if (mIndexType == 국내지수종류.KOSPI200)
            {
                bufQuery = String.Format(queryKOSPI200, this.mDataLength.GetStringValue());
            }
            else if (mIndexType == 국내지수종류.KRX100)
            {
                bufQuery = String.Format(queryKRX100, this.mDataLength.GetStringValue());
            }

            mRequestQuery.Add("", bufQuery);

            return this;
        }
    }

}
