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)