def update_symbols():
    """
    update the symbol list calling myinvestorshub.com
    """
    from finance_game.models import Stock

    def get_countries():
        url = "http://www.myinvestorshub.com/yahoo_stock_list.php"
        countries_html = urllib2.urlopen(url).read()
        index_root = lxml.html.fromstring(countries_html)
        for country in index_root.cssselect("option"):
            yield country.text

    symbol_url_base = "http://www.myinvestorshub.com/yahoo_list.php"
    for country in get_countries():
        if country == "United Kingdom":
            data = urllib.urlencode({"cnt": country})
            binary_data = data.encode('utf-8')
            request = urllib2.Request(symbol_url_base, binary_data)
            response = urllib2.urlopen(request)
            index_root = lxml.html.fromstring(response.read().decode('utf-8'))
            table = index_root.cssselect("table")[3]
            stock_rows = table.cssselect("tr")
            for stock_row in stock_rows[1:]:
                _, company_name, symbol, exchange, country = list(map(lambda s: s.text, stock_row.cssselect("td")))
                if company_name and symbol and exchange and country:
                    print(company_name, symbol, exchange, country)
                    stock = Stock(name=company_name, symbol=symbol, exchange=exchange, country=country)
                    # stock.update_values()
                    stock.save()

def portfolio_add_stock(request, portfolio_id, portfolio, symbol, stock):
    if stock is None:
        stock = Stock(yahoo_symbol=symbol)
        stock.save()
    portfolio.stocks.add(stock)
    portfolio.save()
    return HttpResponse('')

def _update_stocks(symbols):
    """
    Update models.Stock entities.
    :param symbols: Iterable of stock symbols to update.
    :return: dict mapping symbols to new prices, dict mapping symbols to (old price, new price) for changed symbols.
    """

    to_put = []
    symbol_price_updated_dict = {}

    if symbols is None:
        stocks_ = list(Stock.query())
        symbols = [s.key.id() for s in stocks_]
    else:
        stocks_ = [Stock.get_by_id(s) for s in symbols]

    symbol_price_dict = stocks.get_stock_prices(symbols)

    for stock in stocks_:
        if stock is None:
            continue

        symbol = stock.key.id()
        price = symbol_price_dict.get(symbol, None)

        if stock.price != price:
            symbol_price_updated_dict[symbol] = (stock.price, price)
            stock.price = price
            to_put.append(stock)

    ndb.put_multi(to_put)

    return symbol_price_dict, symbol_price_updated_dict

def agregar_stock(detalle, almacen):
  if existe_stock(detalle.producto, almacen):
    stock = Stock.objects.get(producto = detalle.producto, en_almacen = almacen)
    stock.unidades = stock.unidades + detalle.cantidad
    stock.save()
  else:
    stock = Stock(producto = detalle.producto, en_almacen = almacen, unidades = detalle.cantidad)
    stock.save()

def BeginToTrade(request):
    RequestUser = request.user
    RequestGroup = RequestUser.group
    context={
        "form":SearchForm(),
        "sear":True
    }
    if request.method=="POST":
        Post = request.POST.dict()
        if Post.has_key("StockName"):
            StockName=str(Post["StockName"])
        else:
            StockName=""
        if Post.has_key("Volume"):
            Volume=Post["Volume"]
        else:
            Volume=0
        if Post.has_key("TransactionType"):
            TransactionType=Post["TransactionType"]
        else:
            TransactionType=None
        print Post

        context={
            "form":OrderForm(),
            "sear":False
            
            }
        
        stock = Stock.objects.filter(StockName=StockName)
        if len(stock)==0:
            print "123123"
            stock = Stock(StockName=StockName)
            stock.save()
            context["StockData"] = jsonpickle.encode(stock.Get_data())
        else:
             context["StockData"] = jsonpickle.encode(stock[0].Get_data())

        if Volume!="" and TransactionType != None:
            RequestUser.group.UpdateAcount()
            stockprice = stock[0].Get_Price()
            if TransactionType=="buy":
                if RequestUser.group.Cash >= stockprice*float(Volume):
                    RequestGroup.transaction_set.create(TransType = TransactionType,StockOfTrans = stock[0], Volume=Volume, PriceAtTrans=stock[0].Get_Price() )
                    context["mess"] = "Succesfully making transaction"
                else:
                    context["mess"] = "You don't have enough money"
            else:
                asset = RequestUser.group.AllAsset
                if asset[StockName] >= float(Volume):
                    RequestGroup.transaction_set.create(TransType = TransactionType,StockOfTrans = stock[0], Volume=Volume, PriceAtTrans=stock[0].Get_Price() )
                    context["mess"] = "Succesfully making transaction"
                else:
                    context["mess"] = "You don't have enough this stock to sell"
        
    return render(request, "BeginToTrade.html", context)

