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 SANGSE_PAN : ADataObject
    {

        internal SANGSE_PAN(국내시장종류 recvDataType, 상세판데이터분류 recvDataType2, string recvStockCode)
        {
            this.mDataType = recvDataType;
            this.mDataType2 = recvDataType2;
            this.mStockCode = recvStockCode;
        }

        국내시장종류 mDataType;

        상세판데이터분류 mDataType2;

        string mStockCode = "";

        #region 쿼리
   //     string queryKOSPI1 = @"SELECT a.stock_code, a.stock_name, a.curr_price, a.chg_type, a.net_chg, a.rate,
			//a.init_price, round(((a.init_price - a.final_price) / a.final_price) * 100, 2) init_rate,
			//a.high_price, round(((a.high_price - a.final_price) / a.final_price) * 100, 2) high_rate,
			//a.low_price,  round(((a.low_price  - a.final_price) / a.final_price) * 100, 2) low_rate
			//FROM V_2 a
			//WHERE a.stock_code = '{0}'";

        string queryKOSPI1 = @"SELECT a.stock_code, a.stock_name, a.curr_price, a.chg_type, a.net_chg, a.rate,
                                      a.init_price, decode(a.final_price, 0, round(((a.init_price - a.base_price) / a.base_price)  * 100,2),
                                                                             round(((a.init_price - a.final_price) / a.final_price)  * 100,2)) init_rate,
                                      a.high_price, decode(a.final_price, 0, round(((a.high_price - a.base_price) / a.base_price)  * 100,2),
                                                                             round(((a.high_price - a.final_price) / a.final_price)  * 100,2)) high_rate,
                                      a.low_price,  decode(a.final_price, 0, round(((a.low_price  - a.base_price) / a.base_price)  * 100,2),
                                                                             round(((a.low_price  - a.final_price) / a.final_price)  * 100,2)) low_rate
			FROM V_2 a
			WHERE a.stock_code = '{0}'";


   //     string queryKOSDAQ1 = @"SELECT a.stock_code, a.stock_name, a.curr_price, a.chg_type, a.net_chg, a.rate,
			//a.init_price, round(((a.init_price - a.final_price) / a.final_price) * 100, 2) init_rate,
			//a.high_price, round(((a.high_price - a.final_price) / a.final_price) * 100, 2) high_rate,
			//a.low_price,  round(((a.low_price  - a.final_price) / a.final_price) * 100, 2) low_rate
			//FROM V_2_kosdaq a 
			//WHERE a.stock_code = '{0}'";

        string queryKOSDAQ1 = @"SELECT a.stock_code, a.stock_name, a.curr_price, a.chg_type, a.net_chg, a.rate,
                                      a.init_price, decode(a.final_price, 0, round(((a.init_price - a.base_price) / a.base_price)  * 100,2),
                                                                             round(((a.init_price - a.final_price) / a.final_price)  * 100,2)) init_rate,
                                      a.high_price, decode(a.final_price, 0, round(((a.high_price - a.base_price) / a.base_price)  * 100,2),
                                                                             round(((a.high_price - a.final_price) / a.final_price)  * 100,2)) high_rate,
                                      a.low_price,  decode(a.final_price, 0, round(((a.low_price  - a.base_price) / a.base_price)  * 100,2),
                                                                             round(((a.low_price  - a.final_price) / a.final_price)  * 100,2)) low_rate
			FROM V_2_kosdaq a 
			WHERE a.stock_code = '{0}'";


        string queryKOSPI2 = @"SELECT a.stock_code, a.stock_name, a.curr_price, a.chg_type, a.net_chg, a.rate,
			a.list_price, a.capital_price, a.siga_price, b.rank_num
			FROM V_2 A, 
			(select z.f_stock_code stock_code, 
				    rank() over(order by(z.f_list_num * y.f_curr_price) desc) rank_num 
			   from t_online1 y, t_stock z 
	 	      where y.f_stock_code = z.f_stock_code and z.f_mkt_halt = 'N') B 
			WHERE a.stock_code = b.stock_code 
			and a.stock_code = '{0}'";

        string queryKOSDAQ2 = @"SELECT a.stock_code, a.stock_name, a.curr_price, a.chg_type, a.net_chg, a.rate,
			a.list_price, a.capital_price, a.siga_price, b.rank_num 
			FROM V_2_kosdaq A,
			(select z.f_stock_code stock_code, 
				rank() over(order by(z.f_list_num * y.f_curr_price) desc) rank_num 
				from t_kosdaq_online1 y, t_kosdaq_stock z 
		where y.f_stock_code = z.f_stock_code and z.f_mkt_halt = 'N') B 
			WHERE a.stock_code = b.stock_code 
			and a.stock_code = '{0}'";

        string queryKOSPI3 = @"SELECT a.stock_code, a.stock_name, a.curr_price, a.chg_type, a.net_chg, a.rate, 
				a.pbr, a.per, a.bps, a.eps 
				FROM V_2 A 
				WHERE a.stock_code = '{0}'";

        string queryKOSDAQ3 = @"SELECT a.stock_code, a.stock_name, a.curr_price, a.chg_type, a.net_chg, a.rate, 
				a.pbr, a.per, a.bps, a.eps 
				FROM V_2_kosdaq A 
				WHERE a.stock_code = '{0}'";

        string queryKOSPI4 = @"SELECT a.stock_code, a.stock_name, a.curr_price, a.chg_type, a.net_chg, a.rate, 
        a.net_vol, a.net_turnover, nvl(c.avg_5,0) avg_5, nvl(d.avg_20,0) avg_20 
        FROM V_2 a, 
        (SELECT b.f_stock_code, round((SUM(a.F_CURR_PRICE) + b.f_curr_price) / 5) avg_5 
        	FROM (SELECT f_stock_code, f_curr_price 
        		FROM T_CANDLE_HISTORY 
        		WHERE F_DATA_DATE < (select max(open_day) from v_open_day) 
        		AND f_stock_code = '{0}' 
        		AND ROWNUM < 5 
        		ORDER BY F_DATA_DATE DESC) a, t_online1 b 
        	WHERE a.f_stock_code = b.f_stock_code 
        	GROUP by b.f_stock_code, b.f_curr_price) c, 
        (SELECT b.f_stock_code, round((SUM(a.F_CURR_PRICE) + b.f_curr_price) / 20) avg_20 
        	FROM (SELECT f_stock_code, b1.f_curr_price 
        		FROM T_CANDLE_HISTORY  b1 
        		WHERE F_DATA_DATE < (select max(open_day) from v_open_day) 
        		AND f_stock_code = '{1}' 
        		AND ROWNUM < 20 
        		ORDER BY F_DATA_DATE DESC) a, t_online1 b 
        	WHERE a.f_stock_code = b.f_stock_code 
        	GROUP by b.f_stock_code, b.f_curr_price) d 
        WHERE a.stock_code = c.f_stock_code (+)
        AND   a.stock_code = d.f_stock_code (+)
        AND   a.stock_code = '{1}' ";

        string queryKOSDAQ4 = @"SELECT a.stock_code, a.stock_name, a.curr_price, a.chg_type, a.net_chg, a.rate, 
        a.net_vol, a.net_turnover, nvl(c.avg_5,0) avg_5, nvl(d.avg_20,0) avg_20 
        FROM V_2_kosdaq a, 
        (SELECT b.f_stock_code, round((SUM(a.F_CURR_PRICE) + b.f_curr_price) / 5) avg_5 
        	FROM (SELECT f_stock_code, f_curr_price 
        		FROM T_kosdaq_CANDLE_HISTORY 
        		WHERE F_DATA_DATE < (select max(open_day) from v_open_day) 
        		AND f_stock_code = '{0}' 
        		AND ROWNUM < 5 
        		ORDER BY F_DATA_DATE DESC) a, t_kosdaq_online1 b 
        	WHERE a.f_stock_code = b.f_stock_code 
        	GROUP by b.f_stock_code, b.f_curr_price) c, 
        (SELECT b.f_stock_code, round((SUM(a.F_CURR_PRICE) + b.f_curr_price) / 20) avg_20 
        	FROM (SELECT f_stock_code, b1.f_curr_price 
        		FROM T_kosdaq_CANDLE_HISTORY  b1 
        		WHERE F_DATA_DATE < (select max(open_day) from v_open_day) 
        		AND f_stock_code = '{1}' 
        		AND ROWNUM < 20 
        		ORDER BY F_DATA_DATE DESC) a, t_kosdaq_online1 b 
        	WHERE a.f_stock_code = b.f_stock_code 
        	GROUP by b.f_stock_code, b.f_curr_price) d 
        WHERE a.stock_code = c.f_stock_code (+)
        AND   a.stock_code = d.f_stock_code (+)
        AND   a.stock_code = '{1}' ";



        #endregion

        public override IDataRequest buildData()
        {

            string bufQuery = "";

            switch (this.mDataType)
            {
                case 국내시장종류.KOSPI:
                    switch (this.mDataType2)
                    {
                        case 상세판데이터분류.시가:
                            bufQuery = String.Format(queryKOSPI1, mStockCode);
                            break;
                        case 상세판데이터분류.액면가:
                            bufQuery = String.Format(queryKOSPI2, mStockCode);
                            break;
                        case 상세판데이터분류.PBR:
                            bufQuery = String.Format(queryKOSPI3, mStockCode);
                            break;
                        case 상세판데이터분류.거래량:
                            bufQuery = String.Format(queryKOSPI4, mStockCode, mStockCode);
                            break;
                    }
                    break;
                case 국내시장종류.KOSDAQ:

                    switch (this.mDataType2)
                    {
                        case 상세판데이터분류.시가:
                            bufQuery = String.Format(queryKOSDAQ1, mStockCode);
                            break;
                        case 상세판데이터분류.액면가:
                            bufQuery = String.Format(queryKOSDAQ2, mStockCode);
                            break;
                        case 상세판데이터분류.PBR:
                            bufQuery = String.Format(queryKOSDAQ3, mStockCode);
                            break;
                        case 상세판데이터분류.거래량:
                            bufQuery = String.Format(queryKOSDAQ4, mStockCode, mStockCode);
                            break;
                    }
                    break;
            }

            mRequestQuery.Add("", bufQuery);

            return this;
        }
    }
}
