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)