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 Grid8 : ADataObject
    {
     
        /// <summary>
        /// 금일 데이터 쿼리
        /// </summary>
         string queryToday = @"select 
                name, 
                sum(decode(invest, '개인',   org_amt, 0)) ""개인"", 
                sum(decode(invest, '외국인', org_amt, 0)) ""외국인"", 
                sum(decode(invest, '기관', org_amt, 0)) ""기관"" 
            from( 
                select 
                    1 s, 
                    '코스피' name, 
                    decode(f_invest_code, '8000', '개인', '9000', '외국인', '9001', '외국인', '기관') invest, 
                    round((sum(F_SELL_turnover) / 100000000) - (sum(F_BUY_turnover) / 100000000)) as org_amt 
                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 decode(f_invest_code, '8000', '개인', '9000', '외국인', '9001', '외국인', '기관') 
                UNION 
                select 
                    2 s, 
                    '코스닥' name, 
                    decode(f_invest_code, '8000', '개인', '9000', '외국인', '9001', '외국인', '기관') invest, 
                    round((sum(F_SELL_turnover) / 100000000) - (sum(F_BUY_turnover) / 100000000)) as org_amt 
                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 decode(f_invest_code, '8000', '개인', '9000', '외국인', '9001', '외국인', '기관') 
                UNION 
                select 
                    3 s, 
                    '코스피200' name, 
                    decode(f_invest_code, '8000', '개인', '9000', '외국인', '9001', '외국인', '기관') invest, 
                    round((sum(F_SELL_turnover) / 100000000) - (sum(F_BUY_turnover) / 100000000)) as org_amt 
                from t_invest 
                where F_PART_CODE = '029' 
                    and F_INVEST_CODE in('1000', '2000', '3000', '3100', '4000', '5000', '6000', '7000', '8000', '9000', '9001') 
                group by decode(f_invest_code, '8000', '개인', '9000', '외국인', '9001', '외국인', '기관') 
            ) 
            group by name, s 
            order by s";

        public override IDataRequest buildData()
        {

            string bufQuery = "";

            bufQuery += String.Format(queryToday);

            mRequestQuery.Add("", bufQuery);

            return this;
        }
    }
}
