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 update_compte(db, id=None):
""" Update information for a compte """
entity = App.check_data(Compte, request.body.readline())
if entity:
try:
compte = db.query(Compte).filter(Compte.id == id).one()
except NoResultFound:
abort(404, "ID not found")
for column, value in entity.iteritems():
if column == "archive":
setattr(compte, "archive", App.convert_value(value))
else:
setattr(compte, column, value)
try:
db.commit()
compte = loads(list_compte(db, compte.id))
return compte[0]
except IntegrityError as ex:
abort(404, ex.args)
def insert_banque(db):
""" Create a banque """
entity = App.check_data(Banque, request.body.readline())
if entity:
banque = Banque()
for column, value in entity.iteritems():
setattr(banque, column, value)
db.add(banque)
try:
db.commit()
except IntegrityError as ex:
abort(404, ex.args)
response.status = 201
response.headers["Location"] = "/banque/%s" % (banque.id,)
banque = loads(list_banque(db, banque.id))
return banque[0]
def update_banque(db, id=None):
""" Update information for a banque """
entity = App.check_data(Banque, request.body.readline())
if entity:
try:
banque = db.query(Banque).filter(Banque.id == id).one()
except NoResultFound:
abort(404, "ID not found")
for column, value in entity.iteritems():
setattr(banque, column, value)
try:
db.commit()
banque = loads(list_banque(db, banque.id))
return banque[0]
except IntegrityError as ex:
abort(404, ex.args)
def insert_compte(db):
""" Create a compte """
entity = App.check_data(Compte, request.body.readline())
if entity:
compte = Compte()
for column, value in entity.iteritems():
setattr(compte, column, value)
db.add(compte)
try:
db.commit()
except IntegrityError as ex:
abort(404, ex.args)
response.status = 201
response.headers["Location"] = "/compte/%s" % (compte.id,)
compte = loads(list_compte(db, compte.id))
return compte[0]
def update_categorie(db, id):
""" Update information for an categorie """
entity = App.check_data(Categorie, request.body.readline())
if entity:
try:
categorie = db.query(Categorie).\
filter(Categorie.id == id).\
one()
except NoResultFound:
abort(404, "ID not found")
for column, value in entity.iteritems():
setattr(categorie, column, value)
try:
db.commit()
categorie = loads(list_categorie(db, categorie.id))
return categorie[0]
except IntegrityError as ex:
abort(404, ex.args)
def insert_ecriture(db):
""" Create an ecriture """
entity = App.check_data(Ecriture, request.body.readline())
if entity:
ecriture = Ecriture()
for column, value in entity.iteritems():
if column == 'date':
ecriture.date = datetime.strptime(value, "%Y/%m/%d")
#else:
# setattr(ecriture, column, value)
elif column == 'nom':
ecriture.nom = value
elif column == 'type':
ecriture.type = value
elif column == 'valide':
ecriture.valide = value
elif column == 'compte_id':
ecriture.compte_id = value
elif column == 'nom_id':
ecriture.nom_id = value
try:
db.add(ecriture)
db.commit()
except IntegrityError as ex:
print ex
abort(404, ex.args)
montant = Montant()
for column, value in entity.iteritems():
if column == 'montant':
montant.montant = int(round(locale.atof(value)*100,2))
elif column == 'description':
montant.description = value
elif column == 'categorie_id':
montant.categorie_id = value
try:
ecriture.montant.append(montant)
db.commit()
except IntegrityError as ex:
print ex
abort(404, ex.args)
response.status = 201
response.headers["Location"] = "/ecriture/%s/" % (ecriture.id,)
ecriture = loads(list_ecriture(db, ecriture.id))
return ecriture[0]
def update_ecriture(db, id=None, montant_id=None):
""" Update information for an ecriture """
entity = App.check_data(Ecriture, request.body.readline())
if entity:
try:
ecriture = db.query(Ecriture).\
filter(Ecriture.id == id).\
one()
# if montant_id:
# ecriture = ecriture.filter(Montant.id == montant_id).\
#ecriture = ecriture.one()
except NoResultFound:
abort(404, "ID not found")
for column, value in entity.iteritems():
if column == 'date':
ecriture.date = datetime.strptime(value, "%Y/%m/%d")
elif column == 'nom':
ecriture.nom = value
elif column == 'type':
ecriture.type = value
elif column == 'valide':
ecriture.valide = value
elif column == 'nom_id':
ecriture.nom_id = value
try:
montant = db.query(Montant).\
filter(Montant.id == entity['montant_id']).\
one()
except NoResultFound:
abort(404, "ID not found")
for column, value in entity.iteritems():
if column == 'montant':
montant.montant = int(round(locale.atof(value)*100,2))
if column == 'description':
montant.description = value
elif column == 'categorie_id':
montant.categorie_id = value
try:
db.commit()
ecriture = loads(list_ecriture(db, id))
return ecriture[0]
except Exception as ex:
abort(404, ex)
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)
