История страницы

YellowАвтор

Свойства страницыinfo

Статус

colour

title
icon	false

В работе

Васильев Антон

Комментарий

панель

На этой странице:

Оглавление

Постановка задачи

Разработать представление для отображения документов, в котором будет присутствовать фильтрация, сортировка по колонкам.

Подсказка

Image Removed

Image RemovedРеализация <prototype>

Создание основы

В основу входит:

Написание SQL-запроса (Кастомная коллекция)
Описание метаданных (Кастомная коллекция)
Описание колонок (Тип элемента коллекции)
Связь колонок с данными SQL-запроса (Дескриптор)
Связь навигационной панели на полученную таблицу (Представление)

Кастомная коллекция

Кастомная коллекция представляет собой набор 2-х файлов:

Блок кода

language	xml
title	XML коллекции. Описывается SQL-запрос, кол-во возвращаемых записей, фильтрация, поиск
linenumbers	true
collapse	true

Создание представления

SQL-запрос

Примечание
Список обязательных колонок, если по записи нужно потом получить объект (документ, заявку, справочник и т.д.): id, created_date, updated_date, Module, self_1, self_2, self_3.

Получение данных:

Системные поля прописаны в таблицах и их можно найти в разделе 4.1. Схема данных AF5/CM6.

Настраиваемые поля хранятся в таблицах tn_field и tn_field_<тип данных> (string, date, datetime, decimal и т.д.)

Блок кода

language	sql
title	Запрос на получение значения из настаиваемого поля status
linenumbers	true
collapse	true

select tfs."value" as v
        from tn_field
        join tn_field_string tfs on tn_field.access_object_id = tfs.id
        where tn_field."owner" = rkkbase.id
        and tn_field.owner_type = rkkbase.id_type
        and tn_field.cmjfield = 'status') as statusWPRequest

Поля из комплексного справочника хранятся в таблицах classifier_compleх, clscom_tag, cls_typeComp

Блок кода

language	sql
title	Запрос на получение значения из поля valueCountry комплексного справочника Офис (схема ComplectClassifierOffice)
linenumbers	true
collapse	true

SELECT title FROM classifier_complex WHERE classifier_complex.id = (
					SELECT
						complex1.id
						FROM
							clscom_tag tag1
					left join clscom_to_tag to_tag1 on to_tag1.tag = tag1.id
					left join classifier_complex complex1 on complex1.id = to_tag1.classifier
					left join cls_typeComp typecom1 on typecom1.id = complex1.owner
							WHERE (
								tag1.schema = 'ComplectClassifierOffice' and typecom1.alias = 'valueCountry' and tag1.id = tag.id
								))
								) as OfficeCountry

Кастомная коллекция

Кастомная коллекция представляет собой набор 2-х файлов:

Блок кода

language	xml
title	XML коллекции. Описывается SQL-запрос, кол-во возвращаемых записей, фильтрация, поиск
linenumbers	true
collapse	true

