Пример #1
1
    def _perform_sql(self):

        filter = {}
        filter["last_check"] = "now()"
        query = (
            self.session.query(self.tables.monitor_obs)
            .add_column((func.timediff(self.tables.monitor_obs.last_check, func.now())).label("test"))
            .filter(self.tables.monitor_obs.locked == False)
            .filter(
                func.timestampdiff(MINUTE, self.tables.monitor_obs.last_check, func.now())
                <= func.TIMESTAMP(text("0000-00-00 00:") + self.tables.monitor_obs.frequency + ":0")
            )
            .order_by("last_check")
        )
        logger.debug(query)
Пример #2
0
def by_day():
    form = DateForm()
    day = None

    if request.method == 'GET':
        lday = db.session.query(Kontrol.date).order_by(Kontrol.date.desc()).limit(1).scalar()
        day = day_interval(str(lday.date()))

    if request.method == 'POST':
        if form.validate():
            sday = request.form['date']
            day = day_interval(sday)

    entries = None
    if not day:
        flash(u'Ошибка в дате.', category="error")
    else:
        form.date.data = day[0]
        entries = db.session.query(Kontrol.id
                                   , Kontrol.date
                                   # , Kontrol.date2
                                   , func.timediff(Kontrol.date2, Kontrol.date).label("dt")
                                   , Kontrol.category
                                   , Kontrol.comment
                                   , Kontrol.message
                                   , Kontrol.object
                                   , Kontrol.obj_id
                                   , Kontrol.prim) \
            .filter(Kontrol.date > day[0]) \
            .filter(Kontrol.date < day[1]) \
            .filter(Kontrol.category!=2) \
            .order_by(Kontrol.date.desc()).all()
    return render_template('kontrol/kontrol_by_day.html', form=form, day=day, entries=entries)
Пример #3
0
def now_playing():
    if engine.name == 'sqlite':
        query = session.query(User).join(Track).filter(
            func.strftime('%s', now()) -
            func.strftime('%s', User.last_play_date) < Track.duration * 2)
    elif engine.name == 'postgresql':
        query = session.query(User).join(Track).filter(
            func.date_part('epoch',
                           func.now() - User.last_play_date) < Track.duration *
            2)
    else:
        query = session.query(User).join(Track).filter(
            func.timediff(func.now(), User.last_play_date) < Track.duration *
            2)

    return request.formatter({
        'nowPlaying': {
            'entry': [
                dict(
                    u.last_play.as_subsonic_child(request.user).items() + {
                        'username': u.name,
                        'minutesAgo': (now() - u.last_play_date).seconds / 60,
                        'playerId': 0
                    }.items()) for u in query
            ]
        }
    })
Пример #4
0
def check_upload (diff):
    """Check upload time of first revision with created time for change.

    For each change, the upload time of the first revision (patchset) is
    matched against the created time for the change. Those changes with
    more than diff mins. of difference are shown.

    Parameters
    ----------

    diff: int
        Minutes of difference considered.

    """

    revs = session.query(label ("daterev",
                                func.min(DB.Revision.date)),
                         label ("change_id",
                                DB.Revision.change_id),
                         label ("number",
                                DB.Change.number)) \
          .filter (DB.Revision.change_id == DB.Change.uid) \
          .group_by("change_id") \
          .subquery()
    res = session.query(
        label ("number",
               revs.c.number),
        label ("created",
               DB.Change.created),
        label ("daterev",
               revs.c.daterev)
        ) \
        .filter(and_(
                func.abs(func.timediff(
                        DB.Change.created,
                        revs.c.daterev) > timedelta (minutes = diff)),
                 DB.Change.uid == revs.c.change_id)) \
       .order_by (func.datediff(DB.Change.created, revs.c.daterev),
                  func.timediff(DB.Change.created, revs.c.daterev))
    messages = res.all()
    for message in messages:
        print "Change " + str(message.number) + ": " + \
            str(message.created - message.daterev) + \
             " -- " + str(message.created) + " (created), " + \
             str(message.daterev) + " (first revision)"
    print "Total changes with discrepancy: " + str (len(messages))
Пример #5
0
def obj_id(id):
    rec = db.session.query(Kontrol.id
                           , Kontrol.date
                           , func.timediff(Kontrol.date2, Kontrol.date).label("dt")
                           , Kontrol.category
                           , Kontrol.comment
                           , Kontrol.message
                           , Kontrol.object
                           , Kontrol.obj_id
                           , Kontrol.prim) \
        .filter(Kontrol.obj_id == id) \
        .order_by(Kontrol.date.desc()).limit(100).all()
    return render_template('kontrol/obj_id.html', ent=rec)
