departmen.py
3.25 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
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
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'))
parent_id = Column(Integer, ForeignKey('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"
eng = cls.db_session.get_bind()
if eng.dialect.name.lower() == "oracle":
sql = """
SELECT
--LPAD(' ', (LEVEL - 1) * 2) || nama AS hierarchy,
SYS_CONNECT_BY_PATH(nama, '/') AS hierarchy,
id,
kode,
nama,
parent_id,
status,
(LEVEL - 1) AS lvl,
SYS_CONNECT_BY_PATH(id, ',') AS path
FROM
departemen
START WITH
parent_id IS NULL
CONNECT BY
PRIOR id = parent_id
ORDER SIBLINGS BY
id"""
else:
sql = """
WITH RECURSIVE dep_tree AS (
SELECT
id,
kode,
nama,
parent_id,
status,
0 AS lvl,
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(' ', lvl) || 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