def get_todays_electricity():
    return Electricity.query.with_entities(
        (func.max(Electricity.meter_280) - func.min(Electricity.meter_280)).label(
            'todays_export'), (func.max(Electricity.meter_180) - func.min(Electricity.meter_180)).label(
            'todays_import')).filter(
        func.strftime('%Y-%m-%d', Electricity.created_at) == datetime.now().strftime('%Y-%m-%d')).group_by(
        func.strftime('%Y-%m-%d', Electricity.created_at)).first()

def year_report_by_month_json(year):
    by_year = func.strftime('%Y', Item.date) == year
    expenses = db.session.query(func.sum(Item.amount), func.strftime('%Y-%m', Item.date)).filter(by_year).filter(Item.is_expense == True).group_by(func.strftime('%Y-%m', Item.date)).all()
    cols = [{'id':'', 'label':'Month', 'type':'string'}, {'id':'', 'label':'Expense', 'type':'number'}]
    rows = []
    for (expense, month) in expenses:
        rows.append({'c': [{'v': month}, {'v': expense}]})
    return jsonify(cols=cols, rows=rows)

def get_last_n_days_import(n):
    return (Electricity.query.
            with_entities(func.strftime('%Y-%m-%dT00:00:00', Electricity.created_at).label('created_at'),
                          (func.max(Electricity.meter_180) - func.min(Electricity.meter_180))
                          .label('electricity_import'))
            .filter(Electricity.created_at > (datetime.now() - timedelta(days=n)))
            .group_by(func.strftime('%Y-%m-%d', Electricity.created_at))
            .all())

def get_yearly_series():
    """Returns a list of yearly generated energy for past years
    :return: list of yearly generated energy for past years
    """
    return (PVData.query
            .with_entities(func.strftime('%Y', PVData.created_at).label('year'),
                           (func.max(PVData.total_energy) - func.min(PVData.total_energy)).label('yearly_output'))
            .group_by(func.strftime('%Y', PVData.created_at))
            .all())

 def stravenky(cls,month,card_number):
     narok=0
     form = db.session.query( func.strftime('%Y-%m-%d', cls.time).label("date"),func.max(func.strftime('%H:%M', cls.time)).label("Max"),\
                              func.min(func.strftime('%H:%M', cls.time)).label("Min"),( func.max(cls.time) - func.min(cls.time)).label("Rozdil"))\
         .filter(func.strftime('%Y-%m', cls.time) == month).filter(cls.card_number == card_number).group_by(func.strftime('%Y-%m-%d', cls.time)).all()
     for n in form:
         if n.rozdil >= 3:
             narok = narok + 1
     return narok

def get_yearly_data(year):
    """
    :param year: year of the data
    :return: returns an array of monthly energy for a given year
    """
    return PVData.query.with_entities((func.max(PVData.total_energy) - func.min(PVData.total_energy)).label(
        'total_energy')).filter(
        func.strftime('%Y', PVData.created_at) == str(year)).group_by(
        func.strftime('%Y-%m', PVData.created_at)).all()

def get_daily_operating_hours_last_n_days(n=30):
    """
    Returns an array with the daily operating hours for the last n days.
    """
    end_date = datetime.utcnow()
    start_date = (end_date - timedelta(days=n)).strftime('%Y-%m-%d')
    return Kessel.query.with_entities((func.max(Kessel.operating_hours) - func.min(Kessel.operating_hours)).label('operating_hours'),
                                            func.strftime('%Y-%m-%d', Kessel.created_at).label('created_at')).filter(
        Kessel.created_at >= start_date).filter(
        Kessel.created_at <= end_date).group_by(func.strftime('%Y-%m-%d', Kessel.created_at)).all()

def tbl_insdata(od, do):
    # data = db.session.query( func.strftime('%Y-%m', Card.time).label("time")).filter_by(card_number=current_user.card_number).group_by(func.strftime('%Y-%m', Card.time))
    if od == 0 and do == 0:
        data = db.session.query(Card.id, Card.card_number, func.strftime('%Y-%m', Card.time).label("time")).all()
    else:
        data = db.session.query(Card.id, Card.card_number, func.strftime('%Y-%m', Card.time).label("time")).slice(od,
                                                                                                                  do)
    pole = ['id', 'time', 'card_number']
    result = [{col: getattr(d, col) for col in pole} for d in data]
    return jsonify(data=result)

