departmen.py
3.01 KB
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
from sqlalchemy import (Column, Integer, ForeignKey, String, SmallInteger, text)
from sqlalchemy.orm import (relationship, backref, declared_attr)
from ..models import DBSession, Base
from ..models import (NamaModel, TABLE_ARGS)
class _Departemen(NamaModel):
__table_args__ = (TABLE_ARGS,)
id = Column(Integer, primary_key=True)
parent_id = Column(Integer, ForeignKey('public.departemen.id'))
kategori = Column(String(32))
alamat = Column(String(255))
singkat = Column(String(32))
level_id = Column(SmallInteger)
__tablename__ = 'departemen'
@declared_attr
def children(self):
return relationship(
"Departemen", backref=backref('parent', remote_side=[self.id]))
def get_parents(self, start=False):
allparents = []
if start:
allparents.append(self.nama)
p = self.parent
while p:
allparents.append(p.nama)
p = p.parent
return allparents
@property
def parents(self, start=False):
return self.get_parents()
@property
def name_get(self):
allparents = self.get_parents(True)
return '/'.join(reversed(allparents))
@property
def uraian_all(self):
allparents = self.get_parents(True)
return '/'.join(reversed(allparents))
@classmethod
def get_list(cls):
return DBSession.query(cls.id, cls.nama).order_by(cls.nama).all()
@classmethod
def cte_get(cls, search=None, **kwargs):
# tahun = kwargs.get('tahun', self.req.params.get(
# 'tahun', datetime.datetime.now().year-1))
parent_id = kwargs.get('id', None)
str_where = parent_id and " parent_id={} ".format(
parent_id) or " parent_id IS NULL"
sql = """
WITH RECURSIVE dep_tree AS (
SELECT
id,
kode,
nama,
parent_id,
status,
0 AS level,
ARRAY[id] AS path
FROM
public.departemen
WHERE
{str_where}
UNION ALL
SELECT
c.id,
c.kode,
c.nama,
c.parent_id,
c.status,
ct.level + 1,
ct.path || c.id
FROM
public.departemen c
JOIN
dep_tree ct ON c.parent_id = ct.id
)
SELECT
REPEAT(' ', level) || nama AS hierarchy, -- Indent names based on level
id,
kode,
nama,
parent_id,
status,
level,
path
FROM
dep_tree
""".format(str_where=str_where)
if search:
sql = f"{sql} WHERE nama ILIKE '%{search}%' "
sql=sql+"""ORDER BY path;"""
return cls.db_session.execute(text(sql)).fetchall()
class Departemen(_Departemen, Base):
db_session = DBSession