def list_compte(db, id=None, nom=None, banque_id=None):
""" List compte """
filter = {}
if id:
filter["id"] = id
elif nom:
filter["nom"] = nom
elif banque_id:
filter["banque_id"] = banque_id
else:
filter = App.get_filter(request.query.filter)
sort = App.get_sort(request.query.sort)
comptes = db.query(Compte)
if filter:
for column, value in filter.iteritems():
if not isinstance(value, list):
comptes = comptes.filter(getattr(Compte, column) == value)
else:
comptes = comptes.filter(getattr(Compte, column).in_(value))
if sort:
for column in sort:
comptes = comptes.order_by(getattr(Compte, column))
else:
comptes = comptes.order_by(Compte.nom)
try:
comptes = comptes.all()
except NoResultFound:
abort(404, "ID not found")
if not comptes:
abort(404, "ID not found")
list_comptes = []
attributs = App.get_attribut(request.query.attribut)
if attributs:
for compte in comptes:
dict_attributs = {}
for attribut in attributs:
dict_attributs[attribut] = getattr(compte, attribut)
list_comptes.append(dict_attributs)
else:
for compte in comptes:
list_comptes.append(
{
"id": compte.id,
"nom": compte.nom,
"numero": compte.numero,
"cle": compte.cle,
"type": compte.type,
"archive": compte.archive,
"banque_id": compte.banque_id,
}
)
return dumps(list_comptes)
def list_banque(db, id=None, nom=None):
""" Display information for banque """
filter = {}
if id:
filter["id"] = id
elif nom:
filter["nom"] = nom
else:
filter = App.get_filter(request.query.filter)
sort = App.get_sort(request.query.sort)
banques = db.query(Banque)
if filter:
for column, value in filter.iteritems():
if not isinstance(value, list):
banques = banques.filter(getattr(Banque, column) == value)
else:
banques = banques.filter(getattr(Banque, column).in_(value))
if sort:
for column in sort:
banques = banques.order_by(getattr(Banque, column))
else:
banques = banques.order_by(Banque.nom)
try:
banques = banques.all()
except NoResultFound:
abort(404, "ID not found")
if not banques:
abort(404, "ID not found")
list_banques = []
attributs = App.get_attribut(request.query.attribut)
if attributs:
for banque in banques:
dict_attributs = {}
for attribut in attributs:
dict_attributs[attribut] = getattr(banque, attribut)
list_banques.append(dict_attributs)
else:
for banque in banques:
list_banques.append(
{
"id": banque.id,
"nom": banque.nom,
"adresse": banque.adresse,
"ville": banque.ville,
"cp": banque.cp,
"pays": banque.pays,
"cle": banque.cle_controle,
"code_banque": banque.code_banque,
"code_guichet": banque.code_guichet,
}
)
return dumps(list_banques)
def list_categorie(db, id=None, nom=None):
""" List categorie """
filter = {}
if id:
filter = ['id', '=', id]
elif nom:
filter['nom', '=', "'%s'" % nom]
else:
filter = App.get_filter(request.query.filter, True)
sort = App.get_sort(request.query.sort)
categories = db.query(Categorie.id, Categorie.nom, func.count(Categorie.nom).label("count")).\
outerjoin(Montant).\
group_by(Categorie.nom, Categorie.id)
if filter:
for tmp_filter in filter:
categories = categories.having("%s %s %s" % (tmp_filter[0],
tmp_filter[1],
tmp_filter[2]
)
)
if sort:
for column in sort:
categories = categories.order_by(getattr(Categorie, column))
else:
categories = categories.order_by(desc('count'))
categories = categories.order_by(Categorie.nom)
try:
categories = categories.all()
except NoResultFound:
abort(404, "ID not found")
if not categories:
abort(404, "ID not found")
list_categories = []
attributs = App.get_attribut(request.query.attribut)
if attributs:
for categorie in categories:
dict_attributs = {}
for attribut in attributs:
dict_attributs[attribut] = getattr(categorie, attribut)
list_categories.append(dict_attributs)
else:
for categorie in categories:
list_categories.append({'id': categorie.id,
'nom': categorie.nom,
'count': categorie.count,
}
)
return dumps(list_categories)
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)