def get_all_stocks(name=None):
    search_name = request.args.get('name')
    if search_name:
        stock = Stock.find_one({'name': search_name}, test_mode=app.testing)
        return jsonify(stock=stock)
    industry = request.args.get('industry')
    if industry:
        stocks = Stock.find({'industry': industry}, test_mode=app.testing)
        return jsonify(stocks=stocks)
    # Get all stocks
    stocks = Stock.find_all(test_mode=app.testing)
    return jsonify(stocks=stocks['stocks'])

def stock():
    if not account.user_is_logged_in():
        return redirect(url_for("login"))
    ticker = request.args.get("ticker", "AAPL") # TODO: no ticker is error...

    # Find the stock.
    stock = Stock.get_stock_from_db(ticker)

    # Check for errors.
    if stock == -1:
        N_STOCKS = 20 # number of stocks to retrieve
        tickers = Stock.get_all_tickers()
        stocks = Stock.get_stocks(N_STOCKS, True) # default: list of stocks by alpha order

        return render_template("home.html",
                               error="No ticker matching that name could be found.",
                               tickers = tickers, stocks = stocks,
                               metric = "alpha", order = True,
                               has_recs = account.user_has_recommendations())

    # Get the prices with corresponding dates.
    # Produce formatted date strings to paste into HTML.
    price_time_series = Stock.get_time_series(ticker, 7)
    price_dates = price_time_series.keys()
    price_dates.sort()
    price_dates_str = []
    price_values = []
    for curr_date in price_dates:
        price_values.append(price_time_series[curr_date])
        price_dates_str.append(curr_date.strftime("%m-%d"))

    # Compute price change now-- consistent with time series data!
    price_change = price_values[-1] - price_values[-2]

    # Put a blurb in the stock page if it is recommended.
    stock_is_recommended = False
    if account.user_has_recommendations():
        if stock.ticker in Stock.recommended_tickers:
            stock_is_recommended = True

    return render_template("stock.html", ticker = stock.ticker,
                           name = stock.name, latest_price = stock.cur_price,
                           pe_ratio = stock.pe, market_cap = stock.cap,
                           dividends = stock.dividends,
                           beta = stock.beta,
                           sustainability = stock.sustainability,
                           socialgood = stock.socialgood,
                           american = stock.american,
                           price_change = price_change,
                           price_series_dates = price_dates_str,
                           price_series_values = price_values,
                           recommended = stock_is_recommended)

 def test_create_stock(self):
     """Make sure it create a stock."""
     Category.create(category_id='001', description='Testing Stock')
     create_stock('001', 'Testing stock', 1, '001', 9.99)
     s = Stock.select().where(Stock.stock_id == '001')
     t = Stock.delete().where(Stock.stock_id == '001')
     t.execute()
     t = Category.delete().where(Category.category_id == '001')
     t.execute()
     self.assertEqual(str(s), ("<class 'models.Stock'> SELECT `t1`.`id`,"
                               " `t1`.`stock_id`, `t1`.`description`,"
                               " `t1`.`quantity`, `t1`.`category_id`,"
                               " `t1`.`price` FROM `stock` AS t1"
                               " WHERE (`t1`.`stock_id` = %s) ['001']"))

 def test_create_outgoing(self):
     """Create an outgoing record."""
     Category.create(category_id='001', description='Testing Stock')
     Project.create(project_id='001', project_description="Testing")
     Role.create(role_name='Admin')
     User.create(first_name='Jay', second_name='Palm', phone='9783978223', role='Admin', username='JayPalm',
                 password='jfresh13', email='[email protected]')
     create_stock('001', 'Testing stock', 1, '001', 9.99)
     create_outgoing_stock(stock='001', project_id='001', date="2015-07-22", quantity='7', username='JayPalm')
     OutgoingStock.get(OutgoingStock.stock == '001').delete_instance()
     Stock.get(Stock.category == '001').delete_instance()
     User.get(User.username == 'JayPalm').delete_instance()
     Role.get(Role.role_name == 'Admin').delete_instance()
     Project.get(Project.project_id == '001').delete_instance()
     Category.get(Category.category_id == '001').delete_instance()

