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 DataObject_3PAN : ADataObject
    {
     
        internal DataObject_3PAN(판3데이터분류 recvDataType)
        {
            this.mDataType = recvDataType;
        }

        판3데이터분류 mDataType;

        #region "쿼리"

        string query주요지수200 = @"SELECT 'KOSPI' name, f_part_idx / 100 part_idx, f_chg_type chg_type, ABS(f_part_chg / 100) part_chg, 
		round(f_part_chg / decode(f_chg_type, '+', f_part_idx - f_part_chg, 
			                                  '-', f_part_idx + f_part_chg, 1) * 100, 2) rate 
		FROM t_index 
		WHERE f_part_code = '001' 
		union

		SELECT '코스닥' name, f_part_idx / 100 part_idx, f_chg_type chg_type, ABS(f_part_chg / 100) part_chg, 
		round(f_part_chg / decode(f_chg_type, '+', f_part_idx - f_part_chg, 
			'-', f_part_idx + f_part_chg, 1) * 100, 2) rate 
		FROM T_KOSDAQ_INDEX 
		WHERE f_part_code = '001' 
		union 

		SELECT '코스피200' name, f_part_idx / 100 part_idx, f_chg_type chg_type, ABS(f_part_chg / 100) part_chg, 
		round(f_part_chg / decode(f_chg_type, '+', f_part_idx - f_part_chg, 
			'-', f_part_idx + f_part_chg, 1) * 100, 2) rate 
		FROM T_200_INDEX 
		WHERE f_part_code = '029'";

        string query주요지수선물 = @"SELECT 'KOSPI' name,f_part_idx/100 part_idx, f_chg_type chg_type, f_part_chg/100 part_chg, 
		round(f_part_chg / decode(f_chg_type, '+', (f_part_idx / 100) - (f_part_chg / 100), 
		   '-', (f_part_idx / 100) + (f_part_chg / 100), 1), 2) rate 
		FROM t_index 
		WHERE f_part_code = '001' 
		union 
		SELECT 'KOSQ' name, f_part_idx / 100 part_idx, f_chg_type chg_type, f_part_chg / 100 part_chg, 
		round(f_part_chg / decode(f_chg_type, '+', (f_part_idx / 100) - (f_part_chg / 100), 
			'-', (f_part_idx / 100) + (f_part_chg / 100), 1), 2) rate 
		FROM T_KOSDAQ_INDEX 
		WHERE f_part_code = '001' 
		union 
		SELECT '선물' name, part_idx part_idx, chg_type, ABS(part_chg) part_chg, 
		       ABS(round(part_chg / decode(chg_type, '+', (part_idx / 100) - (part_chg / 100), 
		   	   '-', (part_idx / 100) + (part_chg / 100), 1), 2)) rate 
		FROM(SELECT decode(a.f_curr_price,0, b.F_JUN_LAST_PRICE/100, a.f_curr_price/100) part_idx,                    
                decode(a.f_curr_price, 0, 0, (a.f_curr_price - b.F_JUN_LAST_PRICE*100)/100) part_chg,
                decode(a.f_curr_price, 0, ' ', decode(sign(a.f_curr_price -b.F_JUN_LAST_PRICE*100),1,'+',-1,'-',0,' ')) chg_type
           FROM t_sunmul_online a, t_sunmul_batch b
          WHERE a.f_stock_code = b.f_stock_code
            AND a.f_stock_seq = 1
            AND b.f_market_date = (select max(OPEN_DAY) from v_open_day)
            AND b.F_MONTH_GUBUN = '1')";


        string query예상지수200 = @"SELECT 'KOSPI' name,f_part_idx/100 part_idx, f_chg_type chg_type, ABS(f_part_chg/100) part_chg, 
		round(f_part_chg / decode(f_chg_type, '+', f_part_idx - f_part_chg, 
			'-', f_part_idx + f_part_chg, 1) * 100, 2) rate 
		FROM t_fo_index 
		WHERE f_part_code = '001' 
		UNION 
		SELECT '코스닥' name, f_part_idx / 100 part_idx, f_chg_type chg_type, ABS(f_part_chg / 100) part_chg, 
		round(f_part_chg / decode(f_chg_type, '+', f_part_idx - f_part_chg, 
			'-', f_part_idx + f_part_chg, 1) * 100, 2) rate 
		FROM T_KOSDAQ_FO_INDEX 
		WHERE f_part_code = '001' 
		UNION 
		SELECT '코스피200' name, f_part_idx / 100 part_idx, f_chg_type chg_type, ABS(f_part_chg / 100) part_chg, 
		round(f_part_chg / decode(f_chg_type, '+', f_part_idx - f_part_chg, 
			'-', f_part_idx + f_part_chg, 1) * 100, 2) rate 
		FROM T_200_FO_INDEX 
		WHERE f_part_code = '029'";


        string query예상지수선물 = @"SELECT 'KOSPI' name,f_part_idx/100 part_idx, f_chg_type chg_type, f_part_chg/100 part_chg, 
		round(f_part_chg / decode(f_chg_type, '+', (f_part_idx / 100) - (f_part_chg / 100), 
			'-', (f_part_idx / 100) + (f_part_chg / 100),1), 2) rate 
		FROM t_fo_index 
		WHERE f_part_code = '001' 
		UNION 
		SELECT 'KOSQ' name, f_part_idx / 100 part_idx, f_chg_type chg_type, f_part_chg / 100 part_chg, 
		round(f_part_chg / decode(f_chg_type, '+', (f_part_idx / 100) - (f_part_chg / 100), 
			'-', (f_part_idx / 100) + (f_part_chg / 100),1), 2) rate 
		FROM T_KOSDAQ_FO_INDEX 
		WHERE f_part_code = '001' 
		UNION 
		SELECT '선물' name, part_idx part_idx, chg_type, ABS(part_chg) part_chg, 
		ABS(round(part_chg / decode(chg_type, '+', (part_idx / 100) - (part_chg / 100),
			'-', (part_idx / 100) + (part_chg / 100),1), 2)) rate 
		FROM(SELECT decode(a.f_fo_price,0, b.F_JUN_LAST_PRICE, a.f_fo_price) part_idx,                    
      decode(a.f_fo_price, 0, 0, (a.f_fo_price - b.F_JUN_LAST_PRICE)/100) part_chg,
      decode(a.f_fo_price, 0, ' ', decode(sign(a.f_fo_price -b.F_JUN_LAST_PRICE),1,'+',-1,'-',0,' ')) chg_type
 FROM t_sunmul_online a, t_sunmul_batch b
WHERE a.f_stock_code = b.f_stock_code
  AND a.f_stock_seq = 1
  AND b.f_market_date = (select max(OPEN_DAY) from v_open_day)
  AND b.F_MONTH_GUBUN = '1')";




        string query미국 = @"select decode(a.f_symb,  'DJI@DJI', 1, 
		'NAS@IXIC', 2, 
		'SPI@SPX', 3) ss, 
		a.f_input_name name, round(b.f_last, 2) part_idx, b.f_sign chg_type, round(b.f_diff, 2)  part_chg, round(b.f_rate, 2) rate 
		from t_world_ix_eq_master a, t_world_ix_eq_sise b 
	    where a.f_symb = b.f_symb 
	    and a.f_symb in('DJI@DJI', 'NAS@IXIC', 'SPI@SPX')";


        string query중화권 = @"select decode(a.f_symb, 'HSI@HSI', 1, 
		'SHS@000001', 2, 
		'TWS@TI01', 3) ss, 
		a.f_input_name name, round(b.f_last, 2) part_idx, b.f_sign chg_type, ABS(round(b.f_diff, 2))  part_chg, round(b.f_rate, 2) rate 
		from t_world_ix_eq_master a, t_world_ix_eq_sise b 
	    where a.f_symb = b.f_symb 
	    and a.f_symb in('SHS@000001', 'HSI@HSI', 'TWS@TI01')
		order by ss";


        string query유럽 = @"select decode(a.f_symb,  'LNS@FTSE100', 1, 
		'PAS@CAC40', 2, 
		'XTR@DAX30', 3) ss, 
		a.f_input_name name, round(b.f_last, 2) part_idx, b.f_sign chg_type, ABS(round(b.f_diff, 2))  part_chg, round(b.f_rate, 2) rate 
		from t_world_ix_eq_master a, t_world_ix_eq_sise b 
	    where a.f_symb = b.f_symb 
	    and a.f_symb in('PAS@CAC40', 'XTR@DAX30', 'LNS@FTSE100')  
		order by ss";


        string query아시아 = @"select decode(a.f_symb, 'SHS@000001', 1, 
		'TWS@TI01', 2, 
		'NII@NI225', 3) ss, 
		a.f_input_name name, round(b.f_last, 2) part_idx, b.f_sign chg_type, ABS(round(b.f_diff, 2))  part_chg, round(b.f_rate, 2) rate 
		from t_world_ix_eq_master a, t_world_ix_eq_sise b 
	    where a.f_symb = b.f_symb 
	    and a.f_symb in('SHS@000001', 'TWS@TI01', 'NII@NI225') 
		order by ss";


        string query달러환율 = @"select decode(a.f_symb,  'USDJPYCOMP', 1, 
		'EURUSDCOMP', 2, 
		'USDCNYCOMP', 3) ss, 
		a.f_input_name name, ABS(b.f_last) part_idx, 
		ABS(b.f_diff)  part_chg, 
		case when to_number(b.f_diff) > 0 then '+' when to_number(b.f_diff) < 0 then '-' else '' end sign, 
		round(b.f_rate, 2) rate 
		from t_world_forex_master a, t_world_forex_sise b 
	    where a.f_symb = b.f_symb 
	    and a.f_symb in('USDJPYCOMP', 'EURUSDCOMP', 'USDCNYCOMP')  --USDJPYCOMP 엔, USDCNYCOMP 위안화, EURUSDCOMP유로 
		order by ss";