def get_daily_pellets_consumption_last_n_days(n=30):
    """
    Returns an array with the daily pellets consumption for the last n days.
    """
    end_date = datetime.utcnow()
    start_date = (end_date - timedelta(days=n)).strftime('%Y-%m-%d')
    return Kessel.query.with_entities((func.max(Kessel.pellets_total) - func.min(Kessel.pellets_total)).label('pellets_consumption'),
                                            func.strftime('%Y-%m-%d', Kessel.created_at).label('created_at')).filter(
        Kessel.created_at >= start_date).filter(
        Kessel.created_at <= end_date).group_by(func.strftime('%Y-%m-%d', Kessel.created_at)).all()

def mesicni_vypis_alluser(mesic):
    # form=Card.find_by_number(current_user.card_number)
    # form = db.session.query(Card.time).filter_by(card_number=current_user.card_number)
    form = db.session.query(func.strftime('%Y-%m-%d', Card.time).label("date"),
                            func.max(func.strftime('%H:%M', Card.time)).label("Max"), \
                            func.min(func.strftime('%H:%M', Card.time)).label("Min"),
                            (func.max(Card.time) - func.min(Card.time)).label("Rozdil")) \
        .filter(func.strftime('%Y-%-m', Card.time) == mesic).group_by(func.strftime('%Y-%m-%d', Card.time))
    # .group_by([func.day(Card.time)])
    return render_template("auth/mesicni_vypisy.tmpl", form=form, user=current_user)

 def monthly_hours(self):
     qs = (
         Worktime.query
         .with_entities(func.strftime('%Y-%m', Worktime.day).label('month'),
                        func.sum(Worktime.hours))
         .filter_by(project=self)
         .group_by(func.strftime('%Y-%m', Worktime.day))
         .all()
     )
     qs.sort(key=lambda a: a[0], reverse=True)
     return qs

def get_last_n_days(n):
    """Returns a list of daily yields
    :param n: number of last days
    :return: list of daily yields
    """
    return (PVData.query.
            with_entities(func.strftime('%Y-%m-%dT00:00:00', PVData.created_at).label('created_at'),
                          func.max(PVData.daily_energy).label('daily_energy'))
            .filter(PVData.created_at > (datetime.now() - timedelta(days=n)))
            .group_by(func.strftime('%Y-%m-%d', PVData.created_at))
            .all())

def get_7_day_max_energy_series(current_date):
    """
    :param current_date: pivot date of the energy series
    :return: theoretical maximum energy series ± 3 days around current date
    """
    return PVData.query.with_entities(func.strftime('%H:%M:00', PVData.created_at).label('pvdata_created_at'),
                                      func.max(PVData.current_power).label('pv_max')).filter(
        PVData.created_at >= (current_date - timedelta(days=3)).strftime('%Y-%m-%d')).filter(
        PVData.created_at <= (current_date + timedelta(days=3)).strftime('%Y-%m-%d')).filter(
        PVData.current_power > 0).group_by(
        func.strftime('%H:%M:00', PVData.created_at)).all()

def report():
    year = func.strftime('%Y', Item.date)
    years = db.session.query(year).group_by(year).all()

    month = func.strftime('%Y-%m', Item.date)
    months = db.session.query(month).group_by(month).all()
    results = []
    for m in months:
        tokens = m[0].split('-')
        results.append((tokens[0], tokens[1]))
    return render_template('report.html', years=years, months=results)