def add_existing_stock(pk_id, to_add):
    """Increase quantity for a specified record by pk_id.
    pk_id: primary key id
    """
    stock = Stock.get(
        Stock.id == pk_id
    )
    current_quantity = stock.quantity
    new_quantity = current_quantity + to_add
    query = Stock.update(
        quantity=new_quantity
    ).where(
        Stock.id == pk_id
    )
    return query.execute()

def show_stock(request, symbol):
    stks = Stock.objects.filter(symbol=symbol)
    if stks.count() == 0:
        stk = Stock(symbol=symbol, price=0.0)
        stk.save()
    elif stks.count() == 1:
        stk = stks[0]
    else:
        return HttpResponse("%s" % stks.count())
    try:
        ss = stk.history.order_by('date')[0]
    except:
        update(stk)
        ss = stk.history.order_by('date')[0]
    return render_to_response('stock.html', {'stock': stk, 'status': ss})

def _process_removed_stocks(sheet, ssheet_id, symbols):
    """
    Update database to reflect sheet's new stocks, changed bounds, or changed order.
    :param sheet: Instance of models.Sheet to modify.
    :param ssheet_id: Spreadsheet id.
    :param symbols: Iterable of symbols.
    :return: list of ndb Entities to put, list of ndb Entities to delete.
    """

    to_put, to_delete = [], []

    symbols_old = {k.id() for k in sheet.stock_keys}

    for symbol in symbols_old - set(symbols):
        stock = Stock.get_by_id(symbol)
        sheetstock_key = ndb.Key('SheetStock', '_'.join([ssheet_id, symbol]))

        to_delete.append(sheetstock_key)

        if stock and sheet.key in stock.sheet_keys:
            stock.sheet_keys.remove(sheet.key)
            if stock.sheet_keys:
                to_put.append(stock)
            else:
                to_delete.append(stock.key)

    return to_put, to_delete

    def test_fuzzy_text_search_industry_name(self):
        stocks = Stock.find({'industry': 'consumer'}, test_mode=True)
        stock = stocks[0]
        self._assert_first_stock(stock)

        stock_2 = stocks[1]
        self._assert_second_stock(stock_2)

