def fetch_hourly(self, page, rows, sidx, sord='asc', _search='false',
searchOper=None, searchField=None, searchString=None, **kw):
''' Function called on AJAX request made by FlexGrid
Fetch data from DB, return the list of rows + total + current page
'''
if not in_any_group('admin','STATS'):
return dict(page=0, total=0, rows=[])
try:
page = int(page)
rows = int(rows)
offset = (page-1) * rows
except:
page = 1
rows = 24
offset = 0
log.info('fetch_hourly : page=%d, rows=%d, offset=%d, sidx=%s, sord=%s' % (
page, rows, offset, sidx, sord))
# Initialize data, in case no data is available for that time slice
data = [{'id': x, 'cell': ['%d h 00 < %d h 00' % (x, x+1), 0, None]}
for x in range(24)]
# Count calls by hour
if db_engine=='oracle':
req = func.to_char(CDR.calldate, 'HH24')
else: # PostgreSql
req = func.date_trunc('hour', cast(CDR.calldate, TIME))
cdrs = DBSession.query(req, func.count(req), func.sum(CDR.billsec))
if self.stats_type:
# Monthly stats
d = datetime.datetime.strptime(self.stats_type, '%m/%d/%Y')
if db_engine=='oracle':
cdrs = cdrs.filter(func.trunc(CDR.calldate, 'month') == \
func.trunc(d, 'month'))
else: # PostgreSql
cdrs = cdrs.filter(func.date_trunc('month', CDR.calldate) == \
func.date_trunc('month', d))
cdrs = cdrs.group_by(req)
# cdrs = cdrs.order_by(func.sum(CDR.billsec))
for i, c in enumerate(cdrs):
if db_engine=='oracle':
j = int(c[0])
else: # PostgreSql
j = c[0].seconds / 3600
data[j] = {'id': j, 'cell': ['%d h 00 < %d h 00' % (j,j+1), c[1], hms(c[2])]}
return dict(page=page, total=24, rows=data[offset:offset+page*rows])
def get_series_items_df(cls,
session,
from_date=None,
until_date=None,
market_type_code=None,
division_codes=None,
item_freq_type_code=None,
min_market_total_volume=None,
min_market_pre_off_volume=None,
max_mins_from_off_time=None,
market_location_code=None):
query = session.query(cls).join(ExchangeOddsSeries)
# Event data filters
if from_date:
query = safe_join(query, Market)
query = query.filter(Market.off_time >= from_date)
if until_date:
query = safe_join(query, Market)
query = query.filter(Market.off_time < until_date)
# Market filters
if market_type_code:
query = safe_join(query, Market)
query = query.filter(Market.market_type_code == market_type_code)
if market_location_code:
query = safe_join(query, Market)
query = query.filter(
Market.market_location_code == market_location_code)
if min_market_pre_off_volume:
query = safe_join(query, Market).filter(
Market.pre_off_volume >= min_market_pre_off_volume)
if min_market_total_volume:
query = safe_join(
query,
Market).filter(Market.total_volume >= min_market_total_volume)
# Division filters
if division_codes:
query = query.join(Event).filter(
Event.division_code.in_(division_codes))
# Time and frequency filters
if max_mins_from_off_time:
query = safe_join(query, Market)
query = query.filter(
func.trunc((extract('epoch', Market.off_time) -
extract('epoch', cls.published_datetime)) /
60) < max_mins_from_off_time)
if item_freq_type_code:
query = query.filter(
ExchangeOddsSeries.item_freq_type_code == item_freq_type_code)
return pd.read_sql(query.statement, query.session.bind)
def setup_class(cls):
global t, f, f2, ts, currenttime, metadata, default_generator
db = testing.db
metadata = MetaData(db)
default_generator = {'x': 50}
def mydefault():
default_generator['x'] += 1
return default_generator['x']
def myupdate_with_ctx(ctx):
conn = ctx.connection
return conn.execute(sa.select([sa.text('13')])).scalar()
def mydefault_using_connection(ctx):
conn = ctx.connection
try:
return conn.execute(sa.select([sa.text('12')])).scalar()
finally:
# ensure a "close()" on this connection does nothing,
# since its a "branched" connection
conn.close()
use_function_defaults = testing.against('postgresql', 'mssql', 'maxdb')
is_oracle = testing.against('oracle')
# select "count(1)" returns different results on different DBs also
# correct for "current_date" compatible as column default, value
# differences
currenttime = func.current_date(type_=sa.Date, bind=db)
if is_oracle:
ts = db.scalar(
sa.select([
func.trunc(func.sysdate(),
sa.literal_column("'DAY'"),
type_=sa.Date).label('today')
]))
assert isinstance(
ts, datetime.date) and not isinstance(ts, datetime.datetime)
f = sa.select([func.length('abcdef')], bind=db).scalar()
f2 = sa.select([func.length('abcdefghijk')], bind=db).scalar()
# TODO: engine propigation across nested functions not working
currenttime = func.trunc(currenttime,
sa.literal_column("'DAY'"),
bind=db,
type_=sa.Date)
def1 = currenttime
def2 = func.trunc(sa.text("sysdate"),
sa.literal_column("'DAY'"),
type_=sa.Date)
deftype = sa.Date
elif use_function_defaults:
f = sa.select([func.length('abcdef')], bind=db).scalar()
f2 = sa.select([func.length('abcdefghijk')], bind=db).scalar()
def1 = currenttime
deftype = sa.Date
if testing.against('maxdb'):
def2 = sa.text("curdate")
elif testing.against('mssql'):
def2 = sa.text("getdate()")
else:
def2 = sa.text("current_date")
ts = db.scalar(func.current_date())
else:
f = len('abcdef')
f2 = len('abcdefghijk')
def1 = def2 = "3"
ts = 3
deftype = Integer
t = Table(
'default_test1',
metadata,
# python function
Column('col1', Integer, primary_key=True, default=mydefault),
# python literal
Column('col2',
String(20),
default="imthedefault",
onupdate="im the update"),
# preexecute expression
Column('col3',
Integer,
default=func.length('abcdef'),
onupdate=func.length('abcdefghijk')),
# SQL-side default from sql expression
Column('col4', deftype, server_default=def1),
# SQL-side default from literal expression
Column('col5', deftype, server_default=def2),
# preexecute + update timestamp
Column('col6', sa.Date, default=currenttime, onupdate=currenttime),
Column('boolcol1', sa.Boolean, default=True),
Column('boolcol2', sa.Boolean, default=False),
# python function which uses ExecutionContext
Column('col7',
Integer,
default=mydefault_using_connection,
onupdate=myupdate_with_ctx),
# python builtin
Column('col8',
sa.Date,
default=datetime.date.today,
onupdate=datetime.date.today),
# combo
Column('col9', String(20), default='py', server_default='ddl'))
t.create()
def define_tables(cls, metadata):
default_generator = cls.default_generator = {"x": 50}
def mydefault():
default_generator["x"] += 1
return default_generator["x"]
def myupdate_with_ctx(ctx):
conn = ctx.connection
return conn.execute(sa.select([sa.text("13")])).scalar()
def mydefault_using_connection(ctx):
conn = ctx.connection
return conn.execute(sa.select([sa.text("12")])).scalar()
use_function_defaults = testing.against("postgresql", "mssql")
is_oracle = testing.against("oracle")
class MyClass(object):
@classmethod
def gen_default(cls, ctx):
return "hi"
class MyType(TypeDecorator):
impl = String(50)
def process_bind_param(self, value, dialect):
if value is not None:
value = "BIND" + value
return value
cls.f = 6
cls.f2 = 11
with testing.db.connect() as conn:
currenttime = cls.currenttime = func.current_date(type_=sa.Date)
if is_oracle:
ts = conn.scalar(
sa.select([
func.trunc(
func.current_timestamp(),
sa.literal_column("'DAY'"),
type_=sa.Date,
)
]))
currenttime = cls.currenttime = func.trunc(
currenttime, sa.literal_column("'DAY'"), type_=sa.Date)
def1 = currenttime
def2 = func.trunc(
sa.text("current_timestamp"),
sa.literal_column("'DAY'"),
type_=sa.Date,
)
deftype = sa.Date
elif use_function_defaults:
def1 = currenttime
deftype = sa.Date
if testing.against("mssql"):
def2 = sa.text("getdate()")
else:
def2 = sa.text("current_date")
ts = conn.scalar(func.current_date())
else:
def1 = def2 = "3"
ts = 3
deftype = Integer
cls.ts = ts
Table(
"default_test",
metadata,
# python function
Column("col1", Integer, primary_key=True, default=mydefault),
# python literal
Column(
"col2",
String(20),
default="imthedefault",
onupdate="im the update",
),
# preexecute expression
Column(
"col3",
Integer,
default=func.length("abcdef"),
onupdate=func.length("abcdefghijk"),
),
# SQL-side default from sql expression
Column("col4", deftype, server_default=def1),
# SQL-side default from literal expression
Column("col5", deftype, server_default=def2),
# preexecute + update timestamp
Column("col6", sa.Date, default=currenttime, onupdate=currenttime),
Column("boolcol1", sa.Boolean, default=True),
Column("boolcol2", sa.Boolean, default=False),
# python function which uses ExecutionContext
Column(
"col7",
Integer,
default=mydefault_using_connection,
onupdate=myupdate_with_ctx,
),
# python builtin
Column(
"col8",
sa.Date,
default=datetime.date.today,
onupdate=datetime.date.today,
),
# combo
Column("col9", String(20), default="py", server_default="ddl"),
# python method w/ context
Column("col10", String(20), default=MyClass.gen_default),
# fixed default w/ type that has bound processor
Column("col11", MyType(), default="foo"),
)
def setup_class(cls):
global t, f, f2, ts, currenttime, metadata, default_generator
db = testing.db
metadata = MetaData(db)
default_generator = {'x': 50}
def mydefault():
default_generator['x'] += 1
return default_generator['x']
def myupdate_with_ctx(ctx):
conn = ctx.connection
return conn.execute(sa.select([sa.text('13')])).scalar()
def mydefault_using_connection(ctx):
conn = ctx.connection
try:
return conn.execute(sa.select([sa.text('12')])).scalar()
finally:
# ensure a "close()" on this connection does nothing,
# since its a "branched" connection
conn.close()
use_function_defaults = testing.against('postgresql', 'mssql')
is_oracle = testing.against('oracle')
class MyClass(object):
@classmethod
def gen_default(cls, ctx):
return "hi"
# select "count(1)" returns different results on different DBs also
# correct for "current_date" compatible as column default, value
# differences
currenttime = func.current_date(type_=sa.Date, bind=db)
if is_oracle:
ts = db.scalar(sa.select([func.trunc(func.sysdate(), sa.literal_column("'DAY'"), type_=sa.Date).label('today')]))
assert isinstance(ts, datetime.date) and not isinstance(ts, datetime.datetime)
f = sa.select([func.length('abcdef')], bind=db).scalar()
f2 = sa.select([func.length('abcdefghijk')], bind=db).scalar()
# TODO: engine propigation across nested functions not working
currenttime = func.trunc(currenttime, sa.literal_column("'DAY'"), bind=db, type_=sa.Date)
def1 = currenttime
def2 = func.trunc(sa.text("sysdate"), sa.literal_column("'DAY'"), type_=sa.Date)
deftype = sa.Date
elif use_function_defaults:
f = sa.select([func.length('abcdef')], bind=db).scalar()
f2 = sa.select([func.length('abcdefghijk')], bind=db).scalar()
def1 = currenttime
deftype = sa.Date
if testing.against('mssql'):
def2 = sa.text("getdate()")
else:
def2 = sa.text("current_date")
ts = db.scalar(func.current_date())
else:
f = len('abcdef')
f2 = len('abcdefghijk')
def1 = def2 = "3"
ts = 3
deftype = Integer
t = Table('default_test1', metadata,
# python function
Column('col1', Integer, primary_key=True,
default=mydefault),
# python literal
Column('col2', String(20),
default="imthedefault",
onupdate="im the update"),
# preexecute expression
Column('col3', Integer,
default=func.length('abcdef'),
onupdate=func.length('abcdefghijk')),
# SQL-side default from sql expression
Column('col4', deftype,
server_default=def1),
# SQL-side default from literal expression
Column('col5', deftype,
server_default=def2),
# preexecute + update timestamp
Column('col6', sa.Date,
default=currenttime,
onupdate=currenttime),
Column('boolcol1', sa.Boolean, default=True),
Column('boolcol2', sa.Boolean, default=False),
# python function which uses ExecutionContext
Column('col7', Integer,
default=mydefault_using_connection,
onupdate=myupdate_with_ctx),
# python builtin
Column('col8', sa.Date,
default=datetime.date.today,
onupdate=datetime.date.today),
# combo
Column('col9', String(20),
default='py',
server_default='ddl'),
# python method w/ context
Column('col10', String(20), default=MyClass.gen_default)
)
t.create()
def csv(self, **kw):
if not in_any_group('admin', 'STATS'):
flash(u'Accès interdit')
redirect('/')
today = datetime.datetime.today()
filename = 'statistiques-' + today.strftime('%Y%m%d') + '.csv'
import StringIO
import csv
csvdata = StringIO.StringIO()
writer = csv.writer(csvdata)
# Global headers
if self.stats_type:
rt = 'quotidien ' + self.stats_type[0:2] + self.stats_type[5:]
col1 = 'Jour'
else:
rt = 'mensuel'
col1 = 'Mois'
writer.writerow(['Statistiques SVI'])
writer.writerow(['Date', today.strftime('%d/%m/%Y')])
writer.writerow(['Type de rapport', rt])
writer.writerow([])
cols = [col1]
if self.stats_type:
# Daily stats
d = datetime.datetime.strptime(self.stats_type, '%m/%d/%Y')
if db_engine=='oracle':
req = func.trunc(CDR.calldate, 'J')
else: # PostgreSql
req = func.date_trunc('day', CDR.calldate)
else:
# Monthly stats
if db_engine=='oracle':
req = func.trunc(CDR.calldate, 'month')
else: # PostgreSql
req = func.date_trunc('month', CDR.calldate)
# Order by date
cdrs = self.stats_req
cdrs = cdrs.order_by(req)
cols.append( (u'Appels\n(nombre)').encode('utf-8') )
cols.append( (u'Appels\n(durée)').encode('utf-8') )
writer.writerow(cols)
for c in cdrs.all():
row = []
if self.stats_type:
row.append(c[0].strftime('%d'))
else:
row.append(c[0].strftime('%m/%Y'))
row.append(c[1])
row.append(hms(c[2]))
writer.writerow(row)
rh = response.headers
rh['Content-Type'] = 'text/csv; charset=utf-8'
rh['Content-Disposition'] = str( (u'attachment; filename="%s"' % (
filename)).encode('utf-8') )
rh['Pragma'] = 'public' # for IE
rh['Cache-control'] = 'max-age=0' #for IE
return csvdata.getvalue()
def fetch(self, page, rows, sidx, sord='desc', _search='false',
searchOper=None, searchField=None, searchString=None, **kw):
''' Function called on AJAX request made by FlexGrid
Fetch data from DB, return the list of rows + total + current page
'''
if not in_any_group('admin','STATS'):
flash(u'Accès interdit !', 'error')
redirect('/')
try:
page = int(page)
rows = int(rows)
offset = (page-1) * rows
except:
page = 1
rows = 12
offset = 0
log.info('fetch : page=%d, rows=%d, offset=%d' % (page, rows, offset))
if self.stats_type:
# Daily stats
d = datetime.datetime.strptime(self.stats_type, '%m/%d/%Y')
if db_engine=='oracle':
req = func.trunc(CDR.calldate, 'J')
else: # PostgreSql
req = func.date_trunc('day', CDR.calldate)
cdrs = DBSession.query(req, func.count(req), func.sum(CDR.billsec))
if db_engine=='oracle':
cdrs = cdrs.filter(func.trunc(CDR.calldate, 'month') == \
func.trunc(d, 'month'))
else: # PostgreSql
cdrs = cdrs.filter(func.date_trunc('month', CDR.calldate) == \
func.date_trunc('month', d))
cdrs = cdrs.group_by(req)
else:
# Monthly stats
if db_engine=='oracle':
req = func.trunc(CDR.calldate, 'month')
else: # PostgreSql
req = func.date_trunc('month', CDR.calldate)
cdrs = DBSession.query(req, func.count(req), func.sum(CDR.billsec))
cdrs = cdrs.group_by(req)
self.stats_req = cdrs
if sidx=='calls':
cdrs = cdrs.order_by(getattr(func.count(req),sord)())
elif sidx=='billsec':
cdrs = cdrs.order_by(getattr(func.sum(CDR.billsec),sord)())
else:
cdrs = cdrs.order_by(getattr(req,sord)())
cdrs = cdrs.offset(offset).limit(rows)
total = cdrs.count()/rows + 1
data = [{ 'id' : i,
'cell': cell(self.stats_type, i, c)
} for i, c in enumerate(cdrs.all()) ]
return dict(page=page, total=total, rows=data)
def precision_check(sess,longitude: float , latitude: float):
ress = sess.query(models.Pincode.pin).filter(func.trunc(models.Pincode.longitude,1) == func.trunc(longitude,1),
func.trunc(models.Pincode.latitude,1)==func.trunc(latitude,1)).first()
sess.close()
return ress
def setup_class(cls):
global t, f, f2, ts, currenttime, metadata, default_generator
db = testing.db
metadata = MetaData(db)
default_generator = {"x": 50}
def mydefault():
default_generator["x"] += 1
return default_generator["x"]
def myupdate_with_ctx(ctx):
conn = ctx.connection
return conn.execute(sa.select([sa.text("13")])).scalar()
def mydefault_using_connection(ctx):
conn = ctx.connection
try:
return conn.execute(sa.select([sa.text("12")])).scalar()
finally:
# ensure a "close()" on this connection does nothing,
# since its a "branched" connection
conn.close()
use_function_defaults = testing.against("postgresql", "mssql", "maxdb")
is_oracle = testing.against("oracle")
# select "count(1)" returns different results on different DBs also
# correct for "current_date" compatible as column default, value
# differences
currenttime = func.current_date(type_=sa.Date, bind=db)
if is_oracle:
ts = db.scalar(
sa.select([func.trunc(func.sysdate(), sa.literal_column("'DAY'"), type_=sa.Date).label("today")])
)
assert isinstance(ts, datetime.date) and not isinstance(ts, datetime.datetime)
f = sa.select([func.length("abcdef")], bind=db).scalar()
f2 = sa.select([func.length("abcdefghijk")], bind=db).scalar()
# TODO: engine propigation across nested functions not working
currenttime = func.trunc(currenttime, sa.literal_column("'DAY'"), bind=db, type_=sa.Date)
def1 = currenttime
def2 = func.trunc(sa.text("sysdate"), sa.literal_column("'DAY'"), type_=sa.Date)
deftype = sa.Date
elif use_function_defaults:
f = sa.select([func.length("abcdef")], bind=db).scalar()
f2 = sa.select([func.length("abcdefghijk")], bind=db).scalar()
def1 = currenttime
deftype = sa.Date
if testing.against("maxdb"):
def2 = sa.text("curdate")
elif testing.against("mssql"):
def2 = sa.text("getdate()")
else:
def2 = sa.text("current_date")
ts = db.scalar(func.current_date())
else:
f = len("abcdef")
f2 = len("abcdefghijk")
def1 = def2 = "3"
ts = 3
deftype = Integer
t = Table(
"default_test1",
metadata,
# python function
Column("col1", Integer, primary_key=True, default=mydefault),
# python literal
Column("col2", String(20), default="imthedefault", onupdate="im the update"),
# preexecute expression
Column("col3", Integer, default=func.length("abcdef"), onupdate=func.length("abcdefghijk")),
# SQL-side default from sql expression
Column("col4", deftype, server_default=def1),
# SQL-side default from literal expression
Column("col5", deftype, server_default=def2),
# preexecute + update timestamp
Column("col6", sa.Date, default=currenttime, onupdate=currenttime),
Column("boolcol1", sa.Boolean, default=True),
Column("boolcol2", sa.Boolean, default=False),
# python function which uses ExecutionContext
Column("col7", Integer, default=mydefault_using_connection, onupdate=myupdate_with_ctx),
# python builtin
Column("col8", sa.Date, default=datetime.date.today, onupdate=datetime.date.today),
# combo
Column("col9", String(20), default="py", server_default="ddl"),
)
t.create()
def age_in_minutes(self, now=datetime.utcnow()):
return func.trunc(
extract('epoch', now) -
extract('epoch', self.creation_datetime) / 60)