<collection name="WorkplaceRequests_(vw_cmj_all)" idField="id" replace="runtime">
    <prototype>
        <![CDATA[
                 SELECT
                  id,
                  created_date,
                  updated_date,
                  Module,
                  self_1,
                  self_2,
                  self_3,
                  typeRequest,
				  statusWPRequest,
				  stage,
                  regNumber,
                  rNumber,
                  numberWOTags,
				  wpInitiator,
				  term,
                  finishtime,
                  wpEmployee,
                  wpEmpDepartment,
                  wpEmpHierDepatment,
                  wpEmpGoingToWork,
                  wpMovingDate,
                  wpInitBranch,
                  wpOffice,
                  wpTypeOffice,
                  wpInitOrganization,
                  wpInitiatorDepartment,
                  wpLocality,
                  wpCountry,
                  wpArea,
                  wpFloor,
                  wpRoomNumber,
                  wpCode
                FROM (
                       select
	rkkbase.id,
    rkkbase.created_date,
    rkkbase.updated_date,
    rkkbase.module                                   AS Module,
    '<id>'         <![CDATA[
                 SELECT
                 AS id,
self_1,
    ':'                             created_date,
                 AS updatedself_date2,
    '</>'              Module,
                              AS self_13,
    rkkbase."type"                  self_2,
                 AS self_3typeRequest,
    (select tfs."value" as v
        from tn_field
   typeRequest,
				  statusWPRequest,
				  stage,
 join tn_field_string tfs on tn_field.access_object_id = tfs.id
        where tn_field."owner" = regNumber,rkkbase.id
        and tn_field.owner_type = rkkbase.id_type
       rNumber,
 and tn_field.cmjfield = 'status') as statusWPRequest,
    (select tfs."value" as v
     numberWOTags,
				  wpInitiator,
				 from term,tn_field
        join tn_field_string tfs on tn_field.access_object_id = tfs.id
    finishtime,
    where tn_field."owner" = rkkbase.id
        and tn_field.owner_type  wpEmployee,= rkkbase.id_type
        and tn_field.cmjfield = 'stage') as stage,
   '<regNumberPrefix>'  wpEmpDepartment,
                  wpEmpHierDepatment,
                  wpEmpGoingToWork,
                  wpMovingDate,
                  wpInitBranch,
                  wpOffice,
        || coalesce(rkk.prjnumprist, '') || '</>' || '<regNumberCounter#Number>' || coalesce(cast(rkk.prjnumcounter as varchar), '') || '</>' || '<regNumberSuffix>' || coalesce(rkk.prjnumfin , '') || '</>' as regNumber,
   rkk.prjnumcounter as rNumber,
   coalesce(rkk.prjnumprist, '') || coalesce(cast(rkk.prjnumcounter as VARCHAR), '') || coalesce(rkk.prjnumfin, '') as numberWOTags,
	(select
		coalesce('<id>' || substring(substring(substring(substring(tfs."value", '%(.*)'), '%(.*)'), '%(.*)'),'%(.*)')
    	|| ':' || substring(substring(substring(substring(tfs."value", '%(.*)'), '%(.*)'), '%(.*)'),'(.*)%') || '</><shortName>'
    	|| substr(tfs."value", 1, strpos(tfs."value", '%') - 1) || '</>', '<id></><shortName></>') as v
          wpTypeOffice,from tn_field
        join tn_field_string tfs on tn_field.access_object_id = tfs.id
    wpInitOrganization,
    where tn_field."owner" = rkkbase.id
        and tn_field.owner_type  wpInitiatorDepartment,= rkkbase.id_type
        and tn_field.cmjfield = 'wpInitiator')       wpLocalityas wpInitiator,
        	(select tfs."value" as v
          wpCountry,from tn_field
        join tn_field_datetime tfs on tn_field.access_object_id = tfs.id
    wpArea,
    where tn_field."owner" = rkkbase.id
        and tn_field.owner_type  wpFloor,= rkkbase.id_type
        and tn_field.cmjfield = 'plannedfinishtime')       wpRoomNumberas term,
	(select tfs."value" as v
        from tn_field
      wpCode
  join tn_field_datetime tfs         on tn_field.access_object_id = tfs.id
   FROM (
    where tn_field."owner" = rkkbase.id
        and tn_field.owner_type = rkkbase.id_type
     select
	rkkbase.id,
   and rkkbase.created_date,
    rkkbase.updated_date,
    rkkbase.moduletn_field.cmjfield = 'finishtime') as finishtime,
	(select tfs."value" as v
        from tn_field
        join tn_field_beard tfs on tn_field.access_object_id = tfs.id
        where tn_field."owner" = rkkbase.id
  AS Module,
    '<id>' and tn_field.owner_type = rkkbase.id_type
        and tn_field.cmjfield = 'assignee') as assignee,
	(select
		coalesce('<id>' || substring(substring(substring(substring(tfs."value", '%(.*)'), '%(.*)'), '%(.*)'),'%(.*)')
    	|| ':' || substring(substring(substring(substring(tfs."value", '%(.*)'), '%(.*)'), '%(.*)'),'(.*)%') || '</><shortName>'
         AS self_1,
    ':'       	|| substr(tfs."value", 1, strpos(tfs."value", '%') - 1) || '</>', '<id></><shortName></>') as v
        from tn_field
        join tn_field_string tfs on tn_field.access_object_id       = tfs.id
        where tn_field."owner" = AS self_2,
rkkbase.id
     '</>'    and tn_field.owner_type = rkkbase.id_type
        and tn_field.cmjfield = 'wpEmployee') as wpEmployee,
    (select date(tfs."value") as v
        from tn_field
        ASjoin self_3,
    rkkbase."type"tn_field_dateonly tfs on tn_field.access_object_id = tfs.id
        where tn_field."owner" = rkkbase.id
            and tn_field.owner_type = rkkbase.id_type
        and tn_field.cmjfield = 'wpEmpGoingToWork') ASas typeRequestwpEmpGoingToWork,
    (select date(tfs."value") as v
        from tn_field
        join tn_field_stringdateonly tfs on tn_field.access_object_id = tfs.id
        where tn_field."owner" = rkkbase.id
        and tn_field.owner_type = rkkbase.id_type.id_type
        and tn_field.cmjfield = 'wpMovingDate') as wpMovingDate,
	(select
		coalesce('<id>' || substring(substring(substring(substring(tfs."value", '%(.*)'), '%(.*)'), '%(.*)'),'%(.*)')
    	|| ':' ||  and tn_field.cmjfield = 'status') as statusWPRequest,substring(substring(substring(substring(tfs."value", '%(.*)'), '%(.*)'), '%(.*)'),'(.*)%') || '</><shortName>'
    (select 	|| substr(tfs."value", 1, strpos(tfs."value"", '%') - 1) || '</>', '<id></><shortName></>') as v
        from tn_field
        join tn_field_string tfs on tn_field.access_object_id = tfs.id
        where tn_field."owner" = rkkbase.id
        and tn_field.owner_type = rkkbase.id_type
        and tn_field.cmjfield = 'stage') as stage,
   '<regNumberPrefix>' || coalesce(rkk.prjnumprist, '') || '</>' || '<regNumberCounter#Number>' || coalesce(cast(rkk.prjnumcounter as varchar), '') || '</>' || '<regNumberSuffix>' || coalesce(rkk.prjnumfin , '') || '</>' as regNumber,
   rkk.prjnumcounter as rNumber,
   coalesce(rkk.prjnumprist, '') || coalesce(cast(rkk.prjnumcounter as VARCHAR), '') || coalesce(rkk.prjnumfin, '') as numberWOTags,
	(select
		coalesce('<id>' || substring(substring(substring(substring(tfs."value", '%(.*)'), '%(.*)'), '%(.*)'),'%(.*)')
    	|| ':' || substring(substring(substring(substring(tfs."value", '%(.*)'), '%(.*)'), '%(.*)'),'(.*)%') || '</><shortName>'
    	|| substr(tfs."value", 1, strpos(tfs."value", '%') - 1) || '</>', '<id></><shortName></>') as v where tn_field."owner" = rkkbase.id
        and tn_field.owner_type = rkkbase.id_type
        and tn_field.cmjfield = 'wpEmpDepartment') as wpEmpDepartment,
    (select
		tfs."value"  as v
        from tn_field
        join tn_field_string tfs on tn_field.access_object_id = tfs.id
        where tn_field."owner" = rkkbase.id
        and tn_field.owner_type = rkkbase.id_type
        fromand tn_field
.cmjfield = 'wpEmpHierDepatment') as wpEmpHierDepatment,
	(select
		coalesce('<id>'    join tn_field_string tfs on tn_field.access_object_id = tfs.id|| substring(substring(substring(substring(tfs."value", '%(.*)'), '%(.*)'), '%(.*)'),'%(.*)')
    	|| ':'   where tn_field."owner" = rkkbase.id
        and tn_field.owner_type = rkkbase.id_type|| substring(substring(substring(substring(tfs."value", '%(.*)'), '%(.*)'), '%(.*)'),'(.*)%') || '</><shortName>'
    	|| substr(tfs."value",   and tn_field.cmjfield = 'wpInitiator') as wpInitiator,
	(select tfs."value"1, strpos(tfs."value", '%') - 1) || '</>', '<id></><shortName></>') as v
        from tn_field
        join tn_field_datetimestring tfs on tn_field.access_object_id = tfs.id
        where tn_field."owner" = rkkbase.id
        and tn_field.owner_type = rkkbase.id_type
        and tn_field.cmjfield = 'plannedfinishtimewpInitBranch') as termwpInitBranch,
	(select tfs."value" as v
        from tn_field
        join tn_field_datetimestring tfs on tn_field.access_object_id = tfs.id
        where tn_field."owner" = rkkbase.id
        and tn_field.owner_type = rkkbase.id_type
        and tn_field.cmjfield = 'finishtimewpOffice') as finishtimewpOffice,
	(select tfs."value" as v
        from tn_field
        join tn_field_beardstring tfs on tn_field.access_object_id = tfs.id
        where tn_field."owner" = rkkbase.id
        and tn_field.owner_type = rkkbase.id_type
        and tn_field.cmjfield = 'assigneewpTypeOffice') as assigneewpTypeOffice,
	(select
			    coalesce('<id>' || substring(substring(substring(substring(tfs."value", '%(.*)'), '%(.*)'), '%(.*)'),'%(.*)')
    	|| ':' || substring(substring(substring(substring(tfs."value", '%(.*)'), '%(.*)'), '%(.*)'),'(.*)%') || '</><shortName>'
    	|| substr(tfs."value", 1, strpos(tfs."value", '%') - 1) || '</>', '<id></><shortName></>') as v
        from tn_field
        join tn_field_string tfs on tn_field.access_object_id = tfs.id
        where tn_field."owner" = rkkbase.id
         and tn_field.owner_type = rkkbase.id_type
        and tn_field.cmjfield = 'wpEmployeewpInitOrganization') as wpEmployeewpInitOrganization,
	(select
		coalesce('<id>'    (select date|| substring(substring(substring(substring(tfs."value") as v
        from tn_field
        join tn_field_dateonly tfs on tn_field.access_object_id = tfs.id
        where tn_field."owner" = rkkbase.id, '%(.*)'), '%(.*)'), '%(.*)'),'%(.*)')
    	|| ':' ||  and tn_field.owner_type = rkkbase.id_type
        and tn_field.cmjfield = 'wpEmpGoingToWork') as wpEmpGoingToWork,
    (select date(tfs."value"substring(substring(substring(substring(tfs."value", '%(.*)'), '%(.*)'), '%(.*)'),'(.*)%') || '</><shortName>'
    	|| substr(tfs."value", 1, strpos(tfs."value", '%') - 1) || '</>', '<id></><shortName></>') as v
        from tn_field
        join tn_field_dateonlystring tfs on tn_field.access_object_id = tfs.id
        where tn_field."owner" = rkkbase.id
        and tn_field.owner_type = rkkbase.id_type
        and tn_field.cmjfield = 'wpMovingDate') as wpMovingDate,
	(select
		coalesce('<id>' || substring(substring(substring(substring(tfs."value", '%(.*)'), '%(.*)'), '%(.*)'),'%(.*)')
_type
     	|| ':' || substring(substring(substring(substring(tfs."value", '%(.*)'), '%(.*)'), '%(.*)'),'(.*)%') || '</><shortName>'
    	|| substr(tfs."value", 1, strpos(tfs."value", '%') - 1) || '</>', '<id></><shortName></>') and tn_field.cmjfield = 'wpInitDepartment') as wpInitiatorDepartment,
	(select tfs."value" as v
        from tn_field
        join tn_field_string tfs on tn_field.access_object_id = tfs.id
        where tn_field."owner" = rkkbase.id
        and tn_field.owner_type = rkkbase.id_type
        and tn_field.cmjfield = 'wpEmpDepartmentwpLocality') as wpEmpDepartmentwpLocality,
    	(select
		 tfs."value"  as v
        from tn_field
        join tn_field_string tfs on tn_field.access_object_id = tfs.id
        where tn_field."owner" = rkkbase.id
        and tn_field.owner_type = rkkbase.id_type
        and tn_field.cmjfield = 'wpEmpHierDepatmentwpCountry') as wpEmpHierDepatmentwpCountry,
	(select
		    coalesce('<id>' || substring(substring(substring(substring(tfs."value", '%(.*)'), '%(.*)'), '%(.*)'),'%(.*)')
    	|| ':' || substring(substring(substring(substring(tfs."value", '%(.*)'), '%(.*)'), '%(.*)'),'(.*)%') || '</><shortName>'
    	|| substr(tfs."value", 1, strpos(tfs."value", '%') - 1) || '</>', '<id></><shortName></>') as v
        from tn_field
        join tn_field_string tfs on tn_field.access_object_id = tfs.id
        where tn_field."owner" = rkkbase.id
        and tn_field.owner_type = rkkbase.id_type
        and tn_field.cmjfield = 'wpInitBranchwpArea') as wpInitBranchwpArea,
	(select tfs."value" as v
        from tn_field
        join tn_field_string tfs on tn_field.access_object_id = tfs.id
        where tn_field."owner" = rkkbase.id
        and tn_field.owner_type = rkkbase.id_type
        and tn_field.cmjfield = 'wpOfficewpFloor') as wpOfficewpFloor,
	(select tfs."value" as v
        from tn_field
        join tn_field_string tfs on tn_field.access_object_id = tfs.id
        where tn_field."owner" = rkkbase.id
        and tn_field.owner_type = rkkbase.id_type
        and tn_field.cmjfield = 'wpTypeOffice') as wpTypeOffice,
	(select
	    coalesce('<id>' || substring(substring(substring(substring(tfs."value", '%(.*)'), '%(.*)'), '%(.*)'),'%(.*)')
    	|| ':' || substring(substring(substring(substring(tfs."value", '%(.*)'), '%(.*)'), '%(.*)'),'(.*)%') || '</><shortName>'
    	|| substr(tfs."value", 1, strpos(tfs."value", '%') - 1) || '</>', '<id></><shortName></>') as v
    and tn_field.cmjfield = 'wpRoomNumber') as wpRoomNumber,
	(select tfs."value" as v
        from tn_field
        join tn_field_string tfs on tn_field.access_object_id = tfs.id
        where tn_field."owner" = rkkbase.id
        and tn_field.owner_type = rkkbase.id_type
        and tn_field.cmjfield = 'wpRMCode') as wpCode
	FROM F_DP_Intrkk internalrkk
         from tn_field
        joinJOIN tnf_fielddp_stringrkk tfsrkk onON tn_field.access_object_rkk.id = tfsinternalrkk.id
          where tn_field."owner" = rkkbase.id
     JOIN f_dp_rkkbase rkkbase andON tn_field.owner_typerkkbase.id = rkkbaserkk.id_type
          and tn_field.cmjfield = 'wpInitOrganization') as wpInitOrganization,
	(select
		coalesce('<id>' || substring(substring(substring(substring(tfs."value", '%(.*)'), '%(.*)'), '%(.*)'),'%(.*)')
    	|| ':' || substring(substring(substring(substring(tfs."value", '%(.*)'), '%(.*)'), '%(.*)'),'(.*)%') || '</><shortName>'
    	|| substr(tfs."value", 1, strpos(tfs."value", '%') - 1) || '</>', '<id></><shortName></>') as v
 JOIN F_DP_Intrkk_Executor executor ON executor.owner = internalrkk.id
                  JOIN so_beard beard ON beard.id = executor.executor
         from tn_fieldWHERE rkkbase.isdeleted <> 1
        join tn_field_string tfs onAND tn_field.access_object_id = tfs.idrkk.regnumcnt IS NULL
		   ) s
        where tn_field."owner" = rkkbase.id
     WHERE stage NOT and tn_field.owner_type = rkkbase.id_type
IN ('Перемещена в архив')
           and tn_field.cmjfield = 'wpInitDepartment') as wpInitiatorDepartment,
	(select tfs."value" as v
 AND 1 = 1
          from tn_field
        join tn_field_string tfs on tn_field.access_object_id = tfs.id
 ::where-clause
            ]]>
 where tn_field."owner" = rkkbase.id
 </prototype>
    <counting-prototype>
     and tn_field.owner_type = rkkbase.id_type <![CDATA[
        and  tn_field.cmjfield = 'wpLocality') as wpLocality,
	(select tfs."value" as v
 SELECT
				COUNT(1)
			FROM (
				SELECT
					rkkbase.id
				FROM F_DP_Intrkk internalrkk
         from tn_field
        joinJOIN tnf_fielddp_stringrkk tfsrkk onON tn_field.access_object_rkk.id = tfsinternalrkk.id
        where tn_field."owner" = rkkbase.id
          JOIN f_dp_rkkbase rkkbase andON tn_field.owner_typerkkbase.id = rkkbaserkk.id_type

               and tn_field.cmjfield = 'wpCountry') as wpCountry,
	(select
	    coalesce('<id>' || substring(substring(substring(substring(tfs."value", '%(.*)'), '%(.*)'), '%(.*)'),'%(.*)')
    	|| ':' || substring(substring(substring(substring(tfs."value", '%(.*)'), '%(.*)'), '%(.*)'),'(.*)%') || '</><shortName>'
    	|| substr(tfs."value", 1, strpos(tfs."value", '%') - 1) || '</>', '<id></><shortName></>') as v
        from tn_field
JOIN F_DP_Intrkk_Executor executor ON executor.owner = internalrkk.id
                  JOIN so_beard beard ON beard.id = executor.executor
				WHERE rkkbase.isdeleted <> 1 AND rkk.regnumcnt IS NULL
			) s
			WHERE stage NOT IN ('Перемещена в архив')
             join tn_field_string tfs on tn_field.access_object_idAND 1 = tfs.id
1
            ::where tn_field."owner" = rkkbase.id
-clause
            ]]>
 and tn_field.owner_type = rkkbase.id_type </counting-prototype>
    <filter name="MODULE">
   and tn_field.cmjfield = 'wpArea') as wpArea,
	(select tfs."value" as v
  <criteria placeholder="where-clause">
       from tn_field
    <![CDATA[
    join tn_field_string tfs on tn_field.access_object_id = tfs.id
         where tn_field."owner" Module = rkkbase.id{0}
        and tn_field.owner_type = rkkbase.id_type
     ]]>
   and tn_field.cmjfield = 'wpFloor') as wpFloor,
	(select tfs."value" as v</criteria>
    </filter>
    <filter name="self">
   from tn_field
    <criteria placeholder="where-clause"><![CDATA[ Module = join tn_field_string tfs on tn_field.access_object_{0} and id = tfs.id{1} ]]></criteria>
    </filter>
    where tn_field."owner" = rkkbase.id<filter name="created_date">
        and tn_field.owner_type = rkkbase.id_type<criteria placeholder="where-clause">
        and tn_field.cmjfield = 'wpRoomNumber') as wpRoomNumber,
	(select tfs."value" as v
 <![CDATA[
            from tn_field
      cast(created_date as join tn_field_string tfs on tn_field.access_object_id = tfs.id
date) = cast({0} as date)
          where tn_field."owner" = rkkbase.id
   ]]>
     and tn_field.owner_type = rkkbase.id_type </criteria>
    </filter>
    and tn_field.cmjfield = 'wpRMCode') as wpCode
	FROM F_DP_Intrkk internalrkk
                  JOIN f_dp_rkk rkk ON rkk.id = internalrkk.id
                  JOIN f_dp_rkkbase rkkbase ON rkkbase.id = rkk.id
            <filter name="rNumber">
        <criteria placeholder="where-clause"><![CDATA[ rNumber = {0} ]]></criteria>
    </filter>
    <filter name="rNumber_partial">
        <criteria placeholder="where-clause"><![CDATA[ rNumber iLIKE ('%'||{0}||'%') ]]></criteria>
    </filter>
    <filter name="rNumber_L">
        <criteria placeholder="where-clause"><![CDATA[ rNumber < {0} ]]></criteria>
    </filter>
    <filter name="rNumber_GE">
      JOIN F_DP_Intrkk_Executor executor ON executor.owner = internalrkk.id <criteria placeholder="where-clause"><![CDATA[ rNumber >= {0} ]]></criteria>
    </filter>
    <filter name="rNumber_Partial">
         JOIN so_beard beard ON beard.id = executor.executor<criteria placeholder="where-clause"><![CDATA[ rNumber LIKE {0} ]]></criteria>
    </filter>
     WHERE rkkbase.isdeleted <> 1<filter name="regNumber">
        <criteria placeholder="where-clause"><![CDATA[ regNumber AND rkk.regnumcnt IS NULL
		= {0} ]]></criteria>
   ) s</filter>
    <filter name="regNumber_partial">
        <criteria placeholder="where-clause"><![CDATA[  WHERE stage NOT INnumberWOTags iLIKE ('Перемещена в архив')'%'||{0}||'%') ]]></criteria>
    </filter>
            AND 1 = 1<filter name="typeRequest">
                    ::<criteria placeholder="where-clause">
            ]]>
    </prototype>
    <counting-prototype>
<![CDATA[
          <![CDATA[
          typeRequest = SELECT
				COUNT(1)
			FROM (
				SELECT
					rkkbase.id
				FROM F_DP_Intrkk internalrkk
{0}
                ]]>
      JOIN f_dp_rkk rkk ON rkk.id = internalrkk.id
    </criteria>
    </filter>
    <filter name="typeRequest_partial">
        <criteria placeholder="where-clause">
      JOIN f_dp_rkkbase rkkbase ON rkkbase.id = rkk.id <![CDATA[
                typeRequest  JOIN F_DP_Intrkk_Executor executor ON executor.owner = internalrkk.id
ilike '%'||{0}||'%'
            ]]>
        </criteria>
    JOIN so_beard beard ON beard.id = executor.executor
				WHERE rkkbase.isdeleted <> 1 AND rkk.regnumcnt IS NULL
			) s
			WHERE stage NOT IN ('Перемещена в архив')
</filter>
    <filter name="wpInitiator">
        <criteria placeholder="where-clause">
            <![CDATA[
                   AND 1wpInitiator = 1{0}
            ::where-clause
    ]]>
        ]]></criteria>
    </counting-prototype>filter>
    <filter name="MODULEwpInitiator_partial">
        <criteria placeholder="where-clause">
            <![CDATA[
                wpInitiator    Module = ilike '%'||{0}||'%'
                ]]>
        </criteria>
    </filter>
    <filter name="selfstage">
        <criteria placeholder="where-clause"><>
            <![CDATA[ Module = {0} and id = {1} ]]><
                    stage = {0}
                ]]>
        </criteria>
    </filter>
    <filter name="createdstage_datepartial">
        <criteria placeholder="where-clause">
            <![CDATA[
                stage   cast(created_date as date) = cast({0} as date)
   ilike '%'||{0}||'%'
             ]]>
        </criteria>
    </filter>
    <filter name="rNumberstatusWPRequest">
        <criteria placeholder="where-clause"><>
            <![CDATA[ rNumber = {0} ]]></criteria>

                </filter>
    <filterwpStatus name="rNumber_partial">