def stock_add(request):
    from app.remote.stocks import StockHistoryClient
    from app.forms import StockWizardForm
    from app.models import Company, Stock

    if request.method == "POST":
        form = StockWizardForm(request.POST)
        pprint(form.is_valid())
        if form.is_valid():
            # save
            company = Company()
            company.name = form.cleaned_data['company_name']
            company.country = form.cleaned_data['company_country']
            company.save()

            stock = Stock()
            stock.company = company
            stock.name = form.cleaned_data['name']
            stock.wkn = form.cleaned_data['wkn']
            stock.isin = form.cleaned_data['isin']
            stock.symbol = form.cleaned_data['symbol']
            stock.type = form.cleaned_data['type']
            stock.default_stock_exchange = form.cleaned_data['default_stock_exchange']
            stock.save()

            return HttpResponseRedirect(reverse('stock', args=(stock.symbol,)))
        else:
            pprint(form.errors)
    else:
        data = None
        if 'wkn_or_isin' in request.GET:
            shc = StockHistoryClient()
            data = shc.get_basics_by_wkn_or_isin(wkn_or_isin=request.GET['wkn_or_isin'])

            data['company_country'] = data['country']
            data['company_name'] = data['name']

        form = StockWizardForm(initial=data)

    return render_to_response(
        'stock_wizard_add.html',
        {
            'form': form
        },
        context_instance=RequestContext(request)
    )

    def test_find_all_stocks(self):
        stocks = Stock.find_all(test_mode=True)
        stocks = stocks['stocks']
        stock_1 = stocks[0]
        self._assert_first_stock(stock_1)

        stock_2 = stocks[1]
        self._assert_second_stock(stock_2)

def show_stock():
  stock = request.args.get('ticker')
  value = request.args.get('value')
  unit = request.args.get('unit')
  stock = Stock(stock)
  if stock.data is not None:
    stock.calc_all()
    format_data = stock.data.reset_index()  # DateTimeIndex to column
    format_data = format_data.to_json(date_format='epoch', orient='records')
    # Markup tells jinja2 that the object is safe for rendering, without
    # escaping the quotes (caused problems when creating JSON object).
    format_data = Markup(format_data)
    # need to redirect instead
    return render_template('chart.html', stock=stock, data=format_data)
  else:
    error = stock.ticker + " is an invalid ticker symbol."
    return render_template('index.html', error = error)

    def test_create_incoming(self):
        """Create an incoming record."""
        Category.create(category_id='001', description='Testing Stock')
        create_stock('001', 'Testing stock', 1, '001', 9.99)
        create_incoming_stock(stock="001", date="2015-07-22", quantity=13, price=13.99)
        p = IncomingStock.select().where(IncomingStock.stock == '001')
        q = IncomingStock.delete().where(IncomingStock.stock == '001')
        q.execute()
        s = Stock.select().where(Stock.stock_id == '001')
        t = Stock.delete().where(Stock.stock_id == '001')
        t.execute()
        t = Category.delete().where(Category.category_id == '001')
        t.execute()

        self.assertEqual(str(p), ("<class 'models.IncomingStock'> SELECT `t1`.`id`, `t1`.`stock_id`, "
                                  "`t1`.`date`, `t1`.`price`, `t1`.`cost` FROM `incomingstock` AS t1 WHERE "
                                  "(`t1`.`stock_id` = %s) ['001']"))

def get_stocks_in_day(stock_name, day):
    out = []
    markets = Market.all(keys_only=False).filter("date =", day).filter("ref =", stock_name.market_ref).order('time').fetch(100)
    for market in markets:
        stock = Stock.all().filter("market =", market.key()).filter('name = ', stock_name.key()).get()
        if not stock is None:
            dt = datetime.combine(market.date, market.time)
            out.append((dt, stock, market.exchange_rate))
    return out

def get_stocks_days(stock_name, num_days=10):
    days = last_weekdays(num_days)
    out = []
    for day in days:
        market = Market.all(keys_only=False).filter("date =", day.date()).filter("ref =", stock_name.market_ref).order('-time').get()
        if not market is None:
            stock = Stock.all().filter("market =", market.key()).filter('name = ', stock_name.key()).get()
            if not stock is None:
                out.append((day, stock, market.exchange_rate))
    return out

 def test_create_stock_no_duplicate(self):
     """Makes sure stock don't have the same stock_id when creating."""
     Category.create(category_id='001', description='Testing Stock')
     create_stock('001', 'Testing stock', 1, '001', 9.99)
     from peewee import IntegrityError
     with self.assertRaises(IntegrityError):
         create_stock('001', 'Testing stock', 1, '001', 9.99)
     q = Stock.delete().where(Stock.stock_id == '001')
     q.execute()
     t = Category.delete().where(Category.category_id == '001')
     t.execute()