def query(cls):
a_ = sql.t.CmsUserRoleAssignment.alias('a')
u = sql.t.CmsUsers.alias('u')
r_ = sql.t.CmsRoles.alias('r')
return sqlalchemy.select(
cls._exclude(a_) + cls._exclude(r_, 'role_id') +
[u.c.login.label('login'),
u.c.fullname.label('fullname')],
from_obj=[
a_.join(u, sql.gR('a.uid = u.uid')).join(
r_, sql.gR('a.role_id = r.role_id'))
])
def query(cls):
profile = sql.t.EPytisFormProfileBase.alias('profile')
params = sql.t.EPytisFormProfileParams.alias('params')
return sqlalchemy.select(
cls._exclude(profile, 'id', 'username', 'spec_name', 'profile_id',
'pickle', 'dump', 'errors') +
cls._exclude(params, 'id', 'pickle', 'dump', 'errors') + [
sql.gL("profile.id||'.'||params.id").label('id'),
sql.
gL("'form/'|| params.form_name ||'/'|| profile.spec_name ||'//'"
).label('fullname'),
sql.gL(
"case when profile.errors is not null and params.errors is not null "
"then profile.errors ||'\n'||params.errors "
"else coalesce(profile.errors, params.errors) end").label(
'errors'),
sql.gL(
"case when profile.dump is not null and params.dump is not null "
"then profile.dump ||'\n'||params.dump "
"else coalesce(profile.dump, params.dump) end").label(
'dump'),
profile.c.pickle.label('pickled_filter'),
params.c.pickle.label('pickled_params')
],
from_obj=[
profile.join(
params,
sql.gR('profile.username = params.username and '
'profile.spec_name = params.spec_name and '
'profile.profile_id = params.profile_id'))
])
def select_1():
h = sql.t.EvPytisHelp.alias("h")
u = sqlalchemy.select(["*"], from_obj=["pytis_view_user_menu()"]).alias("u")
return sqlalchemy.select(
sql.reorder_columns(
cls._exclude(h),
[
"help_id",
"menuid",
"fullname",
"title",
"description",
"menu_help",
"spec_name",
"spec_description",
"spec_help",
"page_id",
"parent",
"ord",
"content",
"position",
"position_nsub",
"changed",
"removed",
],
),
from_obj=[h.join(u, sql.gR("h.menuid = u.menuid"))],
)
def query(cls):
l = sql.t.CmsSessionLogData.alias('l')
s = sql.t.CmsSession.alias('s')
u = sql.t.CmsUsers.alias('u')
return sqlalchemy.select(cls._exclude(l, 'end_time') + [
u.c.fullname.label('fullname'),
sql.gL("coalesce(l.end_time, s.last_access) - l.start_time").label(
'duration'),
sql.gL("s.session_id IS NOT NULL AND age(s.last_access)<'1 hour'").
label('active')
],
from_obj=[
l.outerjoin(
s,
sql.gR('l.session_id = s.session_id')
).join(u, sql.gR('l.uid = u.uid'))
])
def query(cls):
x = sql.t.CmsRightsAssignment.alias('x')
s = sql.t.CmsMenuStructure.alias('s')
r_ = sql.t.CmsRoles.alias('r')
a_ = sql.t.CmsActions.alias('a')
return sqlalchemy.select(
cls._exclude(x) + [
r_.c.name.label('role_name'),
s.c.mod_id.label('mod_id'),
r_.c.description.label('role_description'),
r_.c.system_role.label('system_role'),
a_.c.name.label('action_name'),
a_.c.description.label('action_description')
],
from_obj=[
x.join(s, sql.gR('s.menu_item_id=x.menu_item_id')).join(
r_, sql.gR('r.role_id = x.role_id')).join(
a_, sql.gR('a.action_id = x.action_id'))
])
def select_1():
h = sql.t.EvPytisHelp.alias('h')
u = sqlalchemy.select(["*"], from_obj=["pytis_view_user_menu()"]).alias('u')
return sqlalchemy.select(
sql.reorder_columns(cls._exclude(h),
['help_id', 'menuid', 'fullname', 'title', 'description',
'menu_help', 'spec_name', 'spec_description', 'spec_help',
'page_id', 'parent', 'ord', 'content', 'position',
'position_nsub', 'changed', 'removed']),
from_obj=[h.join(u, sql.gR('h.menuid = u.menuid'))]
)
def query(cls):
s = sql.t.CmsMenuStructure.alias('s')
l = sql.t.CmsLanguages.alias('l')
t_ = sql.t.CmsMenuTexts.alias('t')
m = sql.t.CmsModules.alias('m')
return sqlalchemy.select(
cls._exclude(s) + cls._exclude(l, 'lang_id') +
cls._exclude(t_, 'menu_item_id', 'lang', 'published') +
cls._exclude(m, 'mod_id') + [
sql.gL("s.menu_item_id ||'.'|| l.lang").label('menu_id'),
sql.gL("coalesce(t.published, 'FALSE')").label('published'),
sql.gL("coalesce(t.title, s.identifier)").label(
'title_or_identifier'),
sql.gL("(select count(*)-1 from cms_menu_structure "
"where tree_order <@ s.tree_order)").label(
'tree_order_nsub')
],
from_obj=[
s.join(l, sqlalchemy.sql.true()).outerjoin(
t_,
sql.gR(
't.menu_item_id = s.menu_item_id AND t.lang = l.lang')
).outerjoin(m, sql.gR('m.mod_id = s.mod_id'))
])
def query(cls):
profile = sql.t.EPytisFormProfileBase.alias('profile')
params = sql.t.EPytisFormProfileParams.alias('params')
return sqlalchemy.select(
cls._exclude(profile, 'id', 'username', 'spec_name', 'profile_id', 'pickle', 'dump',
'errors') +
cls._exclude(params, 'id', 'pickle', 'dump', 'errors') +
[sql.gL("profile.id||'.'||params.id").label('id'),
sql.gL("'form/'|| params.form_name ||'/'|| profile.spec_name ||'//'")
.label('fullname'),
sql.gL("case when profile.errors is not null and params.errors is not null "
"then profile.errors ||'\n'||params.errors "
"else coalesce(profile.errors, params.errors) end").label('errors'),
sql.gL("case when profile.dump is not null and params.dump is not null "
"then profile.dump ||'\n'||params.dump "
"else coalesce(profile.dump, params.dump) end").label('dump'),
profile.c.pickle.label('pickled_filter'),
params.c.pickle.label('pickled_params')],
from_obj=[profile.join(params, sql.gR('profile.username = params.username and '
'profile.spec_name = params.spec_name and '
'profile.profile_id = params.profile_id'))]
)
