def all_queryed(cls, ucid, qry=False, cats=False, creds=False, total_only=False, fromdate=False, todate=False,): base = DBSession.query(Invoice) if total_only: base = DBSession.query(func.sum(Invoice.amount).label('a_sum'))\ base = base.join(Invoice.category)\ .join(Invoice.creditor)\ .filter(not_(and_(Category.private == True, Category.user_id != ucid))) """ if argument add filter(s).""" if qry: qry = "%"+qry+"%" base = base.filter(Invoice.title.like(qry)) if cats: for c in cats: base = base.filter(Category.id == c.id) if creds: for c in creds: base = base.filter(Creditor.id == c.id) if fromdate: base = base.filter(Invoice.due >= fromdate) if todate: base = base.filter(Invoice.due <= todate) return base
def with_category(cls, id, total_only=False): if total_only: return DBSession.query(func.sum(Expenditure.amount) .label('a_sum'))\ .filter(and_(Expenditure.archived == False, Expenditure.category_id == id))\ .first() return DBSession.query(Expenditure)\ .filter(and_(Expenditure.category_id == id, Expenditure.archived == False)).all()
def with_category_all_unpaid(cls, cid, total_only=False): base = DBSession.query(Invoice) if total_only: base = DBSession.query(func.sum(Invoice.amount).label('a_sum'))\ base = base.filter(and_(Invoice.category_id == cid, Invoice.archived == False, Invoice.paid == None))\ .all() return base
def with_category_paid(cls, cid, year, month, total_only=False): base = DBSession.query(Invoice) if total_only: base = DBSession.query(func.sum(Invoice.amount).label('a_sum'))\ base = base.filter(and_(Invoice.category_id == cid, Invoice.archived == False, Invoice.paid != None))\ .filter(extract('year', Invoice.due) == year)\ .filter(extract('month', Invoice.due) == month)\ .all() return base
def all_active(cls, request, id=False): if not id: id = authenticated_userid(request) return DBSession.query(Creditor)\ .filter(Creditor.archived == False)\ .filter(not_(and_(Creditor.private == True, Creditor.user_id != id)))
def all_private(cls, request, id=False): if not id: id = authenticated_userid(request) return DBSession.query(Category)\ .filter(and_(Category.user_id == id, Category.private == True, Category.archived == False))
def first_private(cls, request): id = authenticated_userid(request) return DBSession.query(Creditor)\ .filter(and_(Creditor.archived == False, Creditor.private == True, Creditor.user_id == id)).first()
def all_archived(cls, ucid): return DBSession.query(Invoice)\ .join(Invoice.category)\ .filter(Invoice.archived == True)\ .filter(not_(and_(Category.private == True, Category.user_id != ucid)))
def all_active(cls): return DBSession.query(User).filter(User.archived == False)
def by_email(cls, email): return DBSession.query(User).filter(User.email == email).first()
def all_archived(cls, request): id = authenticated_userid(request) return DBSession.query(File)\ .filter(File.archived == True)\ .filter(not_(and_(File.private == True, File.user_id != id)))
def all_shared(cls): return DBSession.query(Category)\ .filter(Category.archived == False)\ .filter(Category.private == False)
def first_active(cls): return DBSession.query(Category)\ .filter(and_(Category.archived == False, Category.private == False)).first()
def by_id(cls, id): return DBSession.query(Category).filter(Category.id == id).first()
def by_id(cls, id): return DBSession.query(Tag).filter(Tag.id == id).first()
def popular(cls): return DBSession.query(Tag).get(20)
def all_shared(cls): return DBSession.query(Creditor)\ .filter(Creditor.archived == False)\ .filter(Creditor.private == False)
def by_id(cls, id): return DBSession.query(File).filter(File.id == id).first()
def by_id(cls, id): return DBSession.query(Expenditure)\ .filter(Expenditure.id == id).first()
def by_id(cls, id): return DBSession.query(User).filter(User.id == id).first()
def all_archived(cls): return DBSession.query(Expenditure)\ .filter(Expenditure.archived == True)
def all_users(cls): return DBSession.query(User).all()
def all_active(cls): return DBSession.query(Income).filter(Income.archived == False)
def all_archived(cls): return DBSession.query(User).filter(User.archived == True)
def by_id(cls, id): return DBSession.query(Income).filter(Income.id == id).first()
def all_archived(cls): return DBSession.query(Income).filter(Income.archived == True)
def by_id(cls, id): return DBSession.query(Creditor).filter(Creditor.id == id).first()
def amount_sum(cls): return DBSession.query(func.sum(Income.amount).label('a_sum'))\ .filter(Income.archived == False).first()
def first_active(cls): return DBSession.query(Creditor)\ .filter(and_(Creditor.archived == False, Creditor.private == False)).first()