def get_tick(code, start, end=None, file=None):
    date = get_dates(start, end)
    if not file:
        file = '-'.join([code, start.replace('-', ''), str(len(date))]) + r'.csv'
    for d in date:
        df = ts.get_tick_data(code, date=d)
        df.to_csv(file, mode='a')

def multi_write((code,start_date,end_date)):
    startdate=datetime.datetime.strptime(start_date,'%Y-%m-%d').date()
    enddate = datetime.datetime.strptime(end_date, '%Y-%m-%d').date()
    #string-to-dateobject conversion

    date_list=date_loop.date_loop(start_date= startdate,end_date= enddate)
    #return a list of dates

    DTS=[]
    for dates in date_list:
        preDTS = ts.get_tick_data(code, date=dates)

        #DTS=Data To be Saved
        try:

            preDTS['type'][0].split()
            #throw an AttributeError when no data is available

            preDTS['date'] = dates
            #insert date column
            preDTS=preDTS.sort_values(by='time',ascending= True)

            #sql table will be named like 'hist_Part0,1,2,3,4...'
            DTS.append(preDTS)

        except AttributeError:

            print 'No available data for ', dates

    try:
        DTS = pd.concat(DTS)

    except ValueError :
        DTS=None
    return DTS

def request_test_tick(code, engine, start_date, end_date):
    create_test_talbe(code)

    cur_day = start_date
    logging.info('requesting tick, code: ' + code + str(threading.currentThread()))


    while cur_day != end_date:
        try:
            #logging.info('cur_day: ' + str(cur_day) + str(threading.currentThread()))
            tick = ts.get_tick_data(code, date=cur_day.date(), retry_count=500, src='tt')
            if not tick.empty:
                if tick.time[0] != 'alert("当天没有数据");':
                    tick['type'] = tick['type'].apply(lambda x: trade_type_dic[x])
                    tick['change'] = tick['change'].apply(change_dic)
                    tick['time'] = str(cur_day.date()) + ' '+ tick['time']
                    tick.to_sql('test_' + code, engine, if_exists='append', dtype={'time': DateTime})
                    logging.info('save to test_' + code + ' on '+ str(cur_day) + ' thread ' + str(threading.currentThread()))


        except Exception:
            logging.error(str(code) + ' request tick failed on ' + str(cur_day) + str(threading.currentThread()))

        delta = datetime.timedelta(days=1)
        cur_day = cur_day + delta

 def deal_list_download(self, stock, engine, start='2014-10-16'):
     '''
     分时数据下载
     '''
     deals = ts.get_tick_data(stock, date='2015-10-16')
     table_name = stock + '_deal'
     deals.to_sql(table_name, engine) 

def get_in_out(symbol, date):
    df = ts.get_tick_data(symbol, date=date, src='tt')
    thre = df.amount.quantile(0.99)
    thre = np.max([thre, 1e6])
    in_amount = df[(df.amount>thre)&(df.type=="买盘")].amount.sum()/1e8
    out_amount = df[(df.amount>thre)&(df.type=="卖盘")].amount.sum()/1e8
    return [in_amount, out_amount]

 def get_tick_data(self, code, t_date = None):
     if t_date is None:
         t_date = dataTime.datetimeRelative(delta = 0)
     t_date = t_date.replace(' 00:00:00', '')
     df = ts.get_tick_data(code, date = t_date)
     df = self.format_date_to_datetime(df, t_date = t_date)
     return df

def get_tick_feng(_stock_id, _trade_date):

    df = None

    try:
        df = ts.get_tick_data(_stock_id, date=_trade_date, retry_count=5, pause=1)
    except Exception:
        log_error("warn: %s get ticks exception!", _stock_id)
        time.sleep(5)
        return None

    if df is None :
        log_error("warn: stock %s, %s is None, next", _stock_id, _trade_date)
        return None

    if df.empty:
        log_error("warn: stock %s, %s is empty, next", _stock_id, _trade_date)
        return None

    if len(df) <= 5:
        log_error("warn: stock %s, %s is short %d, next", _stock_id, _trade_date, len(df))
        return None

    df = df.set_index('time').sort_index()

    return df

def check_if_reach_limit(code, date=datetime.now().strftime("%Y-%m-%d")):
    """
    """
    kdata = ts.get_hist_data(code,date,date,ktype='D')
    if kdata.ix[0]['p_change'] < 9.9:
        return False

    stock = ts.get_tick_data(code,date)[['time','price']]
    indx, = np.where(np.diff(list(reversed(stock['price'])))==0)

#     print "length: ", len(indx)

    arr = []
    beg = 0
    cur = indx[0]-1
    for i,val in enumerate(indx):
        if cur+1 == val:
            cur += 1
        else:
            if i > (beg+1):
                arr.append((indx[beg], indx[i-1]))
            beg = i
            cur = val
    arr.append((indx[beg],cur))
    mindx = np.argmax([x[1]-x[0] for x in arr])

    print "length of arr: ", len(arr)

    p = arr[mindx][0]
    print 'starting index: ', p
    print 'starting time: ', stock.ix[stock.shape[0]-p-1]['time']

    return stock.ix[stock.shape[0]-p-1]['time']

	def tick(tstamp):
	    date = tstamp.split(" ")[0]
	    tick_data = ts.get_tick_data(code,date=date,retry_count=10,pause=5)
	    nrows = tick_data.shape[0]
	    tick_data['date'] = pd.Series([date]*nrows)

	    return tick_data