Пример #6
0
def last():
    curr = Kontrol.query.filter(or_(Kontrol.date2 == None, Kontrol.date2 == 0)).order_by(Kontrol.date.desc()).all()
    last = db.session.query(Kontrol.id
                            , Kontrol.date
                            # , Kontrol.date2
                            , func.timediff(Kontrol.date2, Kontrol.date).label("dt")
                            , Kontrol.category
                            , Kontrol.comment
                            , Kontrol.message
                            , Kontrol.object
                            , Kontrol.obj_id
                            , Kontrol.prim) \
        .filter(Kontrol.category!=2) \
        .order_by(Kontrol.date.desc()).limit(32).all()
    return render_template('kontrol/kontrol_last.html', ent1=curr, ent2=last)
Пример #7
0
def now_playing():
	if engine.name == 'sqlite':
		query = User.query.join(Track).filter(func.strftime('%s', now()) - func.strftime('%s', User.last_play_date) < Track.duration * 2)
	elif engine.name == 'postgresql':
		query = User.query.join(Track).filter(func.date_part('epoch', func.now() - User.last_play_date) < Track.duration * 2)
	else:
		query = User.query.join(Track).filter(func.timediff(func.now(), User.last_play_date) < Track.duration * 2)

	return request.formatter({
		'nowPlaying': {
			'entry': [ dict(
				u.last_play.as_subsonic_child(request.user).items() +
				{ 'username': u.name, 'minutesAgo': (now() - u.last_play_date).seconds / 60, 'playerId': 0 }.items()
			) for u in query ]
		}
	})
 def print_body_for_user(self, authenticated_user):
     now = datetime.now()
     end_date = now - timedelta(hours=now.hour, minutes=now.minute, seconds=now.second, microseconds=now.microsecond)
     start_date = end_date - timedelta(days=self.num_days)
     db_session = DB_Session_Factory.get_db_session()
     time_spent = {}
     interviewer_emails = []
     coding_interview_documented_time = 7200
     coding_interview_actual_time = 1800
     interview_documented_length = func.time_to_sec(func.timediff(Interview.end_time, Interview.start_time))
     for time_spent_info in db_session.query(func.date(Interview.start_time), Interview.interviewer_email, func.sum(func.IF(interview_documented_length == coding_interview_documented_time, coding_interview_actual_time, interview_documented_length))).group_by(func.date(Interview.start_time), Interview.interviewer_email).filter(Interview.start_time > start_date, Interview.end_time < end_date).all():
         [date, email, secs_spent] = time_spent_info
         date_str = date.isoformat()
         if time_spent.get(date_str) is None:
             time_spent[date_str] = {}
         time_spent[date_str][email] = int(secs_spent)
         interviewer_emails.append(email)
     interviewers = {}
     for interviewer in db_session.query(Interviewer).filter(Interviewer.email.in_(interviewer_emails)).all():
         interviewers[interviewer.email] = interviewer.dict_representation()
     print json.dumps({
         'time_spent' : time_spent,
         'interviewers' : interviewers
     })
    def watchWhale(self):
        @retry
        def requestWhaleBalance():
            r = requests.get('https://verge-blockchain.info/ext/getbalance/DQkwDpRYUyNNnoEZDf5Cb3QVazh4FuPRs9',verify=False)
            return(r)
        r = requestWhaleBalance()
        
        while True:    
            sleep(0.5)

            ## Old text is the text before a new query
            old_text = r.text
            ## If server error, text contains html. Condition stops exception from
            ## failure to convert error message to decimal
            if old_text.find('<') == -1:
                old_balance = Decimal(old_text)

            r = requestWhaleBalance()
                    
            transaction_time = datetime.now()

            ## If text is number and not error message, which contains HTML
            if r.text.find('<') == -1 and old_text.find('<') == -1:
                new_balance = Decimal(r.text)

                ## If the text retrieved is new...
                if r.text != old_text:

                    new_balance = Decimal(r.text)

                    if (new_balance - old_balance) < 0:
                        action= 'sold'
                    else:
                        action = 'bought'

                    quantity = new_balance - old_balance
                    
                    ## Prevents errors from being registered as transactions
                    if quantity != 0:
                        change = abs(quantity)
                        percent = change / old_balance

                        ## REPLACE WITH API CALL!
                        ## Gets the aggregated price nearest to the time associated with the trade
                        result = self.session.query(Aggregated_prices).filter(Aggregated_prices.price != None).order_by(func.abs( func.timediff(transaction_time,Aggregated_prices.endTime) ).asc() )[0]
                        approx_price = result.price
