using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace MMoneyCoderSharp.Data.Request.봉차트
{
    internal class CandleStockKOSPI : ACandleData
    {
        public CandleStockKOSPI(DBDefine.봉차트세부종목 recvDataType, DBDefine.데이터기간 recvDataLength, string recvStockCode) : base(recvDataType, recvDataLength)
        {

            this.mStockCode = recvStockCode;

        }
        
        protected override void setQuery()
        {
              
            #region 예상지수

        this.queryForecastToday = @"SELECT DISTINCT 
                b.f_stock_code stock_code, 
                b.f_stock_nickname nickname, 
                b.F_STOCK_WANNAME wanname, 
                a.f_fore_price curr_price, 
                a.f_fore_vol net_vol, 
                c.f_final_price final_price, 
                ABS(a.f_fore_price - c.f_final_price) net_chg, 
                '' f_net_chg, 
                case when(a.f_fore_price - c.f_final_price) > 0 then '+' 
                     when(a.f_fore_price - c.f_final_price) = 0 then '' 
                     when(a.f_fore_price - c.f_final_price) < 0 then '-' end chg_type, 
                round(((a.f_fore_price - c.f_final_price) / c.f_final_price) * 100, 2)  rate 
            FROM t_online1_call a, t_stock b, t_batch_day c 
            WHERE b.f_stock_code = a.f_stock_code 
                AND c.f_stock_code = a.f_stock_code 
                AND b.f_mkt_halt = 'N' 
                and b.f_STOCK_wanname = '{0}'";

            //////////this.queryForecastToday = @"SELECT DISTINCT 
            //////////    b.f_stock_code stock_code, 
            //////////    b.f_stock_nickname nickname, 
            //////////    b.F_STOCK_WANNAME wanname, 
            //////////    a.f_fore_price curr_price, 
            //////////    a.f_fore_vol net_vol, 
            //////////    c.f_final_price final_price, 
            //////////    a.f_fore_price - c.f_final_price net_chg, 
            //////////    a.f_net_chg, 
            //////////    case when(a.f_fore_price - c.f_final_price) > 0 then '+' when(a.f_fore_price - c.f_final_price) = 0 then '' when(a.f_fore_price - c.f_final_price) < 0 then '-' end chg_type, 
            //////////    round(((a.f_fore_price - c.f_final_price) / c.f_final_price) * 100, 2)  rate 
            //////////FROM t_online1 a, t_stock b, t_batch_day c 
            //////////WHERE b.f_stock_code = a.f_stock_code 
            //////////    AND c.f_stock_code = a.f_stock_code 
            //////////    AND b.f_mkt_halt = 'N' 
            //////////    and b.f_STOCK_wanname = '{0}'";

            //====================
            //this.queryForecastLast = @"select 
            //    F_DATA_DATE, 
            //    F_STOCK_CODE, 
            //    f_stock_wanname, 
            //    F_CHG_TYPE, 
            //    F_NET_CHG, 
            //    F_HIGH_PRICE, 
            //    F_LOW_PRICE, 
            //    F_INIT_PRICE, 
            //    F_CURR_PRICE, 
            //    F_MA5_PRICE, 
            //    F_MA20_PRICE 
            //from ( 
            //    select  a.F_DATA_DATE, a.F_STOCK_CODE, b.f_stock_wanname, 
            //        a.F_CHG_TYPE, a.F_NET_CHG, 
            //        a.F_HIGH_PRICE, a.F_LOW_PRICE, a.F_INIT_PRICE, 
            //        a.F_CURR_PRICE, a.F_MA5_PRICE, a.F_MA20_PRICE 
            //    from t_candle_history a, t_stock b 
            //    where a.f_stock_code = b.f_stock_code 
            //        and b.f_stock_wanname = '{1}' 
            //        and b.f_mkt_halt = 'N' 
            //    order by a.F_DATA_DATE desc
            //) 
            //where rownum <= {0} order by F_DATA_DATE asc";
            this.queryForecastLast = @"select
                           O.OPEN_DAY  F_DATA_DATE,  
                           X.F_STOCK_CODE,
                           X.f_stock_wanname, 
                           X.F_CHG_TYPE, 
                           X.F_NET_CHG, 
                           X.F_HIGH_PRICE, 
                           X.F_LOW_PRICE,
                           X.F_INIT_PRICE, 
                           X.F_CURR_PRICE,
                           X.F_MA5_PRICE,
                           X.F_MA20_PRICE 
            		from(select a.F_DATA_DATE, a.F_STOCK_CODE, b.f_stock_wanname, 
                           a.F_CHG_TYPE, a.F_NET_CHG, 
                           a.F_HIGH_PRICE, a.F_LOW_PRICE, a.F_INIT_PRICE, 
                           a.F_CURR_PRICE, a.F_MA5_PRICE, a.F_MA20_PRICE 
            			from t_candle_history a, t_stock b 
                       where a.f_stock_code = b.f_stock_code 
                         and b.f_stock_wanname = '{1}' 
                         and b.f_mkt_halt = 'N'    
                    order by a.F_DATA_DATE desc) X, 
                   (select open_day from(select open_day from v_open_day 
                  order by open_day desc) 
            where rownum <= {0}) O 
            where X.f_data_date(+) = o.open_day 
            order by O.OPEN_DAY";

            //=================================

            #endregion

            #region 지수

            this.queryIndexNow = @"SELECT DISTINCT 
                b.f_stock_code stock_code, 
                b.F_STOCK_WANNAME stock_name , 
                a.f_curr_price curr_price, 
                ABS(a.f_net_chg) net_chg, 
                a.f_net_vol, 
                decode(a.f_chg_type, '1', '상한', '2', '상승', '3', '보합', '4', '하한', '5', '하락') chg_type, 
                round((a.f_net_chg / decode(a.f_chg_type, '1', (a.f_curr_price - a.f_net_chg), '2', (a.f_curr_price - a.f_net_chg), '3', (a.f_curr_price), '4', -1*(a.f_curr_price + a.f_net_chg), '5', -1*(a.f_curr_price + a.f_net_chg))) * 100, 2) rate 
            FROM t_online1 a, t_stock b 
            WHERE b.f_mkt_halt = 'N' 
                AND a.f_curr_price < > 0 
                AND a.f_stock_code = b.f_stock_code 
                AND b.f_stock_wanname = '{0}'";

            this.queryIndexToday = @"select 
                a.F_DATA_DAY, 
                a.F_DATA_TIME, 
                a.F_STOCK_CODE, 
                b.f_stock_wanname, 
                a.F_CURR_PRICE, 
                a.F_CHG_TYPE, 
                a.F_NET_CHG, 
                a.F_NET_VOL, 
                a.F_HIGH_PRICE, 
                a.F_LOW_PRICE, 
                a.F_INIT_PRICE 
            from t_online_his_5M a, t_stock b 
            WHERE b.f_mkt_halt = 'N' 
                AND a.f_curr_price < > 0 
                AND a.f_stock_code = b.f_stock_code 
                AND b.f_stock_wanname = '{0}' 
                and a.f_data_day = ( 
                    select max(open_day) from v_open_day 
                ) 
            ORDER BY a.F_DATA_TIME";
        
            this.queryIndexLast = @"select O.OPEN_DAY,   X.F_STOCK_CODE,  X.f_stock_wanname, 
            X.F_CHG_TYPE, X.F_NET_CHG, 
            X.F_HIGH_PRICE, X.F_LOW_PRICE, X.F_INIT_PRICE, 
            X.F_CURR_PRICE, X.F_MA5_PRICE, X.F_MA20_PRICE 
            from(select  a.F_DATA_DATE, a.F_STOCK_CODE, b.f_stock_wanname, 
                a.F_CHG_TYPE, a.F_NET_CHG, 
                a.F_HIGH_PRICE, a.F_LOW_PRICE, a.F_INIT_PRICE, 
                a.F_CURR_PRICE, a.F_MA5_PRICE, a.F_MA20_PRICE 
                from t_candle_history a, t_stock b 
    where a.f_stock_code = b.f_stock_code 
    and b.f_stock_wanname = '{1}' 
        and b.f_mkt_halt = 'N' 
        order by a.F_DATA_DATE desc) X, 
            (select open_day from(select open_day from v_open_day 
                order by open_day desc) 
    where rownum <= {0}) O 
    where X.f_data_date(+) = o.open_day 
    order by O.OPEN_DAY";

        #endregion

            #region 거래량
        
        this.queryQuantityToday = @"select 
                a.F_DATA_DAY, 
                a.F_DATA_TIME, 
                a.F_STOCK_CODE, 
                b.f_stock_wanname, 
                a.F_CURR_PRICE, 
                a.F_CHG_TYPE, 
                a.F_NET_CHG, 
                a.F_NET_VOL, 
                a.F_HIGH_PRICE, 
                a.F_LOW_PRICE,
                a.F_INIT_PRICE 
            from t_online_his_5M a, t_stock b 
            WHERE b.f_mkt_halt = 'N' 
                AND a.f_curr_price < > 0 
                AND a.f_stock_code = b.f_stock_code 
                AND b.f_stock_wanname = '{0}' 
                and a.f_data_day = (select max(open_day) from v_open_day) 
            ORDER BY a.F_DATA_TIME";


            this.queryQuantityLast = @"select O.OPEN_DAY,   X.F_STOCK_CODE,  X.f_stock_wanname, 
			X.F_CHG_TYPE, X.F_NET_CHG, X.F_NET_VOL, 
			X.F_HIGH_PRICE, X.F_LOW_PRICE, X.F_INIT_PRICE, 
			X.F_CURR_PRICE, X.F_MA5_PRICE, X.F_MA20_PRICE 
			from(select  a.F_DATA_DATE, a.F_STOCK_CODE, b.f_stock_wanname, 
				a.F_CHG_TYPE, a.F_NET_CHG, a.F_NET_VOL, 
				a.F_HIGH_PRICE, a.F_LOW_PRICE, a.F_INIT_PRICE, 
				a.F_CURR_PRICE, a.F_MA5_PRICE, a.F_MA20_PRICE 
				from t_candle_history a, t_stock b 
	where a.f_stock_code = b.f_stock_code 
	and b.f_stock_wanname = '{1}' 
		and b.f_mkt_halt = 'N'    
		order by a.F_DATA_DATE desc) X, 
			(select open_day from(select open_day from v_open_day 
				order by open_day desc) 
    where rownum <= {0}) O 
	where X.f_data_date(+) = o.open_day 
	order by O.OPEN_DAY";

    }

    #endregion

        }
}