def main():
    session = create_session()
    date_format = '%Y-%m-%W'
    results = session.query(
        func.strftime(date_format, Message.sent),
        func.sum(Message.polarity),
        func.count(Message.id),
        func.strftime('%m-%d-%Y', Message.sent),
    ).join(Contact).filter(
        and_(
            Contact.name == "Me",
            Message.polarity != 0,
            Message.subjectivity >= .9,
        )
    ).group_by(
        func.strftime(date_format, Message.sent),
    ).order_by(
        Message.sent.asc()
    ).all()

    # width = .35
    axis_every = 10 * 1

    x_axis_labels = [
        x[1][3] for x in enumerate(results) if x[0] % axis_every == 0]
    x_axis = [x[0] for x in enumerate(results)]
    y_axis = [x[1]/x[2] for x in results]
    colors = map(lambda v: {True: 'g', False: 'r'}[v > 0], y_axis)
    #y_axis = map(abs, y_axis)

    print "Samples: {}".format(len(x_axis))

    plt.rcParams["figure.figsize"][0] = 21.0
    plt.rcParams["figure.figsize"][1] = 9.0
    plt.rcParams["figure.autolayout"] = True

    plt.bar(x_axis, y_axis, linewidth=0, color=colors)
    plt.grid(b=True, which='major', linestyle='-')
    plt.title('Positive or Negative calculated sentiment of sent text messages per week')
    plt.ylabel("Calculated percentage of sentiment per message")
    plt.xlabel("Date")
    plt.xticks(
        np.arange(min(x_axis), max(x_axis), axis_every),
        x_axis_labels,
        rotation=35,
        horizontalalignment='center',
    )
    plt.xlim([min(x_axis) - 0.5, max(x_axis) + 0.5])
    plt.ylim([min(y_axis) - .1, max(y_axis) + .1])

    print plt.rcParams["figure.figsize"]

    plt.savefig('output.png')

def example1():
    """
    func.strftime practice:

    seleft dob that month = day (in this case, dob = Jan 1th)
    """
    result = ses.query(Student.student_id, Student.dob) \
        .filter(func.strftime("%m", Student.dob) == func.strftime("%d", Student.dob)) \
        .all()

    for row in result:
        print(row)

def account_expenses(account, period):
    ''' total amount expendited during that period '''
    total = session.query(func.sum(Operation.amount))\
               .filter_by(account=account)\
               .filter(func.strftime('%Y-%m-%d', Operation.invoice_date)\
                       > period.start_on.strftime('%Y-%m-%d'))\
               .filter(func.strftime('%Y-%m-%d', Operation.invoice_date)\
                       < period.end_on.strftime('%Y-%m-%d'))\
               .scalar()
    if total:
        return total
    else:
        return 0

 def list_events(self):
     out = []
     session = self.session_mgr()
     for row in session.query(
             HALDB.Event.type, HALDB.Event.src, HALDB.Event.msg, HALDB.Event.severity,
             func.strftime('%Y-%m-%d', HALDB.Event.created_on), func.count()) \
             .group_by(HALDB.Event.type, HALDB.Event.src, HALDB.Event.msg, HALDB.Event.severity,
                       func.strftime('%Y-%m-%d', HALDB.Event.created_on)) \
             .having(func.count() > 0) \
             .order_by(desc(func.strftime('%Y-%m-%d', HALDB.Event.created_on))) \
             .all():
         out.append({'source': row[1], 'type': row[0], 'msg': row[2], 'count': row[5],
                     'severity': row[3], 'period': row[4]})
     self.session_mgr.remove()
     return out

def monthly_list_by_category(year, month, category):
    c = Category.query.filter_by(name=category).first()
    by_month = func.strftime('%Y-%m', Item.date) == '%s-%s' % (year, month)
    items = db.session.query(Item).filter(Item.is_expense == True).filter(by_month).filter_by(category=c).all()
    expense = db.session.query(func.sum(Item.amount)).filter(Item.is_expense == True).filter(by_month).first()[0]

    return render_template('monthly_list_by_category.html', items=items, year=year, month=month, category=category, total=expense)

def get_last_years_energy():
    """
    :return: total energy yielded in the previous year
    """
    current_year = datetime.now().year
    return PVData.query.with_entities(PVData.total_energy).filter(
        func.strftime('%Y', PVData.created_at) == str(current_year - 1)).order_by(PVData.id.desc()).first()