def make_grouping(cls, logger, grouping_info):
group_type, group_args, group_name = grouping_info
grouping = None
if group_type == "extract":
subfield, field_name = group_args
real_field = getattr(cls, field_name, None)
if real_field:
if subfield == 'ampm':
grouping = case(whens = [(cast(extract('hour', real_field), Integer()) > 12, 1),], else_ = 0).label(group_name)
else:
grouping = cast(extract(subfield, real_field), Integer()).label(group_name)
else:
logger.error("Invalid grouping %s (%s)", grouping_info, cls)
elif group_type == "date_trunc":
subfield, field_name = group_args
real_field = getattr(cls, field_name, None)
if real_field:
grouping = func.date_trunc(subfield, real_field)
grouping = grouping.label(group_name)
else:
logger.error("Invalid grouping %s (%s)", grouping_info, cls)
elif group_type == "func":
logger.error("Grouping by func not implemented yet")
elif group_type == 'coalesce_trim':
# trim(coalesce(field_name, ''))
field_name = group_args.get('field', group_name)
real_field = getattr(cls, field_name, None)
if real_field:
grouping = func.coalesce(real_field, group_args.get('coalesce_to', ''))
if group_args.get('trim', True):
grouping = func.trim(grouping)
grouping = grouping.label(group_name)
else:
logger.error("Invalid grouping %s (%s)", grouping_info, cls)
else:
logger.error("Unknown grouping type %s", group_type)
return grouping
def list_ecriture(db, id=None, nom=None, compte_id=None, sum=None, month=None):
""" List compte
Filter to use :
filter = [1-9]+ / sum : give number or sum
sort = field1,[field2,...] : sort by field
field = field1,[field2,...] : field to return
page = [1-9]+ / first / last : print by page
valide = yes / no : print valide or not valide
"""
# return sums of account
if sum:
ecritures = db.query(func.count(Ecriture.nom).label("nombre"),
func.sum(Montant.montant/100.0).label("somme")
).\
join(Ecriture.montant).\
filter(Ecriture.compte_id == compte_id).\
one()
return dumps({'somme': "%0.2f" % ecritures.somme if ecritures.somme is not None else 0,
'nombre': "%d" % ecritures.nombre,
}
)
if month:
ecriture = aliased(Ecriture, name="ecriture_full")
debit = db.query(func.sum(Montant.montant/100.0)).\
select_from(Ecriture).\
join(Ecriture.montant).\
filter(Ecriture.compte_id == compte_id).\
filter(extract('year', Ecriture.date) == 2015).\
filter(extract('month', Ecriture.date) == extract('month', ecriture.date)).\
filter(Montant.montant < 0).\
label("debit")
credit = db.query(func.sum(Montant.montant/100.0)).\
select_from(Ecriture).\
join(Ecriture.montant).\
filter(Ecriture.compte_id == compte_id).\
filter(extract('year', Ecriture.date) == 2015).\
filter(extract('month', Ecriture.date) == extract('month', ecriture.date)).\
filter(Montant.montant > 0).\
label("credit")
ecritures = db.query(extract('month', ecriture.date).label("date"),
debit,
credit
).\
join(ecriture.montant).\
filter(ecriture.compte_id == compte_id).\
filter(extract('year', ecriture.date) == 2015).\
group_by(extract('month', ecriture.date).label("date")).\
all()
list_month_debit = [0 for number in range(12)]
list_month_credit = [0 for number in range(12)]
cpt = 0
for ecriture in ecritures:
cpt += 1
print ecriture, cpt
list_month_debit[ecriture.date-1] = float(ecriture.debit) if ecriture.debit != None else 0
list_month_credit[ecriture.date-1] = float(ecriture.credit) if ecriture.credit != None else 0
return dumps([list_month_debit,list_month_credit])
filter = {}
if id:
filter['id'] = id
elif nom:
filter['nom'] = nom
elif compte_id:
filter['compte_id'] = compte_id
else:
filter = App.get_filter(request.query.filter)
sort = App.get_sort(request.query.sort)
ecritures = db.query(Ecriture.id.label("id"),
func.trim(Ecriture.nom).label("nom"),
Ecriture.date.label("date"),
Montant.montant.label("montant"),
Ecriture.type.label("type"),
Categorie.nom.label("categorie"),
Categorie.id.label("categorie_id"),
Montant.description.label("description"),
Montant.id.label("montant_id"),
Ecriture.valide.label("valide"),
Ecriture.compte_id.label("compte_id"),
).\
join(Ecriture.montant).\
join(Montant.categorie)
if filter:
for column, value in filter.iteritems():
if not isinstance(value, list):
ecritures = ecritures.filter(getattr(Ecriture, column) == value)
else:
ecritures = ecritures.filter(getattr(Ecriture, column).in_(value))
if sort:
for column in sort:
ecritures = ecritures.order_by(getattr(Ecriture, column))
else:
ecritures = ecritures.order_by(desc(Ecriture.date))
try:
ecritures = ecritures.all()
#if re.match(r"^\d+$", filter):
# ecritures = ecritures[:int(filter):]
except NoResultFound:
abort(404, "ID not found")
if not ecritures:
abort(404, "ID not found")
list_ecritures = []
attributs = App.get_attribut(request.query.attribut)
if attributs:
for ecriture in ecritures:
dict_attributs = {}
for attribut in attributs:
dict_attributs[attribut] = getattr(ecriture, attribut)
list_ecritures.append(dict_attributs)
else:
for ecriture in ecritures:
list_ecritures.append({'id': ecriture.id,
'nom': ecriture.nom,
'date': datetime.strftime(ecriture.date, "%Y/%m/%d"),
'type': ecriture.type,
'valide': ecriture.valide,
'categorie': ecriture.categorie,
'categorie_id': ecriture.categorie_id,
'montant': "%0.2f" % (ecriture.montant/100.0,),
'description': ecriture.description,
'montant_id': ecriture.montant_id,
'compte_id': ecriture.compte_id,
})
if request.query.get('skip') and request.query.get('top'):
return dumps({'count': len(list_ecritures),
'values': list_ecritures[request.query.get('skip',type=int):request.query.get('skip', type=int) + request.query.get('top', type=int)]
}
)
else:
return dumps(list_ecritures)
c = [
## date/time functions ##
#func.timeofday(), # for postgresql
func.localtime(),
func.current_timestamp(),
#func.date_part("month", func.now()), # for postgresql
func.now(),
## mathematical functions ##
func.pow(4, 2),
func.sqrt(441),
func.pi(),
func.floor(func.pi()),
func.ceil(func.pi()),
## string functions ##
func.lower("ABC"),
func.upper("abc"),
func.length("abc"),
func.trim(" ab c "),
#func.chr(65), # for postgresql
]
s = select(c)
rs = conn.execute(s)
print(rs.keys())
print(rs.fetchall())
def build_filter(
field_name, data_type, filter_type, inverted, value ):
if DEBUG_FILTERS:
logger.info('build filter: %r, %r, %r, %r, %r',
field_name, data_type, filter_type, inverted, value)
expression = None
col = column(field_name)
if data_type in ['integer', 'float', 'decimal']:
col = cast(col, sqlalchemy.sql.sqltypes.Numeric)
elif data_type == 'boolean':
col = cast(col, sqlalchemy.sql.sqltypes.Boolean)
if data_type == 'string':
col = cast(col, sqlalchemy.sql.sqltypes.Text)
if filter_type in ['exact', 'eq']:
if data_type == 'string':
value = str(value)
expression = col == value
if data_type == 'list':
expression = text(
"'%s'=any(string_to_array(%s,'%s'))" % (value, field_name, LIST_DELIMITER_SQL_ARRAY))
elif filter_type == 'about':
decimals = 0
if '.' in value:
decimals = len(value.split('.')[1])
expression = func.round(col, decimals) == value
if DEBUG_FILTERS:
logger.info(
'create "about" expression for: %r, %r, decimals %r',
field_name, value, decimals)
elif filter_type == 'contains':
if data_type == 'string':
value = str(value)
expression = col.contains(value)
elif filter_type == 'icontains':
if data_type == 'string':
value = str(value)
expression = col.ilike('%{value}%'.format(value=value))
elif filter_type == 'lt':
expression = col < value
elif filter_type == 'lte':
expression = col <= value
elif filter_type == 'gt':
expression = col > value
elif filter_type == 'gte':
expression = col >= value
elif filter_type == 'is_blank':
if data_type == 'string':
col = func.trim(col)
if value and str(value).lower() == 'true':
expression = col == None
if data_type == 'string':
expression = col == ''
else:
expression = col != None
if data_type == 'string':
col = func.trim(col)
if (data_type == 'string' or
data_type == 'list'):
expression = col != ''
elif filter_type == 'is_null':
if value and str(value).lower() == 'true':
expression = col == None
else:
expression = col != None
# TODO: test that col <> '' expression is created
elif filter_type == 'in':
if data_type == 'list': # NOTE: for the list type, interpret "in" as any of the
# given values are in the field
temp_expressions = []
for _val in value:
temp_expressions.append(col.ilike('%{value}%'.format(value=_val)))
expression = or_(*temp_expressions)
else:
expression = col.in_(value)
elif filter_type == 'ne':
if data_type == 'string':
value = str(value)
expression = col != value
elif filter_type == 'range':
if len(value) != 2:
logger.error('field: %r, val: %r, '
'range expression must be list of length 2',
field_name, value)
else:
expression = col.between(value[0], value[1], symmetric=True)
else:
logger.error(
'field: %r, unknown filter type: %r for value: %r',
field_name, filter_type, value)
if inverted:
expression = not_(expression)
return expression
def fullname(cls):
return func.trim(cls.firstname + " " + cls.webi_lastname)
def fullname(cls):
return func.trim(cls.firstname + " " + cls.webi_lastname)