summaryrefslogtreecommitdiff
path: root/model.py
blob: 0d2489a74ba44f97b48b7077f20e9219ae658123 (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
import elixir
from elixir import Field, ManyToOne, OneToMany, OneToOne, ColumnProperty, using_options, using_options_defaults
from sqlalchemy import types as T
from sqlalchemy import sql

from functools import partial
from collections import namedtuple

__all__ = ["Category", "SingleExpense", "ConstExpense", "CatExpense", "MonthExpense", "session"]

#
# DB Setup
#

elixir.metadata.bind = "sqlite:///test.sqlite"
elixir.metadata.bind.echo = True

#
# Global definitions
#

ReqField = partial(Field, required = True)

class Entity (elixir.Entity):
    using_options(abstract = True)

    using_options_defaults(shortnames = True)

#
# Database Entities
#

class Category (Entity):
    
    name = Field(T.String(50), unique = True)

    parent = ManyToOne('Category')
    children = OneToMany('Category')

    def __repr__ (self):
        if self.parent:
            return '<Category "%s" of "%s">' % (self.name, self.parent.name)
        else:
            return '<Category "%s">' % self.name

class Expense (Entity):
    using_options(abstract = True)

    description = Field(T.String(50))
    expense     = ReqField(T.Numeric(scale = 2))
    category    = ManyToOne('Category', required = True, innerjoin = True)

class SingleExpense (Expense):
    date = ReqField(T.Date)
    
    year = ColumnProperty(lambda c: sql.extract('year', c.date))
    month = ColumnProperty(lambda c: sql.extract('month', c.date))

    @classmethod
    def of_month (cls, month, year):
        comp = sql.and_(
                cls.month == month,
                cls.year == year)

        return cls.query.filter(comp)

class ConstExpense (Expense):
    months  = ReqField(T.Integer)
    start   = ReqField(T.Date)
    end     = ReqField(T.Date)

    next = OneToOne('ConstExpense', inverse = 'prev')
    prev = ManyToOne('ConstExpense')
    
    monthly = ColumnProperty(lambda c: sql.cast(c.expense / c.months, T.Numeric(scale = 2)), deferred = True)
    
    start_year = ColumnProperty(lambda c: sql.extract('year', c.start))
    start_month = ColumnProperty(lambda c: sql.extract('month', c.start))
    
    end_year = ColumnProperty(lambda c: sql.extract('year', c.end))
    end_month = ColumnProperty(lambda c: sql.extract('month', c.end))

    @classmethod
    def of_month (cls, month, year):
        c1 = sql.or_(
                    cls.start_year < year,
                    sql.and_(
                        cls.start_year == year,
                        cls.start_month <= month
                    ))

        c2 = sql.or_(
                    cls.end_year > year,
                    sql.and_(
                        cls.end_year == year,
                        cls.end_month >= month
                    ))

        return cls.query.filter(sql.and_(c1,c2))

#
# Work entities (not stored in DB)
#
CatExpense = namedtuple('CatExpense', 'cat expense')

class MonthExpense (namedtuple('MonthExpense', 'date const catexps')):
    __slots__ = ()
    
    @property
    def sum (self):
        return self.const + sum(x.expense for x in self.catexps)

    def __str__ (self):
        return '<MonthExpense of "%s": %s>' % (self.date, self.sum)

#
# Rest
#

elixir.setup_all()

session = elixir.session

if __name__ == "__main__":
    elixir.create_all()