def get_sd(id, label_start_date, fea_delta = 240):
    # delta 指的是 确定label；的时候 是一个星期的涨幅还是什么
    # fea delta 指的是， 我要准备多久的数据
    ## 获取分笔数据
    # get all time ticks
    # 创建一个日期列表
    ticks = None
    date_list = dt_tool.dt_range(label_start_date, -fea_delta)
    for date in date_list:
        try:
            tick = ts.get_tick_data(id, date)
        except Exception, e:
            print e
            continue
        if tick is None:
            continue
        tick.type = tick.type.apply(lambda x : type2id(x))
        ft = tick.sort('amount', ascending=False).head(10).reset_index().drop(['index', 'time', 'change'], 1).stack().reset_index()
        ft.index = ft.level_0.map(str) + '_' + ft.level_1
        fT = ft.drop(['level_0', 'level_1'], 1).T
        fT['date'] = dt_tool.format(date)
        if ticks is None:
            ticks = fT
        else:
            ticks = ticks.append(fT)
        ticks.to_csv('data/ticks.csv', index=None)

def pankou(name):
    # rang =pd.date_range('20150101', periods=5)
    # a = ts.get_tick_data(code, rang)
    a = ts.get_tick_data(name, date='2017-03-09')
    buy_sum = 0
    sell_sum = 0
    other_sum = 0
    for item in range(0, len(a)):
        row = a[item:item + 1]
        type = row['type']
        if type[item] == '卖盘':
            sell_sum += row['amount'][item]
        elif type[item] == '买盘':
            buy_sum += row['amount'][item]
        else:
            other_sum += row['amount'][item]
    pan_sum = sum([sell_sum, buy_sum, other_sum])
    mp = buy_sum / pan_sum
    sp = sell_sum / pan_sum
    zx = other_sum / pan_sum
    # print('股票',code,(sell_sum, buy_sum, other_sum)/pan_sum)
    print('股票', name, mp, sp, zx)
    if isnan(mp):
        return
    with UseDatabase(config) as cursor:
        _SQL = """insert into pankou
        (name, mp, sp, zx)
        values
        (%s, %s, %s, %s)"""
        cursor.execute(_SQL, (name, mp, sp, zx))

def multi_write((start_date,end_date)):
    startdate=datetime.datetime.strptime(start_date,'%Y-%m-%d').date()
    enddate = datetime.datetime.strptime(end_date, '%Y-%m-%d').date()
    #string-to-dateobject conversion

    date_list=date_loop.date_loop(start_date= startdate,end_date= enddate)
    #return a list of date
    #engine=create_engine('mysql+mysqldb://root:[email protected]:3306/tusharedb?charset=utf8')
    DTS=[]
    for dates in date_list:
        preDTS = ts.get_tick_data('600640', date=dates)

        #DTS=Data To be Saved
        try:

            preDTS['type'][0].split()
            #throw an AttributeError when no data is available
            preDTS.sort_index(axis=0, ascending=True)
            #reverse the dataframe with latest data placed at the bottom
            preDTS['date'] = dates
            #insert date column

            #sql table will be named like 'hist_Part0,1,2,3,4...'
            DTS.append(preDTS)

        except AttributeError:

            print 'No available data for ', dates

    try:
        DTS = pd.concat(DTS)
    except ValueError :
        DTS=None
    return DTS

def get_tick_history(exsymbol, days=10):
    store = get_store(store_type)
    dates = store.get('id000001').index
    symbol = exsymbol_to_symbol(exsymbol)
    df_res = pd.DataFrame(columns=["kaipan_vol", "kaipan_money", "shoupan_vol", "shoupan_money"])
    for i in range(days, 0, -1):
        date = dates[len(dates)-i].strftime("%Y-%m-%d")
        df = ts.get_tick_data(symbol, date=date, src="tt")
        if df is None or len(df) == 0:
            continue
        df["time"] = pd.to_datetime(df["time"], format='%H:%M:%S')
        kaipan_time = df.iloc[0].time
        kaipan_vol = 0
        kaipan_money = 0
        if kaipan_time.hour == 9 and kaipan_time.minute < 30:
            kaipan_vol = df.iloc[0].volume
            kaipan_money = df.iloc[0].amount
        shoupan_time = df.iloc[len(df)-1].time
        shoupan_vol = 0
        shoupan_money = 0
        if shoupan_time.hour >= 15 and shoupan_time.minute >= 0:
            shoupan_vol = df.iloc[len(df)-1].volume
            shoupan_money = df.iloc[len(df)-1].amount
        df_res.loc[date] = [kaipan_vol, kaipan_money, shoupan_vol, shoupan_money]
    return df_res

