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 Nxt_SANGSE_PAN : Nxt_ADataObject
    {

        internal Nxt_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, 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, 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}'";

        // 시가_NXT
        string queryNXT_KOSPI1 = @"SELECT a.f_stock_code stock_code, b.f_stock_name stock_name, a.f_curr_price curr_price, 
                                          case when a.f_chg_type = '1' then '상한' when a.f_chg_type = '2' then '상승' when a.f_chg_type = '3' then '보합' 
                                               when a.f_chg_type = '4' then '하한' when a.f_chg_type = '5' then '하락' end chg_type, a.f_net_chg net_chg,
                                          round((a.f_net_chg / case when a.f_chg_type = '1' then (a.f_curr_price - a.f_net_chg) 
			                                                        when a.f_chg_type = '2' then (a.f_curr_price - a.f_net_chg) 
			                                                        when a.f_chg_type = '3' then (a.f_curr_price) 
			     			                                        when a.f_chg_type = '4' then -1*(a.f_curr_price + a.f_net_chg) 
			                                                        when a.f_chg_type = '5' then -1*(a.f_curr_price + a.f_net_chg) end) * 100, 2) rate ,
                                          a.f_init_price, if (c.f_final_price = 0, round(((a.f_init_price - c.f_base_price)  / c.f_base_price)  * 100,2),
                                                                                   round(((a.f_init_price - c.f_final_price) / c.f_final_price) * 100,2)) init_rate,
                                          a.f_high_price, if (c.f_final_price = 0, round(((a.f_high_price - c.f_base_price)  / c.f_base_price)  * 100,2),
                                                                                   round(((a.f_high_price - c.f_final_price) / c.f_final_price) * 100,2)) high_rate,
                                          a.f_low_price,  if (c.f_final_price = 0, round(((a.f_low_price  - c.f_base_price)  / c.f_base_price)  * 100,2),
                                                                                   round(((a.f_low_price  - c.f_final_price) / c.f_final_price) * 100,2)) low_rate
			                         FROM n_online a, n_stock b, n_batch_his c
			                        WHERE a.f_stock_code = b.f_stock_code
                                      and a.f_stock_code = c.f_stock_code
			                          and a.f_curr_price != 0
			                          and a.f_stock_code = b.F_STOCK_CODE
			                          and c.f_data_day = (select max(f_data_day) from n_batch_his where f_stock_code = a.f_stock_code)
			                          and a.f_stock_code = '{0}'";

        // 시가_코스닥_NXT
        string queryNXT_KOSDAQ1 = @"SELECT a.f_stock_code stock_code, b.f_stock_name stock_name, a.f_curr_price curr_price, 
                                          case when a.f_chg_type = '1' then '상한' when a.f_chg_type = '2' then '상승' when a.f_chg_type = '3' then '보합' 
                                               when a.f_chg_type = '4' then '하한' when a.f_chg_type = '5' then '하락' end chg_type, a.f_net_chg net_chg,
                                          round((a.f_net_chg / case when a.f_chg_type = '1' then (a.f_curr_price - a.f_net_chg) 
			                                                        when a.f_chg_type = '2' then (a.f_curr_price - a.f_net_chg) 
			                                                        when a.f_chg_type = '3' then (a.f_curr_price) 
			     			                                        when a.f_chg_type = '4' then -1*(a.f_curr_price + a.f_net_chg) 
			                                                        when a.f_chg_type = '5' then -1*(a.f_curr_price + a.f_net_chg) end) * 100, 2) rate ,
                                          a.f_init_price, if (c.f_final_price = 0, round(((a.f_init_price - c.f_base_price)  / c.f_base_price)  * 100,2),
                                                                                   round(((a.f_init_price - c.f_final_price) / c.f_final_price) * 100,2)) init_rate,
                                          a.f_high_price, if (c.f_final_price = 0, round(((a.f_high_price - c.f_base_price)  / c.f_base_price)  * 100,2),
                                                                                   round(((a.f_high_price - c.f_final_price) / c.f_final_price) * 100,2)) high_rate,
                                          a.f_low_price,  if (c.f_final_price = 0, round(((a.f_low_price  - c.f_base_price)  / c.f_base_price)  * 100,2),
                                                                                   round(((a.f_low_price  - c.f_final_price) / c.f_final_price) * 100,2)) low_rate
			                         FROM n_kosdaq_online a, n_kosdaq_stock b, n_kosdaq_batch_his c
			                        WHERE a.f_stock_code = b.f_stock_code
                                      and a.f_stock_code = c.f_stock_code
			                          and a.f_curr_price != 0
			                          and a.f_stock_code = b.F_STOCK_CODE
			                          and c.f_data_day = (select max(f_data_day) from n_kosdaq_batch_his where f_stock_code = a.f_stock_code)
			                          and a.f_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}'";

        // 액면가_NXT
        string queryNXT_KOSPI2 =
            @"SELECT a.f_stock_code stock_code, b.f_stock_name stock_name, a.f_curr_price curr_price, 
                     case when a.f_chg_type = '1' then '상한' when a.f_chg_type = '2' then '상승' when a.f_chg_type = '3' then '보합' 
                          when a.f_chg_type = '4' then '하한' when a.f_chg_type = '5' then '하락' end chg_type, a.f_net_chg chg_type, a.f_net_chg net_chg, 
		             round((a.f_net_chg / case when a.f_chg_type = '1' then (a.f_curr_price - a.f_net_chg) 
			                                   when a.f_chg_type = '2' then (a.f_curr_price - a.f_net_chg) 
                                  			   when a.f_chg_type = '3' then (a.f_curr_price) 
			                                   when a.f_chg_type = '4' then -1*(a.f_curr_price + a.f_net_chg) 
			                                   when a.f_chg_type = '5' then -1*(a.f_curr_price + a.f_net_chg) end) * 100, 2) rate ,
			         b.f_list_price list_price, b.f_capital_price capital_price, b.f_list_num * a.f_curr_price siga_price, b.rank_num 
			    FROM n_online a, 
			         (select z.f_stock_code f_stock_code, z.f_stock_name, z.f_list_price, z.f_capital_price, z.f_list_num, 
     			             rank() over(order by(z.f_list_num * y.f_curr_price) desc) rank_num 
	                    from n_online y, n_stock z 
 	                   where y.f_stock_code = z.f_stock_code 
                         and z.f_stop_gubun = 'N') b 
	           WHERE a.f_stock_code = b.f_stock_code 
			     AND a.f_stock_code = '{0}'";

        // 액면가_코스닥_NXT
        string queryNXT_KOSDAQ2 =
            @"SELECT a.f_stock_code stock_code, b.f_stock_name stock_name, a.f_curr_price curr_price, 
                     case when a.f_chg_type = '1' then '상한' when a.f_chg_type = '2' then '상승' when a.f_chg_type = '3' then '보합' 
                          when a.f_chg_type = '4' then '하한' when a.f_chg_type = '5' then '하락' end chg_type, a.f_net_chg chg_type, a.f_net_chg net_chg, 
		             round((a.f_net_chg / case when a.f_chg_type = '1' then (a.f_curr_price - a.f_net_chg) 
			                                   when a.f_chg_type = '2' then (a.f_curr_price - a.f_net_chg) 
                                  			   when a.f_chg_type = '3' then (a.f_curr_price) 
			                                   when a.f_chg_type = '4' then -1*(a.f_curr_price + a.f_net_chg) 
			                                   when a.f_chg_type = '5' then -1*(a.f_curr_price + a.f_net_chg) end) * 100, 2) rate ,
			         b.f_list_price list_price, b.f_capital_price capital_price, b.f_list_num * a.f_curr_price siga_price, b.rank_num
			    FROM n_kosdaq_online a, 
			         (select z.f_stock_code f_stock_code, z.f_stock_name, z.f_list_price, z.f_capital_price, z.f_list_num, 
     			             rank() over(order by(z.f_list_num * y.f_curr_price) desc) rank_num 
	                    from n_kosdaq_online y, n_kosdaq_stock z 
 	                   where y.f_stock_code = z.f_stock_code 
                         and z.f_stop_gubun = 'N') b 
	           WHERE a.f_stock_code = b.f_stock_code 
			     AND a.f_stock_code = '{0}'";

        // PBR
        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}'";

        // PBR_코스닥
        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}'";

        // PBR_NXT
        string queryNXT_KOSPI3 =
            @"SELECT a.f_stock_code stock_code, b.f_stock_name stock_name, a.f_curr_price curr_price, 
                     case when a.f_chg_type = '1' then '상한' when a.f_chg_type = '2' then '상승' when a.f_chg_type = '3' then '보합' 
                          when a.f_chg_type = '4' then '하한' when a.f_chg_type = '5' then '하락' end chg_type, a.f_net_chg chg_type, a.f_net_chg net_chg, 
                     round((a.f_net_chg / case when a.f_chg_type = '1' then (a.f_curr_price - a.f_net_chg) 
			                                   when a.f_chg_type = '2' then (a.f_curr_price - a.f_net_chg) 
                                  			   when a.f_chg_type = '3' then (a.f_curr_price) 
			                                   when a.f_chg_type = '4' then -1*(a.f_curr_price + a.f_net_chg) 
			                                   when a.f_chg_type = '5' then -1*(a.f_curr_price + a.f_net_chg) end) * 100, 2) rate ,
                      0 pbr, 0 per, 0 bps, 0 eps 
				FROM n_online a, n_stock b
               WHERE a.f_stock_code = b.f_stock_code
				 AND a.f_stock_code = '{0}'";

        // PBR_코스닥
        string queryNXT_KOSDAQ3 =
            @"SELECT a.f_stock_code stock_code, b.f_stock_name stock_name, a.f_curr_price curr_price, 
                     case when a.f_chg_type = '1' then '상한' when a.f_chg_type = '2' then '상승' when a.f_chg_type = '3' then '보합' 
                          when a.f_chg_type = '4' then '하한' when a.f_chg_type = '5' then '하락' end chg_type, a.f_net_chg chg_type, a.f_net_chg net_chg, 
                     round((a.f_net_chg / case when a.f_chg_type = '1' then (a.f_curr_price - a.f_net_chg) 
			                                   when a.f_chg_type = '2' then (a.f_curr_price - a.f_net_chg) 
                                  			   when a.f_chg_type = '3' then (a.f_curr_price) 
			                                   when a.f_chg_type = '4' then -1*(a.f_curr_price + a.f_net_chg) 
			                                   when a.f_chg_type = '5' then -1*(a.f_curr_price + a.f_net_chg) end) * 100, 2) rate ,
                      0 pbr, 0 per, 0 bps, 0 eps 
				FROM n_kosdaq_online a, n_kosdaq_stock b
               WHERE a.f_stock_code = b.f_stock_code
				 AND a.f_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}' ";

        // 거래량_NXT
        string queryNXT_KOSPI4 = @"SELECT a.f_stock_code stock_code, z.f_stock_name stock_name, a.f_curr_price curr_price, 
        case when a.f_chg_type = '1' then '상한' when a.f_chg_type = '2' then '상승' when a.f_chg_type = '3' then '보합' when a.f_chg_type = '4' then '하한' when a.f_chg_type = '5' then '하락' end chg_type, a.f_net_chg net_chg, 
        round((a.f_net_chg / 
		    case when a.f_chg_type = '1' then (a.f_curr_price - a.f_net_chg) 
			     when a.f_chg_type = '2' then (a.f_curr_price - a.f_net_chg) 
 			     when a.f_chg_type = '3' then (a.f_curr_price) 
			     when a.f_chg_type = '4' then -1*(a.f_curr_price + a.f_net_chg) 
			     when a.f_chg_type = '5' then -1*(a.f_curr_price + a.f_net_chg) end) * 100, 2) rate ,
        a.f_net_vol net_vol, a.f_net_turnover net_turnover, 0 avg_5, 0 avg_20 
        FROM n_online a, n_stock z
        WHERE a.f_stock_code = z.f_stock_code
        AND   a.f_stock_code = '{1}' ";

        // 거래량_코스닥
        string queryNXT_KOSDAQ4 = @"SELECT a.f_stock_code stock_code, z.f_stock_name stock_name, a.f_curr_price curr_price, 
        case when a.f_chg_type = '1' then '상한' when a.f_chg_type = '2' then '상승' when a.f_chg_type = '3' then '보합' when a.f_chg_type = '4' then '하한' when a.f_chg_type = '5' then '하락' end chg_type, a.f_net_chg net_chg, 
        round((a.f_net_chg / 
		    case when a.f_chg_type = '1' then (a.f_curr_price - a.f_net_chg) 
			     when a.f_chg_type = '2' then (a.f_curr_price - a.f_net_chg) 
 			     when a.f_chg_type = '3' then (a.f_curr_price) 
			     when a.f_chg_type = '4' then -1*(a.f_curr_price + a.f_net_chg) 
			     when a.f_chg_type = '5' then -1*(a.f_curr_price + a.f_net_chg) end) * 100, 2) rate ,
        a.f_net_vol net_vol, a.f_net_turnover net_turnover, 0 avg_5, 0 avg_20 
        FROM n_kosdaq_online a, n_kosdaq_stock z
        WHERE a.f_stock_code = z.f_stock_code
        AND   a.f_stock_code = '{1}' ";

        #endregion

        public override Nxt_IDataRequest Nxt_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;
                case 국내시장종류.NXT_KOSPI:
                    switch (this.mDataType2)
                    {
                        case 상세판데이터분류.시가:
                            bufQuery = String.Format(queryNXT_KOSPI1, mStockCode);
                            break;
                        case 상세판데이터분류.액면가:
                            bufQuery = String.Format(queryNXT_KOSPI2, mStockCode);
                            break;
                        case 상세판데이터분류.PBR:
                            bufQuery = String.Format(queryNXT_KOSPI3, mStockCode);
                            break;
                        case 상세판데이터분류.거래량:
                            bufQuery = String.Format(queryNXT_KOSPI4, mStockCode, mStockCode);
                            break;
                    }
                    break;
                case 국내시장종류.NXT_KOSDAQ:

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

            Nxt_mRequestQuery.Add("", bufQuery);

            return this;
        }
    }
}
