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>
    /// 10번컷
    /// </summary>
    internal class Trading : ADataObject
    {
     

        public Trading(선그래프데이터분류 recvDataType)
        {
            this.mDataType = recvDataType;
        }

        private 선그래프데이터분류 mDataType;

        #region 개인쿼리

        string queryRetailNumber = @"select 
                '코스피' name, 
                round((sum(F_SELL_turnover) - sum(F_BUY_turnover))/100000000) amt 
            from t_invest 
            where F_PART_CODE = '001' 
                and F_INVEST_CODE = '8000' 
            UNION 
            select 
                '코스닥' name, 
                round((sum(F_SELL_turnover) - sum(F_BUY_turnover)) / 100000000) amt 
            from t_kosdaq_invest 
            where F_PART_CODE = '001' 
                and F_INVEST_CODE = '8000' 
            union 
            select 
                '코스피200' name, 
                round((sum(F_SELL_turnover) / 100000000) - (sum(F_BUY_turnover) / 100000000)) as amt 
            from t_invest 
            where F_PART_CODE = '029' 
                and F_INVEST_CODE = '8000'";

        string queryRetailGraph = @"select 
                '코스피' f_name, 
                f_data_time f_data_time, 
                round((F_SELL_turnover - F_BUY_turnover) / 100000000) price 
            from t_invest_his 
            where F_PART_CODE = '001' 
                and F_INVEST_CODE = '8000' 
                and f_data_time like(select max(open_day) || '%' data_time from v_open_day) 
            union 
            select 
                '코스닥' f_name, 
                f_data_time f_data_time, 
                round((F_SELL_turnover - F_BUY_turnover) / 100000000) price 
            from t_kosdaq_invest_his 
            where F_PART_CODE = '001' 
                and F_INVEST_CODE = '8000' 
                and f_data_time like(select max(open_day) || '%' data_time from v_open_day) 
            union 
            select 
                '코스피200' f_name, 
                f_data_time f_data_time, 
                round((F_SELL_turnover - F_BUY_turnover) / 100000000) price 
            from t_invest_his 
            where F_PART_CODE = '029' 
                and F_INVEST_CODE = '8000' 
                and f_data_time like(select max(open_day) || '%' data_time from v_open_day) 
            order by  f_name, f_data_time";

        #endregion

        #region "외국인"

        string queryForgienNumber = @"select 
                '코스피' name, 
                round((sum(F_SELL_turnover) - sum(F_BUY_turnover))/100000000) amt 
            from t_invest 
            where F_PART_CODE = '001' 
                and F_INVEST_CODE in('9000', '9001') 
            UNION 
            select 
                '코스닥' name, 
                round((sum(F_SELL_turnover) - sum(F_BUY_turnover)) / 100000000) amt 
            from t_kosdaq_invest 
            where F_PART_CODE = '001' 
                and F_INVEST_CODE in('9000', '9001') 
            union 
            select 
                '코스피200' name, 
                round((sum(F_SELL_turnover) / 100000000) - (sum(F_BUY_turnover) / 100000000)) as amt 
            from t_invest 
            where F_PART_CODE = '029' 
                and F_INVEST_CODE in('9000', '9001')";

        string queryForgienGraph = @"select 
                '코스피' f_name, 
                f_data_time f_data_time, 
                round((sum(F_SELL_turnover) - sum(F_BUY_turnover)) / 100000000) price 
            from t_invest_his 
            where F_PART_CODE = '001' 
                and F_INVEST_CODE in('9000', '9001') 
                and f_data_time like(select max(open_day) || '%' data_time from v_open_day) 
            group by f_data_time 
            union 
            select 
                '코스닥' f_name, 
                f_data_time f_data_time, 
                round((sum(F_SELL_turnover) - sum(F_BUY_turnover)) / 100000000) price 
            from t_kosdaq_invest_his 
            where F_PART_CODE = '001' 
                and F_INVEST_CODE in('9000', '9001') 
                and f_data_time like(select max(open_day) || '%' data_time from v_open_day) 
            group by f_data_time 
            union 
            select 
                '코스피200' f_name, 
                f_data_time f_data_time, 
                round((sum(F_SELL_turnover) - sum(F_BUY_turnover)) / 100000000) price 
            from t_invest_his 
            where F_PART_CODE = '029' 
                and F_INVEST_CODE in('9000', '9001') 
                and f_data_time like(select max(open_day) || '%' data_time from v_open_day) 
            group by f_data_time 
            order by f_name, f_data_time";

        #endregion

        #region "기관"

        string queryInstitutionalNumber = @"select 
                '코스피' f_name, 
                round((sum(F_SELL_turnover) - sum(F_BUY_turnover))/100000000) amt 
            from t_invest 
            where F_PART_CODE = '001' 
                and F_INVEST_CODE in('1000', '2000', '3000', '3100', '4000', '5000', '6000', '7000') 
            group by f_part_code 
            UNION 
            select 
                '코스닥' f_name, 
                round((sum(F_SELL_turnover) - sum(F_BUY_turnover)) / 100000000) amt 
            from t_kosdaq_invest 
            where F_PART_CODE = '001' 
                and F_INVEST_CODE in('1000', '2000', '3000', '3100', '4000', '5000', '6000', '7000') 
            group by f_part_code 
            union 
            select 
                '코스피200' f_name, 
                round((sum(F_SELL_turnover) / 100000000) - (sum(F_BUY_turnover) / 100000000)) as amt 
            from t_invest 
            where F_PART_CODE = '029' 
                and F_INVEST_CODE in('1000', '2000', '3000', '3100', '4000', '5000', '6000', '7000')";

        string queryInstitutionalGraph = @"select 
                '코스피' f_name, 
                f_data_time f_data_time, 
                round((sum(F_SELL_turnover) - sum(F_BUY_turnover)) / 100000000) price 
            from t_invest_his 
            where F_PART_CODE = '001' 
                and F_INVEST_CODE in('1000', '2000', '3000', '3100', '4000', '5000', '6000', '7000') 
                and f_data_time like(select max(open_day) || '%' data_time from v_open_day) 
            group by f_data_time 
            union 
            select 
                '코스닥' f_name, 
                f_data_time f_data_time, 
                round((sum(F_SELL_turnover) - sum(F_BUY_turnover)) / 100000000) price 
            from t_kosdaq_invest_his 
            where F_PART_CODE = '001' 
                and F_INVEST_CODE in('1000', '2000', '3000', '3100', '4000', '5000', '6000', '7000') 
                and f_data_time like(select max(open_day) || '%' data_time from v_open_day) 
            group by f_data_time 
            union 
            select 
                '코스피200' f_name, 
                f_data_time f_data_time, 
                round((sum(F_SELL_turnover) - sum(F_BUY_turnover)) / 100000000) price 
            from t_invest_his 
            where F_PART_CODE = '029' 
                and F_INVEST_CODE in('1000', '2000', '3000', '3100', '4000', '5000', '6000', '7000') 
                and f_data_time like(select max(open_day) || '%' data_time from v_open_day) 
            group by f_data_time 
            order by f_name, f_data_time";

        #endregion

        #region "코스피"

        string queryKOSPINumber = @"select 
                sum(decode(f_invest_code, '8000',  round((sum(F_SELL_turnover) - sum(F_BUY_turnover))/100000000),0)  ) ""개인"", 
                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)) ""외국인"", 
                sum(decode(f_invest_code, '8000', 0, '9000', 0, '9001', 0, round((sum(F_SELL_turnover) - sum(F_BUY_turnover)) / 100000000))) ""기관"" 
            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";

        string queryKOSPIGraph = @"select 
                f_data_time, sum(p_1) ""개인"", 
                sum(p_2) ""외국인"", 
                sum(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 like(select max(open_day) || '%' data_time from v_open_day) 
                group by f_data_time, f_invest_code 
            ) 
            group by f_data_time 
            order by f_data_time";

        #endregion

        #region "코스닥"

        string queryKOSDAQNumber = @"select 
                sum(decode(f_invest_code, '8000',  round((sum(F_SELL_turnover) - sum(F_BUY_turnover))/100000000),0)  ) ""개인"", 
                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)) ""외국인"", 
                sum(decode(f_invest_code, '8000', 0, '9000', 0, '9001', 0, round((sum(F_SELL_turnover) - sum(F_BUY_turnover)) / 100000000))) ""기관"" 
            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";

        string queryKOSDAQGraph = @"select 
                f_data_time, 
                sum(p_1) ""개인"", 
                sum(p_2) ""외국인"", 
                sum(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 like(select max(open_day) || '%' data_time from v_open_day) 
                group by f_data_time, f_invest_code 
            ) 
            group by f_data_time 
            order by f_data_time";

        #endregion

        public override IDataRequest buildData()
        {

            string bufQueryNumber = "";

            string bufQueryGraph = "";

            switch (this.mDataType)
            {
                case 선그래프데이터분류.개인매매동향:
                    bufQueryNumber = queryRetailNumber;
                    bufQueryGraph = queryRetailGraph;
                    break;
                case 선그래프데이터분류.기관매매동향:
                    bufQueryNumber = queryInstitutionalNumber;
                    bufQueryGraph = queryInstitutionalGraph;
                    break;
                case 선그래프데이터분류.외국인매매동향:
                    bufQueryNumber = queryForgienNumber;
                    bufQueryGraph = queryForgienGraph;
                    break;
                case 선그래프데이터분류.코스피매매동향:
                    bufQueryNumber = queryKOSPINumber;
                    bufQueryGraph = queryKOSPIGraph;
                    break;
                case 선그래프데이터분류.코스닥매매동향:
                    bufQueryNumber = queryKOSDAQNumber;
                    bufQueryGraph = queryKOSDAQGraph;
                    break;
                default:
                    throw new Exception("잘못 참조한 데이터(매매동향에 없는 데이터)");
            }

            mRequestQuery.Add("현재지수", bufQueryNumber);
            mRequestQuery.Add("그래프데이터", bufQueryGraph);

            return this;
        }
    }
}