def get_multiday_ave_compare_silent(code, dayl='10'):
    dtick = ts.get_today_ticks(code)
    d_hist = ema.getdata_ema_trend_silent(code, dayl, 'd')
    # print d_hist
    day_t = ema.get_today()
    if day_t in d_hist.index:
        dl = d_hist.drop(day_t).index
    else:
        dl = d_hist.index
    # print dl
    # print dl
    ep_list = []
    for da in dl.values:
        # print code,da
        td = ts.get_tick_data(code, da)
        # print td
        if not type(td) == types.NoneType:
            ep = td['amount'].sum() / td['volume'].sum()
            ep_list.append(ep)
            # print ("D: %s P: %s" % (da[-5:], ep))
    ave = ema.less_average(ep_list)
    if len(dtick.index) > 0:
        ep = dtick['amount'].sum() / dtick['volume'].sum()
        p_now = dtick['price'].values[0] * 100
        if p_now > ave or ep > ave:
            print ("GOLD:%s ep:%s UP:%s!!! A:%s %s !!!" % (code, ep, p_now, ave, get_now_time()))
            # elif p_now > ave and ep < ave:
            #     print ("gold:%s ep:%s UP:%s! A:%s %s !" % (code, ep, p_now, ave, get_now_time()))
            # elif p_now < ave and ep > ave:
            #     print ("down:%s ep:%s Dow:%s? A:%s %s ?" % (code, ep, p_now, ave, get_now_time()))
            return True
        else:
            if p_now < ave and ep < ave:
                print ("DOWN:%s ep:%s now:%s??? A:%s %s ???" % (code, ep, p_now, ave, get_now_time()))
            return False

 def __init__(self, tableName):
     '''
     Constructor
     '''
     bdl.BaseDataLoader.__init__(self, tableName)
     #self.db = msql.MySQLConnection.GetConnection()
     #self.ml = mlg.MyLogger.getLogger()
     #tradingday = datetime.strftime(datetime.today(), "%Y-%m-%d")
     
     code = '600848'
     
     tradingday = '2015-11-30'
     
     self.ml.info("Loading Data")
     data = ts.get_tick_data(code=code, date=tradingday)
     
     data['tradingday'] = tradingday
     
     data['code'] = code
     
     indexdata = data.set_index(['tradingday', 'code'])
     
     self.ml.info("Saving Data")
     indexdata.to_sql(self.tableName, self.db, flavor="mysql", if_exists="append")
     
     #关闭连接
     #db.close()
     
     self.ml.info("Saving Success") 

def db():
    df = ts.get_tick_data('600848', date='2014-12-22')
    engine = create_engine('mysql://root:[email protected]/mystock?charset=utf8')
    #     db = MySQLdb.connect(host='127.0.0.1',user='root',passwd='jimmy1',db="mystock",charset="utf8")
    #     df.to_sql('TICK_DATA',con=db,flavor='mysql')
    #     db.close()
    df.to_sql('tick_data', engine, if_exists='append')

def xtick_down100(qx, ftg):
    '''
    根据指定的日期，股票代码，数据文件名：ftg
    下载指定股票指定日期的ticks数据，并保存到ftg
    [输入]
        qx.code，股票代码
        qx.xtimSgn，当前日期的字符串
        ftg，保存tick数据的文件名
    '''
    df, dn = [], 0
    try:
        df = ts.get_tick_data(qx.code, date=qx.xtimSgn)  # print(df.head())
    except IOError:
        pass  # skip,error
    datFlag, dn = False, len(df)
    print('     n', dn, ftg)  # 跳过无数据 日期
    # if zwt.xin(dn,0,9):print('n2',dn,ftg)
    if dn > 10:
        df['type'] = df['type'].str.replace(u'中性盘', 'norm')
        df['type'] = df['type'].str.replace(u'买盘', 'buy')
        df['type'] = df['type'].str.replace(u'卖盘', 'sell')
        df.to_csv(ftg, index=False, encoding='utf')
        datFlag = True
    #
    return datFlag, dn

def nosql():
    import pymongo
    import json
    conn = pymongo.Connection('127.0.0.1', port=27017)
    df = ts.get_tick_data('600848', date='2014-12-22')
    print(df.to_json(orient='records'))

    conn.db.tickdata.insert(json.loads(df.to_json(orient='records')))

def get_acf(code):
    day = get_last_day()
    print str(day)
    today_prices = tushare.get_tick_data(code=code,
                                         date=str(day-datetime.timedelta(days=3)),
                                         retry_count=50)
    today_prices = [(value[0], value[1]) for value in today_prices.values]
    train_prices = [price for time, price in today_prices if time > "13:00:00"]
    return ar_model.AR.fit(train_prices)

 def virance_a_day(day):
     try:
         datas = tushare.get_tick_data(code, date=day, retry_count = 5)
         prices = [value[1] for value in datas.values]
         ave_price = sum(prices) / len(prices)
         virance = sum([(price - ave_price) ** 2 for price in prices])
         return virance
     except:
         return None