#                       self.cmd(('''select * from binance.aggregated_prices order by ABS(TIMEDIFF(%(transaction_time)s, endTime )) LIMIT 1;'''),params=params)[0]

                        ## Percentile (may be move to separate thread)
                        pctl = percentile(quantity)
                        message = 'Whale {0} {1} XVG ( {2}% of holdings) \nTime: {3}\nApprox_price: {4} \nNormed: {5}'.format(action,int(change),
                                                                                                                             percent,transaction_time,approx_price,pctl)
#                        if pctl > 50
                        try:
                            sendTelegram(message)
                        except:
                            print("sendTelegram failed at " + str(datetime.now()))

                        ## Saves API data and nearest known price
                        params = {'new_balance':new_balance,
                         'old_balance':old_balance,
                         'quantity':quantity,
                         'transaction_time':transaction_time,'approx_price':approx_price}

                        self.session.add(Whaletrades(**params))
                        self.session.commit()
Пример #10
0
    def reports(self):
        c.title = "Sales Dashboard"
        c.items = c.user.programs

        if len(c.items) > 0:
            nullToken = c.nullToken
            
            """Each row is (program_id, date, numPurchases, numAffiliatePurchases, affiliateTotal, purchaseTotal """
            #JOIN was unnecessary
            #c.orders = Session_.query(Order.product_id, func.datediff(Order.date, c.lowerDate), func.count('*').label('total'), func.sum(Order.isReturned, type_=Integer).label('numReturns'), func.count(Order.affiliate_user_id).label('numAffiliatePurchases')).join((Product, and_(Order.seller_user_id==c.user.id, Order.product_id==Product.id))).filter(Order.date >= c.lowerDate).filter(Order.date < c.upperDate).group_by(Order.product_id).group_by(Order.date).all()
            c.orders = Session_.query(Order.program_id,
                                      func.datediff(Order.date, c.lowerDate),
                                      func.count('*').label('numPurchases'), 
                                      func.count(Order.affiliate_user_id).label('numAffiliatePurchases'),
                                      func.sum(case([(Order.affiliate_user_id != None, Order.amount)], else_=0)).label('affiliateTotal'),
                                      func.sum(Order.amount).label('purchaseTotal'),
                                      ).filter(Order.merchant_user_id==c.user.id).filter(Order.date >= c.lowerDate).filter(Order.date <= c.upperDate).group_by(Order.program_id).group_by(Order.date).all()
            #c.days = Session_.query(func.datediff(Transaction.date, c.lowerDate), func.sum(Transaction.amount)).filter(Transaction.user_id==c.user.id).filter(Transaction.date >= c.lowerDate).filter(Transaction.date < c.upperDate).group_by(Transaction.date).all()
 
            """SINGLE PRODUCT FOR OWNER"""
            #c.orders = Session_.query(Order.product_id, func.datediff(Order.date, c.lowerDate), func.count('*').label('total'), func.sum(Order.isReturned, type_=Integer).label('numReturns'), func.count(Order.affiliate_user_id).label('numAffiliatePurchases')).filter(Order.product_id==c.product.id).filter(Order.date >= c.lowerDate).filter(Order.date < c.upperDate).group_by(Order.product_id).group_by(Order.date).all()
            #c.impressions = Session_.query(Impression.product_id, func.datediff(Impression.date, c.lowerDate), func.count('*').label('total'), func.sum(case([(Impression.affiliate_ts != nullToken, 1)],else_=0)), func.sum(case([(Impression.order_ts != nullToken, 1)],else_=0)), func.sum(case([(and_(Impression.affiliate_ts != nullToken, Impression.order_ts != nullToken), 1)],else_=0)).label('buyConversions'), func.sum(case([(and_(Impression.purchase_ts != nullToken, Impression.order_ts != nullToken), 1)],else_=0)).label('purchaseConversions')).filter(Impression.product_id==c.product.id).filter(Impression.date >= c.lowerDate).filter(Impression.date < c.upperDate).group_by(Impression.product_id).group_by(Impression.date).all()
            
            
            """Each row is (product_id, date, affiliateViews, conversionTime)"""
            c.impressions = Session_.query(Impression.program_id, 
                                           func.datediff(Impression.date, c.lowerDate), 
                                           func.sum(case([(Impression.affiliate_ts != nullToken, 1)],else_=0)), 
                                           func.avg(case([(and_(Impression.purchase_ts != nullToken, Impression.affiliate_ts != nullToken), func.time_to_sec(func.timediff(Impression.purchase_ts,Impression.affiliate_ts)))],else_=0), 
        
                                    ).label('purchaseConversions')).join((Program, and_(c.user.id==Program.merchant_user_id, Impression.program_id==Program.id))).filter(Impression.date >= c.lowerDate).filter(Impression.date <= c.upperDate).group_by(Impression.program_id).group_by(Impression.date).all()
            logging.info(c.impressions)
            self.__Temp(c)
        return render('/account/accountSummary.mak')