<collection name="Inventory_(vw_cmj_all_case)" idField="id" replace="runtime">
<prototype>
<![CDATA[
SELECT id,
numberWOTags,
created_date,
Module,
self_1,
self_2,
self_3,
invPrefix_tag,
subject_tag,
invNumber_tag,
numberWOTags,
subject,
moduleName,
fc_subject,
fileCardNumber || invNumber || ' ' || subject as description,
invNumber,
fc_depId,
fileCardNumber as invPrefix
FROM (
SELECT
CASE WHEN b.cmjunid IS NULL THEN substring(u.migrationid, 0, 33) ELSE substring(b.cmjunid, 0, 33) END AS fc_depId,
fc.id AS id,
fc.module as Module,
complect,
ss_module.title as moduleName,
fc.created_date,
fc.title as subject,
fc.Code as numberWOTags,
fc.Number as invNumber,
'<id>' AS self_1,
':' AS self_2,
'</>' AS self_3,
'<invPrefix>' AS invPrefix_tag,
'<subject>' AS subject_tag,
'<invNumber>' AS invNumber_tag,
CASE
WHEN (parent.IsCodeNumIndex = 1)
THEN parent.Code || '-' || fc.Number || ' ' || fc.title
ELSE fc.Code || '-' || fc.Number || ' ' || fc.title
END AS fc_subject,
CASE
WHEN (parent.IsCodeNumIndex = 1)
THEN parent.Code || '-' || fc.Number
ELSE fc.Code || '-'
END AS fileCardNumber
FROM
FD_FileCard fc
LEFT JOIN SO_Parent parent ON parent.id = fc.soobject
JOIN ss_module ON ss_module.id=fc.module
JOIN so_parent_su ON fc.soobject = so_parent_su.id
JOIN so_structureunit su on su.id = so_parent_su.owner
JOIN so_unit u on u.id = su.id
JOIN so_beard b on b.id = su.beard
UNION
SELECT
substring(b.cmjunid, 0, 33) as fc_depId,
fc.id AS id,
fc.module as Module,
complect,
ss_module.title as moduleName,
fc.created_date,
fc.title as subject,
fc.Code as numberWOTags,
fc.Number as invNumber,
'<id>' AS self_1,
':' AS self_2,
'</>' AS self_3,
'<invPrefix>' AS invPrefix_tag,
'<subject>' AS subject_tag,
'<invNumber>' AS invNumber_tag,
CASE
WHEN (parent.IsCodeNumIndex = 1)
THEN parent.Code || '-' || fc.Number || ' ' || fc.title
ELSE fc.Code || '-' || fc.Number || ' ' || fc.title
END AS fc_subject,
CASE
WHEN (parent.IsCodeNumIndex = 1)
THEN parent.Code || '-' || fc.Number
ELSE fc.Code || '-'
END AS fileCardNumber
FROM
FD_FileCard fc
LEFT JOIN SO_Parent parent ON parent.id = fc.soobject
JOIN ss_module ON ss_module.id=fc.module
JOIN so_parent_ph ON fc.soobject = so_parent_ph.id
JOIN so_posthead ph on ph.id = so_parent_ph.owner
JOIN so_appointmenthead apph on apph.post = ph.id AND apph.accessredirect is NULL
JOIN so_appointment app on app.id = apph.id
JOIN so_beard b on b.id = app.beard
) s
WHERE 1=1
::where-clause
]]>
</prototype>
<counting-prototype>
<![CDATA[
SELECT
COUNT(1)
FROM (
SELECT
CASE WHEN b.cmjunid IS NULL THEN substring(u.migrationid, 0, 33) ELSE substring(b.cmjunid, 0, 33) END AS fc_depId,
fc.id AS id,
fc.module as Module,
complect,
ss_module.title as moduleName,
fc.created_date,
fc.title as subject,
fc.Code as numberWOTags,
fc.Number as invNumber,
'<id>' AS self_1,
':' AS self_2,
'</>' AS self_3,
'<invPrefix>' AS invPrefix_tag,
'<subject>' AS subject_tag,
'<invNumber>' AS invNumber_tag,
CASE
WHEN (parent.IsCodeNumIndex = 1)
THEN parent.Code || '-' || fc.Number || ' ' || fc.title
ELSE fc.Code || '-' || fc.Number || ' ' || fc.title
END AS fc_subject,
CASE
WHEN (parent.IsCodeNumIndex = 1)
THEN parent.Code || '-' || fc.Number
ELSE fc.Code || '-'
END AS fileCardNumber
FROM
FD_FileCard fc
LEFT JOIN SO_Parent parent ON parent.id = fc.soobject
JOIN ss_module ON ss_module.id=fc.module
JOIN so_parent_su ON fc.soobject = so_parent_su.id
JOIN so_structureunit su on su.id = so_parent_su.owner
JOIN so_unit u on u.id = su.id
JOIN so_beard b on b.id = su.beard
UNION
SELECT
substring(b.cmjunid, 0, 33) as fc_depId,
fc.id AS id,
fc.module as Module,
complect,
ss_module.title as moduleName,
fc.created_date,
fc.title as subject,
fc.Code as numberWOTags,
fc.Number as invNumber,
'<id>' AS self_1,
':' AS self_2,
'</>' AS self_3,
'<invPrefix>' AS invPrefix_tag,
'<subject>' AS subject_tag,
'<invNumber>' AS invNumber_tag,
CASE
WHEN (parent.IsCodeNumIndex = 1)
THEN parent.Code || '-' || fc.Number || ' ' || fc.title
ELSE fc.Code || '-' || fc.Number || ' ' || fc.title
END AS fc_subject,
CASE
WHEN (parent.IsCodeNumIndex = 1)
THEN parent.Code || '-' || fc.Number
ELSE fc.Code || '-'
END AS fileCardNumber
FROM
FD_FileCard fc
LEFT JOIN SO_Parent parent ON parent.id = fc.soobject
JOIN ss_module ON ss_module.id=fc.module
JOIN so_parent_ph ON fc.soobject = so_parent_ph.id
JOIN so_posthead ph on ph.id = so_parent_ph.owner
JOIN so_appointmenthead apph on apph.post = ph.id AND apph.accessredirect is NULL
JOIN so_appointment app on app.id = apph.id
JOIN so_beard b on b.id = app.beard
) s
WHERE 1=1
::where-clause
]]>
</counting-prototype>
<filter name="MODULE">
<criteria placeholder="where-clause">
<![CDATA[
Module = {0}
]]>
</criteria>
</filter>
<filter name="self">
<criteria placeholder="where-clause">
<![CDATA[
Module = {0} and id = {1}
]]>
</criteria>
</filter>
<filter name="description">
<criteria placeholder="where-clause">
<![CDATA[
description = {0}
]]>
</criteria>
</filter>
<filter name="description_partial">
<criteria placeholder="where-clause">
<![CDATA[
description iLIKE ('%'||{0}||'%')
]]>
</criteria>
</filter>
<filter name="subject">
<criteria placeholder="where-clause">
<![CDATA[
subject = {0}
]]>
</criteria>
</filter>
<filter name="subject_partial">
<criteria placeholder="where-clause">
<![CDATA[
subject iLIKE ('%'||{0}||'%')
]]>
</criteria>
</filter>
<filter name="fc_subject">
<criteria placeholder="where-clause">
<![CDATA[
fc_subject ilike '%'||{0}||'%'
]]>
</criteria>
</filter>
<filter name="fc_subject_partial">
<criteria placeholder="where-clause">
<![CDATA[
fc_subject ilike '%'||{0}||'%'
]]>
</criteria>
</filter>
<filter name="fc_depId">
<criteria placeholder="where-clause">
<![CDATA[
fc_depId ilike '%'||{0}||'%'
]]>
</criteria>
</filter>
<filter name="fc_depId_partial">
<criteria placeholder="where-clause">
<![CDATA[
fc_depId ilike '%'||{0}||'%'
]]>
</criteria>
</filter>
<filter name="moduleName">
<criteria placeholder="where-clause">
<![CDATA[
moduleName LIKE {0}
]]>
</criteria>
</filter>
<filter name="CQSEARCH">
<criteria placeholder="where-clause">
<![CDATA[(lower(fc_subject) like ('%' || lower({0}) || '%'))]]>
</criteria>
</filter>
</collection>