//        string query원환율 = @"select decode(a.f_input_code,'A09','원/달러','A10','원/엔','A91','원/유로')  name, 
	//	a.f_curr_price part_idx, a.f_chg_type chg_type, ABS(a.f_net_chg) part_chg, 
	//	round(a.f_net_chg / decode(a.f_chg_type, '+', (a.f_curr_price / 100) - (ABS(a.f_net_chg) / 100), 
	//		'-', (a.f_curr_price / 100) + ((a.f_net_chg) / 100)), 2) rate 
	//	from t_input_index a, t_input_code b 
	//where a.f_input_code in('A09', 'A10', 'A91') 
	//	and a.f_input_code = b.f_input_code 
	//	order by a.f_input_code";
        string query원환율 = @"select decode(a.f_input_code,'A09','원/달러','A10','원/엔','A91','원/유로')  name, 
		a.f_curr_price part_idx, a.f_chg_type chg_type, ABS(a.f_net_chg) part_chg, 
		round(a.f_net_chg / decode(a.f_chg_type, '+', (a.f_curr_price / 100) - (ABS(a.f_net_chg) / 100), 
			'-', (a.f_curr_price / 100) + ((a.f_net_chg) / 100), 1), 2) rate 
		from t_input_index a, t_input_code b 
	where a.f_input_code in('A09', 'A10', 'A91') 
		and a.f_input_code = b.f_input_code 
		order by a.f_input_code";


        string query미국국채 = @"select decode(a.f_symb,  'GVO@TR02Y', 1, 
		'GVO@TR03Y', 2, 
		'GVO@TR05Y', 3) ss, 
		a.f_input_name name, ABS(round(b.f_last, 2)) part_idx, 
		b.f_sign chg_type, round(b.f_diff, 2)  part_chg, round(b.f_rate, 2) rate 
		from t_world_ix_eq_master a, t_world_ix_eq_sise b 
	where a.f_symb = b.f_symb 
	and a.f_symb in('GVO@TR02Y', 'GVO@TR03Y', 'GVO@TR05Y') 
		order by ss";


        string query채권금리 = @"SELECT decode(a.f_symb, 'KIR@CMAA', 1, 
		'KIR@NB03Y', 2, 
		'KIR@CD91', 3) ss, 
		b.f_input_name part_name, ABS(round(a.f_last, 2)) part_idx, a.f_sign chg_type, 
		round(a.f_diff, 2) part_chg, round(a.f_rate, 2) rate  
		FROM t_world_ix_eq_sise a, t_world_ix_eq_master b  
		WHERE a.f_symb = b.f_symb  
		and b.f_symb in('KIR@CMAA', 'KIR@NB03Y', 'KIR@CD91')  
		order by ss";


        string query유가 = @"select decode(a.f_symb,  'SPT@DU', 1, 
		'SPT@EB', 2, 
		'SPT@CL', 3) ss, 
		a.f_input_name name, round(b.f_last, 2) part_idx, round(b.f_diff, 2)  part_chg, 
		case when to_number(b.f_diff) > 0 then '+' when to_number(b.f_diff) < 0 then '-' else '' end sign, 
		round(b.f_rate, 2) rate 
		from t_world_future_master a, t_world_future_sise b 
		where a.f_symb = b.f_symb 
		and a.f_symb in('SPT@DU', 'SPT@EB', 'SPT@CL')  --SPT@DU 두바이, SPT@CL' WTI, SPT@EB 브렌트 
		order by ss";


        string query광물 = @"select decode(a.f_symb, 'COM@GC', 1, 
		'COM@SI', 2, 
		'LME@CDY', 3) ss, 
		a.f_input_name name, ABS(round(b.f_last, 2)) part_idx, ABS(round(b.f_diff, 2))  part_chg, 
		case when to_number(b.f_diff) > 0 then '+' when to_number(b.f_diff) < 0 then '-' else '' end sign, 
		round(b.f_rate, 2) rate 
		from t_world_future_master a, t_world_future_sise b 
	where a.f_symb = b.f_symb 
	and a.f_symb in('COM@GC', 'COM@SI', 'LME@CDY')  
		order by ss";


        string query식자재 = @"select decode(a.f_symb,  'CBT$CORN', 1, 
		'CBT@SOYBEAN', 2, 
		'CBT@WHEAT', 3) ss, 
		a.f_input_name name, ABS(round(b.f_last, 2)) part_idx, ABS(round(b.f_diff, 2))  part_chg, 
		case when to_number(b.f_diff) > 0 then '+' when to_number(b.f_diff) < 0 then '-' else '' end sign, 
		round(b.f_rate, 2) rate 
		from t_world_future_master a, t_world_future_sise b 
	where a.f_symb = b.f_symb 
	and a.f_symb in('CBT@SOYBEAN', 'CBT$CORN', 'CBT@WHEAT') 
		order by ss";

		string query소맥_옥수수_밀 = @"select decode(a.f_symb,  'MK@WHEAT', 1, 
                         'CBT$CORN',  2, 
                         'CBT@WHEAT', 3) ss, 
         a.f_input_name name, ABS(round(b.f_last, 2)) part_idx, 
         ABS(round(b.f_diff,2))  part_chg, 
         case when to_number(b.f_diff) > 0 then '+' 
              when to_number(b.f_diff) < 0 then '-' else '' end sign, 
		  round(b.f_rate, 2) rate 
		   from t_world_future_master a, t_world_future_sise b 
		 where a.f_symb = b.f_symb 
		   and a.f_symb in ('MK@WHEAT', 'CBT$CORN' , 'CBT@WHEAT') 
		order by ss";

		string query대두_콩_현미 = @"select decode(a.f_symb,  'CBT@SOYBEAN', 1, 
                         'MK@BEAN',  2, 
                         'MK@RICE', 3) ss, 
         a.f_input_name name, ABS(round(b.f_last, 2)) part_idx, 
         ABS(round(b.f_diff,2))  part_chg, 
         case when to_number(b.f_diff) > 0 then '+' 
              when to_number(b.f_diff) < 0 then '-' else '' end sign, 
          round(b.f_rate,2) rate 
		  from t_world_future_master a, t_world_future_sise b 
		where a.f_symb = b.f_symb 
		  and a.f_symb in ('CBT@SOYBEAN' , 'MK@BEAN' , 'MK@RICE') 
		order by ss";

		string query커피_코코아_설탕 = @"select decode(a.f_symb,  'NYB@KC', 1, 
                         'MK@COCOA',  2, 
                         'NYB@SB', 3) ss, 
         a.f_input_name name, ABS(round(b.f_last, 2)) part_idx, 
         ABS(round(b.f_diff,2))  part_chg, 
         case when to_number(b.f_diff) > 0 then '+' 
              when to_number(b.f_diff) < 0 then '-' else '' end sign, 
          round(b.f_rate,2) rate 
		  from t_world_future_master a, t_world_future_sise b 
		where a.f_symb = b.f_symb 
		  and a.f_symb in ('NYB@KC' , 'MK@COCOA', 'NYB@SB') 
		order by ss";

		string query생우_비육우_돈육 = @"select decode(a.f_symb,  'MK@LC', 1, 
                         'MK@FC',  2, 
                         'MK@PORK', 3) ss, 
         a.f_input_name name, ABS(round(b.f_last, 2)) part_idx, 
         ABS(round(b.f_diff,2))  part_chg, 
         case when to_number(b.f_diff) > 0 then '+' 
              when to_number(b.f_diff) < 0 then '-' else '' end sign, 
          round(b.f_rate,2) rate 
		  from t_world_future_master a, t_world_future_sise b 
		where a.f_symb = b.f_symb 
		  and a.f_symb in ('MK@LC', 'MK@FC', 'MK@PORK') 
		order by ss";


		string query구리_철광석_니켈 = @"select decode(a.f_symb,  'LME@CDY', 1, 
                         'MK@IRON',  2, 
                         'LME@NDY', 3) ss, 
         a.f_input_name name, ABS(round(b.f_last, 2)) part_idx, 
         ABS(round(b.f_diff,2))  part_chg, 
         case when to_number(b.f_diff) > 0 then '+' 
              when to_number(b.f_diff) < 0 then '-' else '' end sign, 
          round(b.f_rate,2) rate 
		  from t_world_future_master a, t_world_future_sise b 
		where a.f_symb = b.f_symb 
		  and a.f_symb in ('LME@CDY', 'MK@IRON', 'LME@NDY') 
		order by ss";

		string query천연가스_백금_팔라듐 = @"select decode(a.f_symb,  'NYM@NG', 1, 
                         'NYM@PL',  2, 
                         'NYM@PA', 3) ss, 
         a.f_input_name name, ABS(round(b.f_last, 2)) part_idx, 
         ABS(round(b.f_diff,2))  part_chg, 
         case when to_number(b.f_diff) > 0 then '+' 
              when to_number(b.f_diff) < 0 then '-' else '' end sign, 
          round(b.f_rate,2) rate 
		  from t_world_future_master a, t_world_future_sise b 
		where a.f_symb = b.f_symb 
		  and a.f_symb in ('NYM@NG', 'NYM@PL', 'NYM@PA') 
		order by ss";

		string query납_아연_주석 = @"select decode(a.f_symb,  'MK@PDA', 1, 
                         'MK@ZDA',  2, 
                         'LME@SDY', 3) ss, 
         a.f_input_name name, ABS(round(b.f_last, 2)) part_idx, 
         round(b.f_diff,2)  part_chg, 
         case when to_number(b.f_diff) > 0 then '+' 
              when to_number(b.f_diff) < 0 then '-' else '' end sign, 
          round(b.f_rate,2) rate 
		  from t_world_future_master a, t_world_future_sise b 
		where a.f_symb = b.f_symb 
		  and a.f_symb in ('MK@PDA', 'MK@ZDA', 'LME@SDY') 
		order by ss"; 

		string query원면_목화 = @"select decode(a.f_symb,  'MK@COTTON', 1, 
                         'NYB@CT',  2) ss, 
         a.f_input_name name, ABS(round(b.f_last, 2)) part_idx, 
         round(b.f_diff,2)  part_chg, 
         case when to_number(b.f_diff) > 0 then '+' 
              when to_number(b.f_diff) < 0 then '-' else '' end sign, 
          round(b.f_rate,2) rate 
		  from t_world_future_master a, t_world_future_sise b 
		where a.f_symb = b.f_symb 
		  and a.f_symb in ('MK@COTTON', 'NYB@CT') 
		order by ss";

		string query국제금 = @"select decode(a.f_symb,  'COM@GC', 1, 
                         'COM@SI',  2) ss, 
         a.f_input_name name, ABS(round(b.f_last, 2)) part_idx, 
         round(b.f_diff,2)  part_chg, 
         case when to_number(b.f_diff) > 0 then '+' 
              when to_number(b.f_diff) < 0 then '-' else '' end sign, 
          round(b.f_rate,2) rate 
		  from t_world_future_master a, t_world_future_sise b 
		where a.f_symb = b.f_symb 
		  and a.f_symb in ('COM@GC' , 'COM@SI') 
		order by ss";

		string query국내금 = @"select decode(a.f_symb,  'MK@GC', 1, 
                          'MK@SI',  2) ss, 
         a.f_input_name name, ABS(round(b.f_last, 2)) part_idx, 
         round(b.f_diff,2)  part_chg, 
         case when to_number(b.f_diff) > 0 then '+' 
              when to_number(b.f_diff) < 0 then '-' else '' end sign, 
          round(b.f_rate,2) rate 
			from t_world_future_master a, t_world_future_sise b 
		where a.f_symb = b.f_symb 
			and a.f_symb in ('MK@GC', 'MK@SI') 
		order by ss";

		#endregion

		public override IDataRequest buildData()
        {

            string bufQuery = "";

            switch (mDataType)
            {
                case 판3데이터분류.주요지수_코스피_코스닥_코스피200:
                    bufQuery = query주요지수200;
                    break;
                case 판3데이터분류.주요지수_코스피_코스닥_선물:
                    bufQuery = query주요지수선물;
                    break;
                case 판3데이터분류.예상체결지수_코스피_코스닥_코스피200:
                    bufQuery = query예상지수200;
                    break;
                case 판3데이터분류.예상체결지수_코스피_코스닥_선물:
                    bufQuery = query예상지수선물;
                    break;
                case 판3데이터분류.미국지수:
                    bufQuery = query미국;
                    break;
                case 판3데이터분류.중화권지수:
                    bufQuery = query중화권;
                    break;
                case 판3데이터분류.유럽지수:
                    bufQuery = query유럽;
                    break;
                case 판3데이터분류.아시아지수_상해_대만_일본:
                    bufQuery = query아시아;
                    break;
                case 판3데이터분류.달러환율_엔_위안_유로:
                    bufQuery = query달러환율;
                    break;
                case 판3데이터분류.원환율_달러_엔_유로:
                    bufQuery = query원환율;
                    break;
                case 판3데이터분류.미국국채:
                    bufQuery = query미국국채;
                    break;
                case 판3데이터분류.채권금리:
                    bufQuery = query채권금리;
                    break;
                case 판3데이터분류.유가_두바이_WTI_브랜트:
                    bufQuery = query유가;
                    break;
                case 판3데이터분류.광물:
                    bufQuery = query광물;
                    break;
                case 판3데이터분류.식자재:
                    bufQuery = query식자재;
					break;

				case 판3데이터분류.소맥_옥수수_밀:
					bufQuery = query소맥_옥수수_밀;
					break;

				case 판3데이터분류.대두_콩_현미:
					bufQuery = query대두_콩_현미;
					break;

				case 판3데이터분류.커피_코코아_설탕:
					bufQuery = query커피_코코아_설탕;
					break;

				case 판3데이터분류.생우_비육우_돈육:
					bufQuery = query생우_비육우_돈육;
					break;

				case 판3데이터분류.구리_철광석_니켈:
					bufQuery = query구리_철광석_니켈;
					break;

				case 판3데이터분류.천연가스_백금_팔라듐:
					bufQuery = query천연가스_백금_팔라듐;
					break;

				case 판3데이터분류.납_아연_주석:
					bufQuery = query납_아연_주석;
					break;

				case 판3데이터분류.원면_목화:
					bufQuery = query원면_목화;
					break;

				case 판3데이터분류.국제금:
					bufQuery = query국제금;
					break;

				case 판3데이터분류.국내금:
					bufQuery = query국내금;
					break;
			}

            mRequestQuery.Add("", bufQuery);

            return this;
        }
    }
}
