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

namespace MMoneyCoderSharp.DB
{
    /// <summary>
    /// </summary>
    internal class Grid9 : ADataObject
    {
     
        public Grid9(국내시장종류 recvMarketType)
        {
            this.mMarketType = recvMarketType;
        }

        private 국내시장종류 mMarketType;

        /// <summary>
        /// 금일 데이터 쿼리
        /// </summary>
         string queryKOSPIList = @"select 
                (select max(open_day)  from v_open_day) f_data_time, 
                P_1 ""개인"", 
                P_2 ""외국인"", 
                P_3 ""기관"" 
            FROM( 
                select 
                    sum(decode(f_invest_code, '8000', round((sum(F_SELL_turnover) - sum(F_BUY_turnover)) / 100000000), 0)) p_1, 
                    sum(decode(f_invest_code, '9000', round((sum(F_SELL_turnover) - sum(F_BUY_turnover)) / 100000000), '9001', round((sum(F_SELL_turnover) - sum(F_BUY_turnover)) / 100000000), 0)) p_2, 
                    sum(decode(f_invest_code, '8000', 0, '9000', 0, '9001', 0, round((sum(F_SELL_turnover) - sum(F_BUY_turnover)) / 100000000))) p_3 
                from t_invest 
                where F_PART_CODE = '001' 
                    and F_INVEST_CODE in('1000', '2000', '3000', '3100', '4000', '5000', '6000', '7000', '8000', '9000', '9001') 
                group by f_invest_code 
            ) 
            UNION 
            select 
                f_data_time, 
                sum(p_1) ""개인"", 
                sum(p_2) ""외국인"", 
                sum(p_3) ""기관"" 
            from( 
                select 
                    substr(f_data_time, 1, 8) f_data_time, 
                    decode(f_invest_code, '8000', round((sum(F_SELL_turnover) - sum(F_BUY_turnover)) / 100000000), 0)   p_1, 
                    decode(f_invest_code, '9000', round((sum(F_SELL_turnover) - sum(F_BUY_turnover)) / 100000000), '9001', round((sum(F_SELL_turnover) - sum(F_BUY_turnover)) / 100000000), 0)   p_2, 
                    decode(f_invest_code, '8000', 0, '9000', 0, '9001', 0, round((sum(F_SELL_turnover) - sum(F_BUY_turnover)) / 100000000))   p_3 
                from t_invest_his 
                where F_PART_CODE = '001' 
                    and F_INVEST_CODE in('1000', '2000', '3000', '3100', '4000', '5000', '6000', '7000', '8000', '9000', '9001') 
                    and f_data_time in ( 
                        select data_time || '1535' 
                        from(select open_day data_time from v_open_day 
                        minus 
                        select substr(f_data_time, 1, 8) data_time 
                        from t_index 
                        where f_part_code = '001' 
                        order by data_time desc 
                    ) 
                    where rownum< 5 
                ) 
                group by f_data_time, f_invest_code 
            ) 
            group by f_data_time 
            order by f_data_time desc";

        string queryKOSPISum = @"select 
                sum(p_1) ""개인"", 
                sum(p_2) ""외국인"", 
                sum(p_3) ""기관"" 
            from( 
                select 
                    sum(decode(f_invest_code, '8000', round((sum(F_SELL_turnover) - sum(F_BUY_turnover)) / 100000000), 0)) p_1, 
                    sum(decode(f_invest_code, '9000', round((sum(F_SELL_turnover) - sum(F_BUY_turnover)) / 100000000),  '9001', round((sum(F_SELL_turnover) - sum(F_BUY_turnover)) / 100000000), 0)) p_2, 
                    sum(decode(f_invest_code, '8000', 0, '9000', 0, '9001', 0, round((sum(F_SELL_turnover) - sum(F_BUY_turnover)) / 100000000))) p_3 
                from t_invest 
                where F_PART_CODE = '001' 
                    and F_INVEST_CODE in('1000', '2000', '3000', '3100', '4000', '5000', '6000', '7000', '8000', '9000', '9001') 
                group by f_invest_code 
                union 
                select 
                    sum(p_1) p_1, 
                    sum(p_2) p_2, 
                    sum(p_3) p_3  
                from( 
                    select 
                        f_data_time f_data_time, 
                        decode(f_invest_code, '8000', round((sum(F_SELL_turnover) - sum(F_BUY_turnover)) / 100000000), 0)   p_1, 
                        decode(f_invest_code, '9000', round((sum(F_SELL_turnover) - sum(F_BUY_turnover)) / 100000000), '9001', round((sum(F_SELL_turnover) - sum(F_BUY_turnover)) / 100000000), 0)   p_2, 
                        decode(f_invest_code, '8000', 0, '9000', 0, '9001', 0, round((sum(F_SELL_turnover) - sum(F_BUY_turnover)) / 100000000))   p_3 
                    from t_invest_his 
                    where F_PART_CODE = '001' 
                        and F_INVEST_CODE in('1000', '2000', '3000', '3100', '4000', '5000', '6000', '7000', '8000', '9000', '9001') 
                        and f_data_time in ( 
                            select 
                                data_time 
                            from( 
                                select 
                                    open_day || '1535' data_time 
                                from v_open_day 
                                minus 
                                select 
                                    substr(f_data_time, 1, 8) data_time 
                                from t_index 
                                where f_part_code = '001' 
                                order by data_time desc 
                            ) 
                            where rownum< 20 
                        ) 
                    group by f_data_time, f_invest_code 
                ) 
            )";

