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 Trader_Stocks : ADataObject
    {
     
        internal Trader_Stocks(국내시장종류 recvDataType, string recvStockCode)
        {
            this.mDataType = recvDataType;

            this.mStockCode = recvStockCode;
        }

        국내시장종류 mDataType;

        string mStockCode = "";

        #region 쿼리
        //코스피 상단
        string queryKOSPI상단 = @"SELECT DISTINCT b.f_stock_code STOCK_CODE, 
            b.f_stock_wanname STOCK_NAME, 
            a.f_curr_price CURR_PRICE, 
            a.f_net_chg NET_CHG, 
            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 in('{0}')";


        string queryKOSPI매도 = @"select f_sale_nick_kname sale_name, f_sell_vol  sell_vol 
            from( 
                select a.f_stock_code, 
                b.f_sale_nick_kname, a.f_sell_dealer_vol_1 f_sell_vol 
                from t_dealer a, t_sale_member b, t_stock c 
    where c.f_stock_wanname = '{0}' 
        and c.f_mkt_halt = 'N' 
        and a.f_sell_dealer_no_1 = b.f_sale_no 
        and a.f_stock_code = c.f_stock_code 
        union 
        select a.f_stock_code, 
        b.f_sale_nick_kname, a.f_sell_dealer_vol_2 f_sell_vol 
        from t_dealer a, t_sale_member b, t_stock c 
    where c.f_stock_wanname = '{0}' 
        and c.f_mkt_halt = 'N' 
        and a.f_sell_dealer_no_2 = b.f_sale_no 
        and a.f_stock_code = c.f_stock_code 
        union 
        select a.f_stock_code, 
        b.f_sale_nick_kname, a.f_sell_dealer_vol_3 f_sell_vol 
        from t_dealer a, t_sale_member b, t_stock c 
    where c.f_stock_wanname = '{0}' 
        and c.f_mkt_halt = 'N' 
        and a.f_sell_dealer_no_3 = b.f_sale_no 
        and a.f_stock_code = c.f_stock_code 
        union 
        select a.f_stock_code, 
        b.f_sale_nick_kname, a.f_sell_dealer_vol_4 f_sell_vol 
        from t_dealer a, t_sale_member b, t_stock c 
    where c.f_stock_wanname = '{0}' 
        and c.f_mkt_halt = 'N' 
        and a.f_sell_dealer_no_4 = b.f_sale_no 
        and a.f_stock_code = c.f_stock_code 
        union 
        select a.f_stock_code, 
        b.f_sale_nick_kname, a.f_sell_dealer_vol_5 f_sell_vol 
        from t_dealer a, t_sale_member b, t_stock c 
    where c.f_stock_wanname = '{0}' 
        and c.f_mkt_halt = 'N' 
        and a.f_sell_dealer_no_5 = b.f_sale_no 
        and a.f_stock_code = c.f_stock_code 
        ) 
            order by f_sell_vol desc";

        string queryKOSPI매수 = @"select f_sale_nick_kname sale_name, f_buy_vol  buy_vol 
            from( 
                select a.f_stock_code, 
                b.f_sale_nick_kname, a.f_buy_dealer_vol_1 f_buy_vol 
                from t_dealer a, t_sale_member b, t_stock c 
    where c.f_stock_wanname = '{0}' 
        and c.f_mkt_halt = 'N' 
        and a.f_buy_dealer_no_1 = b.f_sale_no 
        and a.f_stock_code = c.f_stock_code 
        union 
        select a.f_stock_code, 
        b.f_sale_nick_kname, a.f_buy_dealer_vol_2 f_buy_vol 
        from t_dealer a, t_sale_member b, t_stock c 
    where c.f_stock_wanname = '{0}' 
        and c.f_mkt_halt = 'N' 
        and a.f_buy_dealer_no_2 = b.f_sale_no 
        and a.f_stock_code = c.f_stock_code 
        union 
        select a.f_stock_code, 
        b.f_sale_nick_kname, a.f_buy_dealer_vol_3 f_buy_vol 
        from t_dealer a, t_sale_member b, t_stock c 
    where c.f_stock_wanname = '{0}' 
        and c.f_mkt_halt = 'N' 
        and a.f_buy_dealer_no_3 = b.f_sale_no 
        and a.f_stock_code = c.f_stock_code 
        union 
        select a.f_stock_code, 
        b.f_sale_nick_kname, a.f_buy_dealer_vol_4 f_buy_vol 
        from t_dealer a, t_sale_member b, t_stock c 
    where c.f_stock_wanname = '{0}' 
        and c.f_mkt_halt = 'N' 
        and a.f_buy_dealer_no_4 = b.f_sale_no 
        and a.f_stock_code = c.f_stock_code 
        union 
        select a.f_stock_code, 
        b.f_sale_nick_kname, a.f_buy_dealer_vol_5 f_buy_vol 
        from t_dealer a, t_sale_member b, t_stock c 
    where c.f_stock_wanname = '{0}' 
        and c.f_mkt_halt = 'N' 
        and a.f_buy_dealer_no_5 = b.f_sale_no 
        and a.f_stock_code = c.f_stock_code 
        ) 
            order by f_buy_vol desc";

        string queryKOSDAQ상단 = @"SELECT DISTINCT b.f_stock_code STOCK_CODE, 
            b.f_stock_wanname STOCK_NAME, 
            a.f_curr_price CURR_PRICE, 
            a.f_net_chg NET_CHG, 
            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_kosdaq_online1 a, t_kosdaq_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 in('{0}')";

        string queryKOSDAQ매도 = @"select f_sale_nick_kname sale_name, f_sell_vol  sell_vol 
            from( 
                select a.f_stock_code, 
                b.f_sale_nick_kname, a.f_buy_dealer_vol_1 f_sell_vol 
                from t_kosdaq_dealer a, t_sale_member b, t_kosdaq_stock c 
    where c.f_stock_wanname = '{0}' 
        and c.f_mkt_halt = 'N' 
        and a.f_sell_dealer_no_1 = b.f_sale_no 
        and a.f_stock_code = c.f_stock_code 
        union 
        select a.f_stock_code, 
        b.f_sale_nick_kname, a.f_sell_dealer_vol_2 f_sell_vol 
        from t_kosdaq_dealer a, t_sale_member b, t_kosdaq_stock c 
    where c.f_stock_wanname = '{0}' 
        and c.f_mkt_halt = 'N' 
        and a.f_sell_dealer_no_2 = b.f_sale_no 
        and a.f_stock_code = c.f_stock_code 
        union 
        select a.f_stock_code, 
        b.f_sale_nick_kname, a.f_sell_dealer_vol_3 f_sell_vol 
        from t_kosdaq_dealer a, t_sale_member b, t_kosdaq_stock c 
    where c.f_stock_wanname = '{0}' 
        and c.f_mkt_halt = 'N' 
        and a.f_sell_dealer_no_3 = b.f_sale_no 
        and a.f_stock_code = c.f_stock_code 
        union 
        select a.f_stock_code, 
        b.f_sale_nick_kname, a.f_sell_dealer_vol_4 f_sell_vol 
        from t_kosdaq_dealer a, t_sale_member b, t_kosdaq_stock c 
    where c.f_stock_wanname = '{0}' 
        and c.f_mkt_halt = 'N' 
        and a.f_sell_dealer_no_4 = b.f_sale_no 
        and a.f_stock_code = c.f_stock_code 
        union 
        select a.f_stock_code, 
        b.f_sale_nick_kname, a.f_sell_dealer_vol_5 f_sell_vol 
        from t_kosdaq_dealer a, t_sale_member b, t_kosdaq_stock c 
    where c.f_stock_wanname = '{0}' 
        and c.f_mkt_halt = 'N' 
        and a.f_sell_dealer_no_5 = b.f_sale_no 
        and a.f_stock_code = c.f_stock_code 
        ) 
            order by f_sell_vol desc";

        string queryKOSDAQ매수 = @"select f_sale_nick_kname sale_name, f_buy_vol  buy_vol 
            from( 
                select a.f_stock_code, 
                b.f_sale_nick_kname, a.f_buy_dealer_vol_1 f_buy_vol 
                from t_kosdaq_dealer a, t_sale_member b, t_kosdaq_stock c 
    where c.f_stock_wanname = '{0}' 
        and c.f_mkt_halt = 'N' 
        and a.f_buy_dealer_no_1 = b.f_sale_no 
        and a.f_stock_code = c.f_stock_code 
        union 
        select a.f_stock_code, 
        b.f_sale_nick_kname, a.f_buy_dealer_vol_2 f_buy_vol 
        from t_kosdaq_dealer a, t_sale_member b, t_kosdaq_stock c 
    where c.f_stock_wanname = '{0}' 
        and c.f_mkt_halt = 'N' 
        and a.f_buy_dealer_no_2 = b.f_sale_no 
        and a.f_stock_code = c.f_stock_code 
        union 
        select a.f_stock_code, 
        b.f_sale_nick_kname, a.f_buy_dealer_vol_3 f_buy_vol 
        from t_kosdaq_dealer a, t_sale_member b, t_kosdaq_stock c 
    where c.f_stock_wanname = '{0}' 
        and c.f_mkt_halt = 'N' 
        and a.f_buy_dealer_no_3 = b.f_sale_no 
        and a.f_stock_code = c.f_stock_code 
        union 
        select a.f_stock_code, 
        b.f_sale_nick_kname, a.f_buy_dealer_vol_4 f_buy_vol 
        from t_kosdaq_dealer a, t_sale_member b, t_kosdaq_stock c 
    where c.f_stock_wanname = '{0}' 
        and c.f_mkt_halt = 'N' 
        and a.f_buy_dealer_no_4 = b.f_sale_no 
        and a.f_stock_code = c.f_stock_code 
        union 
        select a.f_stock_code, 
        b.f_sale_nick_kname, a.f_buy_dealer_vol_5 f_buy_vol 
        from t_kosdaq_dealer a, t_sale_member b, t_kosdaq_stock c 
    where c.f_stock_wanname = '{0}' 
        and c.f_mkt_halt = 'N' 
        and a.f_buy_dealer_no_5 = b.f_sale_no 
        and a.f_stock_code = c.f_stock_code 
        ) 
            order by f_buy_vol desc";


        #endregion

        public override IDataRequest buildData()
        {

            
            switch (this.mDataType)
            {
                case 국내시장종류.KOSPI:
                    mRequestQuery.Add("상단", String.Format(queryKOSPI상단, this.mStockCode));
                    mRequestQuery.Add("매도", String.Format(queryKOSPI매도, this.mStockCode));
                    mRequestQuery.Add("매수", String.Format(queryKOSPI매수, this.mStockCode));
                    break;
                case 국내시장종류.KOSDAQ:
                    mRequestQuery.Add("상단", String.Format(queryKOSDAQ상단, this.mStockCode));
                    mRequestQuery.Add("매도", String.Format(queryKOSDAQ매도, this.mStockCode));
                    mRequestQuery.Add("매수", String.Format(queryKOSDAQ매수, this.mStockCode));
                    break;
            }

            
            return this;
        }
    }
}