= {0}
         <criteria placeholder="where-clause"><![CDATA[ rNumber iLIKE ('%'||{0}||'%') ]]><       ]]>
        </criteria>
    </filter>
    <filter name="rNumberstatusWPRequest_Lpartial">
        <criteria placeholder="where-clause"><![CDATA[ rNumber < {0} ]]></criteria>
>
            </filter>
![CDATA[
     <filter name="rNumber_GE">
        <criteria placeholder="where-clause"><![CDATA[ rNumber >= {0} ]]>< statusWPRequest ilike '%'||{0}||'%'
            ]]>
        </criteria>
    </filter>
    <filter name="rNumberterm_Partialpartial">
        <criteria placeholder="where-clause"><![CDATA[ rNumber LIKE {0} ]]></criteria>
>
        </filter>
    <filter name="regNumber"><![CDATA[
        <criteria placeholder="where-clause"><![CDATA[ regNumber = {0} ]]></criteria>
    </filter>
  cast(term as <filterdate) name="regNumber_partial">
  = cast({0} as date)
      <criteria placeholder="where-clause"><![CDATA[ numberWOTags iLIKE ('%'||{0}||'%')   ]]><>
        </criteria>
    </filter>
    <filter name="typeRequestfinishtime_partial">
        <criteria placeholder="where-clause">
            <![CDATA[
                 cast(finishtime as date) typeRequest = cast({0}
 as   date)
            ]]>
        </criteria>
    </filter>
    <filter name="typeRequestwpEmpGoingToWork_partial">
        <criteria placeholder="where-clause">
            <![CDATA[
                  cast(wpEmpGoingToWork as typeRequestdate) ilike '%'||= cast({0}||'%' as date)
            ]]>
        </criteria>
    </filter>
    <filter name="wpInitiatorwpMovingDate">
        <criteria placeholder="where-clause">
            <![CDATA[
                    wpInitiator(wpMovingDate >= ({0})::timestamp and
					 wpMovingDate < (({0})::timestamp + interval '1 day'))
                ]]>
        </criteria>
    </filter>
    <filter name="wpInitiator_partialassignee">
        <criteria placeholder="where-clause">
            <![CDATA[
                wpInitiator ilike '%'||    assignee = {0}||'%'

                ]]>
        </criteria>
    </filter>
    <filter name="stageassignee_partial">
        <criteria placeholder="where-clause">
            <![CDATA[
                assignee    stage = ilike '%'||{0}
    ||'%'
            ]]>
        </criteria>
    </filter>
    <filter name="stage_partialwpEmployee">
        <criteria placeholder="where-clause">
            <![CDATA[
                  stage  wpEmployee ilike= '%'||{0}||'%'

                ]]>
        </criteria>
    </filter>
    <filter name="statusWPRequestwpEmployee_partial">
        <criteria placeholder="where-clause">
            <![CDATA[
                wpEmployee    wpStatus = ilike '%'||{0}||'%'
                ]]>
        </criteria>
    </filter>
    <filter name="statusWPRequest_partialwpEmpDepartment">
        <criteria placeholder="where-clause">
            <![CDATA[
                    statusWPRequestwpEmpDepartment ilike= '%'||{0}||'%'
                ]]>
        </criteria>
    </filter>
    <filter name="termwpEmpDepartment_partial">
        <criteria placeholder="where-clause">
            <![CDATA[
   
               cast(term aswpEmpDepartment date) = cast(ilike '%'||{0} as date)||'%'
            ]]>
        </criteria>
    </filter>
    <filter name="finishtime_partialwpInitBranch">
        <criteria placeholder="where-clause">
            <![CDATA[
                  cast(finishtime as date)wpInitBranch = cast({0}
   as date)
            ]]>
        </criteria>
    </filter>
    <filter name="wpEmpGoingToWorkwpInitBranch_partial">
        <criteria placeholder="where-clause">
            <![CDATA[
                wpInitBranch  cast(wpEmpGoingToWork as date) = cast({0} as date)ilike '%'||{0}||'%'
            ]]>
        </criteria>
    </filter>
    <filter name="wpMovingDatewpInitiatorDepartment">
        <criteria placeholder="where-clause">
            <![CDATA[
                    (wpMovingDatewpInitiatorDepartment >= ({0})::timestamp and
					 wpMovingDate < (({0})::timestamp + interval '1 day')) {0}
                ]]>
        </criteria>
    </filter>
    <filter name="assigneewpInitiatorDepartment_partial">
        <criteria placeholder="where-clause">
            <![CDATA[
                wpInitiatorDepartment    assignee = ilike '%'||{0}
    ||'%'
            ]]>
        </criteria>
    </filter>
    <filter name="assignee_partialwpLocality">
        <criteria placeholder="where-clause">
            <![CDATA[
                    assigneewpLocality ilike= '%'||{0}||'%'
                ]]>
        </criteria>
    </filter>
    <filter name="wpEmployeewpLocality_partial">
        <criteria placeholder="where-clause">
            <![CDATA[
                wpLocality    wpEmployee = ilike '%'||{0}
   ||'%'
             ]]>
        </criteria>
    </filter>
    <filter name="wpEmployee_partialwpCountry">
        <criteria placeholder="where-clause">
            <![CDATA[
                    wpEmployeewpCountry ilike= '%'||{0}||'%'
                ]]>
        </criteria>
    </filter>
    <filter name="wpEmpDepartmentwpCountry_partial">
        <criteria placeholder="where-clause">
            <![CDATA[
                    wpEmpDepartment = wpCountry ilike '%'||{0}
    ||'%'
            ]]>
        </criteria>
    </filter>
    <filter name="wpEmpDepartment_partialwpArea">
        <criteria placeholder="where-clause">
            <![CDATA[
                    wpEmpDepartmentwpArea ilike= '%'||{0}||'%'

                ]]>
        </criteria>
    </filter>
    <filter name="wpInitBranchwpArea_partial">
        <criteria placeholder="where-clause">
            <![CDATA[
                wpArea    wpInitBranch = ilike '%'||{0}
    ||'%'
            ]]>
        </criteria>
    </filter>
    <filter name="wpInitBranch_partialwpFloor">
        <criteria placeholder="where-clause">
            <![CDATA[
                    wpInitBranchwpFloor ilike= '%'||{0}||'%'
                ]]>
        </criteria>
    </filter>
    <filter name="wpInitiatorDepartmentwpRoomNumber">
        <criteria placeholder="where-clause">
            <![CDATA[
                    wpInitiatorDepartmentwpRoomNumber = {0}
                ]]>
        </criteria>
    </filter>
    <filter name="wpInitiatorDepartment_partialwpCode">
        <criteria placeholder="where-clause">
            <![CDATA[
                    wpInitiatorDepartmentwpCode ilike= '%'||{0}||'%'
                ]]>
        </criteria>
    </filter>
    <filter name="wpLocalitywpEmpHierDepatment">
        <criteria placeholder="where-clause">
            <![CDATA[
                    wpLocalitywpEmpHierDepatment = {0}
                ]]>
        </criteria>
    </filter>
    <filter name="wpLocalitywpEmpHierDepatment_partial">
        <criteria placeholder="where-clause">
            <![CDATA[
                wpLocalitywpEmpHierDepatment ilike '%'||{0}||'%'
            ]]>
        </criteria>
    </filter>
    <filter name="wpCountryBeforeDate">
        <criteria placeholder="where-clause">
            <![CDATA[
                    wpCountry = updated_date < {0}
                ]]>
        </criteria>
    </filter>
    <filter name="wpCountry_partial">
        <criteria placeholder="where-clause">
            <![CDATA[
                wpCountry ilike '%'||{0}||'%'
            ]]>
        </criteria>
    </filter>
    <filter name="wpArea"></collection>

Блок кода

language	xml
title	XML метаданных коллекции. Описывается сортировка по умолчанию, категоризация, специфичные колонки
linenumbers	true
collapse	true

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans" 
    xmlns:p="http://www.springframework.org/schema/p" 
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.springframework.org/schema/beans
        <criteria placeholder="where-clause">
            <![CDATA[http://www.springframework.org/schema/beans/spring-beans-3.2.xsd" default-lazy-init="true">

	<bean id="WorkplaceRequests_(vw_cmj_all)Metadata"
                    wpArea = {0}
                ]]class="ru.intertrust.cm_sochi.srv.connector.sochi.collections.CollectionMetadataNew" p:caseSensitiveFieldNames="true">
        </criteria>
    </filter>
 <constructor-arg>
   <filter name="wpArea_partial">
        <criteria<list placeholder="where-clausevalue-type="ru.intertrust.cm_sochi.srv.connector.sochi.collections.Field">
            <![CDATA[
    <bean     class="ru.intertrust.cm_sochi.srv.connector.sochi.collections.Field" p:name="self">
       wpArea ilike '%'||{0}||'%'
              ]]<property name="virtualField">
        </criteria>
    </filter>
    <filter name="wpFloor">
       <bean <criteria placeholder="where-clauseclass="ru.intertrust.cm_sochi.srv.connector.sochi.collections.BuildVirtualField">
            <![CDATA[
                 <property   wpFloor = {0}name="realFields">
                ]]>
        </criteria>
    </filter>
    <filter name="wpRoomNumber"> <list>
        <criteria placeholder="where-clause">
            <![CDATA[
                <value>self_1</value>
    wpRoomNumber = {0}
                ]]>
        </criteria>
    <	<value>Module</filter>value>
    <filter name="wpCode">
        <criteria placeholder="where-clause">
            <![CDATA[
          <value>self_2</value>
          wpCode = {0}
                ]]>
        <<value>ID</criteria>value>
    </filter>
    <filter name="wpEmpHierDepatment">
        <criteria placeholder="where-clause">
            <![CDATA[
       <value>created_date</value>
             wpEmpHierDepatment = {0}
                ]]>
     <value>self_3</value>
   </criteria>
    </filter>
    <filter name="wpEmpHierDepatment_partial">
          <criteria placeholder="where-clause">
            <![CDATA[
/list>
                  wpEmpHierDepatment ilike '%'||{0}||'%'
         </property>
							 <property name="pattern" value="<id>([0-9]{16}):([0-9A-F]{32})</>" />
        </criteria>
         </filter>
    <filter name="BeforeDate">
       <property <criteria placeholdername="separator" value="where-clause"/>
            <![CDATA[
                 updated_date < {0}
<property name="emptySeparator" value="true"/>
	            ]]>
        </criteria>bean>
    </filter>
</collection>

Блок кода

language	xml
title	XML метаданных коллекции. Описывается сортировка по умолчанию, категоризация, специфичные колонки
linenumbers	true
collapse	true

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans" 
    xmlns:p="http://www.springframework.org/schema/p" 
      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.springframework.org/schema/beans
 </property>
          http://www.springframework.org/schema/beans/spring-beans-3.2.xsd" default-lazy-init="true">

	<bean id="WorkplaceRequests_(vw_cmj_all)Metadata"
        </bean>        class="ru.intertrust.cm_sochi.srv.connector.sochi.collections.CollectionMetadataNew" p:caseSensitiveFieldNames="true">

             <constructor-arg>
</list>
        </constructor-arg>        
    </bean>
</beans>

Примечание
Названия корневого bean должно строиться из названия collection и префикса Metadata. Пример: WorkplaceRequests_(vw_cmj_all)Metadata

Тип элемента коллекции

Описывает тип колонки и её название

Идентификатор колонки должен совпадать с названием колонки, возвращаемой в SQL-запросе кастомной коллекции.

Блок кода

language	xml
title	Пример. Типов элемента
linenumbers	true
collapse	true

<?xml  <list value-type="ru.intertrust.cm_sochi.srv.connector.sochi.collections.Fieldversion='1.0' encoding='UTF-8'?>
<catalog xmlns="http://www.intertrust.ru/schema/palette/cat-ui">
  <column              <bean class="ru.intertrust.cm_sochi.srv.connector.sochi.collections.Field" p:name="self">
                    <property name="virtualFieldname="finishtime" subject="${catalog.column.finishtime.subject:Дата исполнения}" extends="datetime">
    <meta>
      <documentation>Фактический срок исполнения</documentation>
    </meta>
  </column>
  <column    <bean class="ru.intertrust.cm_sochi.srv.connector.sochi.collections.BuildVirtualFieldname="NumberWPRequest" subject="${catalog.column.NumberWPRequest.subject:Номер}" extends="string">
    <meta>
      <documentation>Номер</documentation>
    </meta>
  </column>
  <column name="NumberWPRequestOneline" subject="${catalog.column.NumberWPRequestOneline.subject:Номер         <property name="realFieldsзаявки}" extends="string">
    <meta>
      <documentation>Номер заявки</documentation>
         </meta>
  </column>
  <column name="statusWPRequest" subject="${catalog.column.statusWPRequest.subject:Статус документа}" extends="string">
     <list><meta>
      <documentation>Статус</documentation>
    </meta>
  </column>
  <column name="term" subject="${catalog.column.term.subject:Срок исполнения по регламенту}" extends="datetime">
    <meta>
      <documentation>Срок исполнения     <value>self_1</value>по регламенту</documentation>
    </meta>
  </column>
  <complex-column        name="wpEmpDepartment" subject="${catalog.column.wpEmpDepartment.subject:СП Работника по заявке}" extends="vcard">
    <meta>
      <documentation>СП Работника по    	<value>Module</value>заявке</documentation>
    </meta>
    <column-ref ref="shortName"/>
    <column-ref ref="id"/>
    <column-ref ref="hint"/>
  </complex-column>
  <complex-column name="wpEmployee" subject="${catalog.column.wpEmployee.subject:Работник по заявке}" extends="vcard">
        <value>self_2</value><meta>
      <documentation>Работник       по заявке</documentation>
    </meta>
    <column-ref ref="shortName"/>
    <column-ref ref="id"/>
    <column-ref ref="hint"/>
    <value>ID</value></complex-column>
  <complex-column name="wpInitBranch" subject="${catalog.column.wpInitBranch.subject:Филиал/ ГО}" extends="vcard">
    <meta>
      <documentation>Филиал/ ГО</documentation>
    </meta>
    <column-ref ref="shortName"/>
    <column-ref ref="id"/>
     <value>created_date</value><column-ref ref="hint"/>
  </complex-column>
  <complex-column name="wpInitiatorDepartment" subject="${catalog.column.wpInitiatorDepartment.subject:СП инициатора}" extends="vcard">
    <meta>
      <documentation>СП инициатора</documentation>
    </meta>
    <column-ref ref="shortName"/>
        <value>self_3</value><column-ref ref="id"/>
    <column-ref ref="hint"/>
  </complex-column>
  <complex-column name="wpInitOrganization"        subject="${wp.Organization:Организация}" extends="vcard">
    <meta>
        <documentation>Организация</documentation>
    </list>meta>
    <column-ref ref="shortName"/>
    <column-ref ref="id"/>
    <column-ref               </property>
							 <propertyref="hint"/>
  </complex-column>
  <complex-column name="patternwpArea" valuesubject="<id>([0-9]{16}):([0-9A-F]{32})</>" /${wp.Area:Зона СП}" extends="vcard">
    <meta>
      <documentation>Зона СП</documentation>
    </meta>
    <column-ref ref="shortName"/>
    <column-ref ref="id"/>
    <column-ref ref="hint"/>
  </complex-column>
  <property<complex-column name="separator"wpInitiator" subject="${wp.Inititator:Инициатор}" valueextends="vcard"/>
    <meta>
      <documentation>Инициатор</documentation>
    </meta>
    <column-ref ref="shortName"/>
    <column-ref ref="id"/>
    <column-ref ref="hint"/>
  </complex-column>
  <column  <property name="emptySeparator" value="truename="wpOffice" subject="${wp.Office:Офис}" extends="string"/>
	  <column name="wpTypeOffice" subject="${wp.TypeOffice:Тип офиса}" extends="string"/>
  <column name="wpLocality"           </bean>subject="${wp.Locality:Город}" extends="string"/>
  <column name="wpCountry"                 </property>
                </bean>         
            </list>
        </constructor-arg>        
    </bean>
</beans>

Примечание
Названия корневого bean должно строиться из названия collection и префикса Metadata. Пример: WorkplaceRequests_(vw_cmj_all)Metadata

Тип элемента коллекции

Описывает тип колонки и её название

Идентификатор колонки должен совпадать с названием колонки, возвращаемой в SQL-запросе кастомной коллекции.

subject="${wp.Country:Страна}" extends="string"/>
  <column name="wpFloor" subject="${wp.Floor:Этаж}" extends="string"/>
  <column name="wpRoomNumber" subject="${wp.RoomNumber:Номер кабинета}" extends="string"/>
  <column name="wpCode" subject="${wp.Code:Код РМ}" extends="string"/>
  <column name="wpEmpGoingToWork" subject="${wp.GoingToWork:Дата выхода}" extends="datetime"/>
  <column name="wpMovingDate" subject="${wp.wpMovingDate:Дата переезда}" extends="datetime"/>
  <column name="wpEmpHierDepatment" subject="${wp.wpEmpHierDepatment:Подразделение}" extends="string"/>
</catalog>

Дескриптор

Дескриптор решает следующие задачи:

Связывает колонки с табличными данными
Управляет колонками: категоризация, сортировка, фильтрация, скрытие, важность и т.д.
Определяет по какому алгоритму будут отображаться данные в Web-клиенте, с помощью builder
Связывает с модулем
Подключает кастомные кнопки

Блок кода

language	xml
title	Пример. Дескриптора
linenumbers	true
collapse	true

<?xml version='1.0' encoding='UTF-8'?>
<catalog xmlns="http://www.intertrust.ru/schema/palette/cat-ui">
  <custom-descriptor ident="WorkplaceRequests" view="WorkplaceRequests_(vw_cmj_all)" id="wp-request-all-docs-af5" name="Заявки на организацию РМ - Все" style="default">
    <builder name="ru.intertrust.cm_sochi.srv.uicollections.UiBuilder">
      <config>
        <view-complex-classif level-select="all"/>
      </config>
    </builder>
    <table>
      <columns>
        <column-ref ref="self" important="true"/>
        <column-ref ref="created_date" sort-asc="true" sort-desc="true" filter="true" invisible-default="true"/>
        <column-ref ref="typeRequest" sort-asc="true" sort-desc="true" filter="true" invisible-default="true"/>
        <column-ref ref="regNumber" sort-asc="true" sort-desc="true" filter="true"/>
        <column-ref ref="wpInitBranch" sort-asc="true" sort-desc="true" important="true" filter="true"/>
        <column-ref ref="wpInitiator" sort-asc="true" sort-desc="true" important="true" filter="true"/>
        <column-ref ref="wpInitiatorDepartment" sort-asc="true" sort-desc="true" important="true" filter="true"/>
        <column-ref ref="wpEmployee" sort-asc="true" sort-desc="true" important="true" filter="true"/>
        <column-ref ref="wpEmpDepartment" sort-asc="true" sort-desc="true" important="true" filter="true"/>
        <column-ref ref="wpEmpHierDepatment" sort-desc="true" sort-asc="true" filter="true" invisible-default="true"/>
        <column-ref ref="stage" sort-asc="true" sort-desc="true" important="true" filter="true"/>
        <column-ref ref="statusWPRequest" filter="true" sort-asc="true" sort-desc="true" important="true"/>
        <column-ref ref="term" sort-asc="true" sort-desc="true" important="true" filter="true"/>
        <column-ref ref="finishtime" sort-asc="true" sort-desc="true" important="true" filter="true"/>
        <column-ref ref="wpArea" sort-asc="true" sort-desc="true" filter="true" invisible-default="true"/>
        <column-ref ref="wpCode" sort-asc="true" sort-desc="true" filter="true" invisible-default="true"/>
        <column-ref ref="wpCountry" sort-asc="true" sort-desc="true" filter="true" invisible-default="true"/>
        <column-ref ref="wpInitOrganization"  sort-asc="true" sort-desc="true" filter="true" invisible-default="true"/>
        <column-ref ref="wpFloor" sort-asc="true" sort-desc="true" filter="true" invisible-default="true"/>
        <column-ref ref="wpLocality" sort-asc="true" sort-desc="true" filter="true" invisible-default="true"/>
        <column-ref ref="wpRoomNumber" sort-asc="true" sort-desc="true" filter="true" invisible-default="true"/>
        <column-ref ref="wpTypeOffice" sort-asc="true" sort-desc="true" filter="true" invisible-default="true"/>
        <column-ref ref="wpOffice" sort-asc="true" sort-desc="true" filter="true" invisible-default="true"/>
        <column-ref ref="wpEmpGoingToWork" sort-asc="true" sort-desc="true" filter="true" invisible-default="true"/>
        <column-ref ref="wpMovingDate" sort-asc="true" sort-desc="true" filter="true" invisible-default="true"/>
      </columns>
    </table>
    <custom-actions schema-ref="RkkReadersCollection"/>
  </custom-descriptor>
</catalog>

Представление

Представление связывает дескриптор с навигационной панелью.

Блок кода

language	xml
title	Пример. Представления
linenumbers	true
collapse	true

  <view parent-ref="request" id="workplace-requests" name="${catalog.view.workplace_requests.name:Заявки на ОРМ}" position="10">
    <pagename name="Заявки на ОРМ"/>
    <descriptor-ref ref="wp-request-all-docs-af5"/>
    <source>
      <module ident="WorkplaceRequests"/>
    </source>
  </view>

Инструменты отладки

Кастомные коллекции

Отлаживать кастомные коллекции можно через Управление конфигурациями. Данный раздел находится в админ-клиенте AF5 в Структуре Системы.

С помощью фильтров ищется нужная коллекция, открывается на редактирование, на вкладку Черновик вносится код xml-коллекции, сохраняется.

Далее выделяется данная коллекция и нажимается кнопка "Применить черновик"

Изменения применяются без dropcache

Дескрипторы и представления

Дескрипторы и представления отлаживать удобнее, через заливу Палитры XML, но возможность сделать через админку AF5 есть.

Нужно перейти в раздел Структура системы\Каталог\Папки и Структура системы\Каталог\Дескрипторы

Дерево страниц

Сравнение версий

Старая версия 16

Новая версия Текущий

Ключ

Создание основы

Кастомная коллекция

Создание представления

SQL-запрос

Кастомная коллекция

Тип элемента коллекции

Тип элемента коллекции

Дескриптор

Представление

Инструменты отладки

Кастомные коллекции

Дескрипторы и представления