        string queryKOSDAQList = @"select 
                (select max(open_day)  from v_open_day) f_data_time, 
                P_1 ""개인"", 
                P_2 ""외국인"", 
                P_3 ""기관"" 
            FROM( 
                select 
                    sum(decode(f_invest_code, '8000', round((sum(F_SELL_turnover) - sum(F_BUY_turnover)) / 100000000), 0)) p_1, 
                    sum(decode(f_invest_code, '9000', round((sum(F_SELL_turnover) - sum(F_BUY_turnover)) / 100000000), '9001', round((sum(F_SELL_turnover) - sum(F_BUY_turnover)) / 100000000), 0)) p_2, 
                    sum(decode(f_invest_code, '8000', 0, '9000', 0, '9001', 0, round((sum(F_SELL_turnover) - sum(F_BUY_turnover)) / 100000000))) p_3 
                from t_kosdaq_invest 
                where F_PART_CODE = '001' 
                    and F_INVEST_CODE in('1000', '2000', '3000', '3100', '4000', '5000', '6000', '7000', '8000', '9000', '9001') 
                group by f_invest_code 
            ) 
            UNION 
            select 
                f_data_time, 
                sum(p_1) ""개인"", 
                sum(p_2) ""외국인"", 
                sum(p_3) ""기관"" 
            from( 
                select 
                    substr(f_data_time, 1, 8) f_data_time, 
                    decode(f_invest_code, '8000', round((sum(F_SELL_turnover) - sum(F_BUY_turnover)) / 100000000), 0)   p_1, 
                    decode(f_invest_code, '9000', round((sum(F_SELL_turnover) - sum(F_BUY_turnover)) / 100000000), '9001', round((sum(F_SELL_turnover) - sum(F_BUY_turnover)) / 100000000), 0)   p_2, 
                    decode(f_invest_code, '8000', 0, '9000', 0, '9001', 0, round((sum(F_SELL_turnover) - sum(F_BUY_turnover)) / 100000000))   p_3 
                from t_kosdaq_invest_his 
                where F_PART_CODE = '001' 
                    and F_INVEST_CODE in('1000', '2000', '3000', '3100', '4000', '5000', '6000', '7000', '8000', '9000', '9001') 
                    and f_data_time in ( 
                        select data_time || '1535' 
                        from( 
                            select open_day data_time from v_open_day 
                            minus 
                            select substr(f_data_time, 1, 8) data_time from t_index 
                            where f_part_code = '001' 
                            order by data_time desc 
                        ) 
                        where rownum< 5 
                    ) 
                    group by f_data_time, f_invest_code 
            ) 
            group by f_data_time 
            order by f_data_time desc";

        string queryKOSDAQSum = @"select 
                sum(p_1) ""개인"", 
                sum(p_2) ""외국인"", 
                sum(p_3) ""기관"" 
            from( 
                select 
                    sum(decode(f_invest_code, '8000', round((sum(F_SELL_turnover) - sum(F_BUY_turnover)) / 100000000), 0)) p_1, 
                    sum(decode(f_invest_code, '9000', round((sum(F_SELL_turnover) - sum(F_BUY_turnover)) / 100000000), '9001', round((sum(F_SELL_turnover) - sum(F_BUY_turnover)) / 100000000), 0)) p_2, 
                    sum(decode(f_invest_code, '8000', 0, '9000', 0, '9001', 0, round((sum(F_SELL_turnover) - sum(F_BUY_turnover)) / 100000000))) p_3 
                from t_kosdaq_invest 
                where F_PART_CODE = '001' 
                    and F_INVEST_CODE in('1000', '2000', '3000', '3100', '4000', '5000', '6000', '7000', '8000', '9000', '9001') 
                group by f_invest_code 
                union 
                select 
                    sum(p_1) p_1, 
                    sum(p_2) p_2, 
                    sum(p_3) p_3 
                from( 
                    select 
                        f_data_time f_data_time, 
                        decode(f_invest_code, '8000', round((sum(F_SELL_turnover) - sum(F_BUY_turnover)) / 100000000), 0)   p_1, 
                        decode(f_invest_code, '9000', round((sum(F_SELL_turnover) - sum(F_BUY_turnover)) / 100000000), '9001', round((sum(F_SELL_turnover) - sum(F_BUY_turnover)) / 100000000), 0)   p_2, 
                        decode(f_invest_code, '8000', 0, '9000', 0, '9001', 0, round((sum(F_SELL_turnover) - sum(F_BUY_turnover)) / 100000000))   p_3 
                    from t_kosdaq_invest_his 
                    where F_PART_CODE = '001' 
                        and F_INVEST_CODE in('1000', '2000', '3000', '3100', '4000', '5000', '6000', '7000', '8000', '9000', '9001') 
                        and f_data_time in ( 
                            select 
                                data_time 
                            from( 
                                select 
                                    open_day || '1535' data_time 
                                from v_open_day 
                                minus 
                                select 
                                    substr(f_data_time, 1, 8) data_time 
                                from t_index 
                                where f_part_code = '001' 
                                order by data_time desc 
                            ) 
                            where rownum< 20 
                        ) 
                    group by f_data_time, f_invest_code 
                ) 
            )";

        public override IDataRequest buildData()
        {

            
            if (mMarketType == 국내시장종류.KOSPI)
            {
                mRequestQuery.Add("일별리스트", queryKOSPIList);
                mRequestQuery.Add("한달합계", queryKOSPISum);
            }
            else
            {
                mRequestQuery.Add("일별리스트", queryKOSDAQList);
                mRequestQuery.Add("한달합계", queryKOSDAQSum);
            }
            
            

            return this;
        }
    }
}
