SELECT DISTINCT el.ACTIVE,
case
when el.id is null then 0 else el.id end as id,
el.DETAIL_TEXT as itemText,
case when be.PROPERTY_499 is null then 0 else 1 end as itemHide,
be.PROPERTY_508 as datadoc,
be.PROPERTY_501 as versiondoc,
be.PROPERTY_502 as siteID,
be.PROPERTY_500 as unit,
mt.UF_FULL_DESCRIPTION as groupNameL0,
mt.UF_ORG_UNIT as groupNameL1,
mt.UF_ITEMPROP as itemProp,
mt.UF_ITEMSCOPE as itemScope,
mt.UF_TYPE as itemType,
mt.UF_SORT2 as sort,
mt.UF_MAIN_ITEM_PROP as mainItemProp,
if (mt.UF_ADD_DATE=1 and (be.PROPERTY_503!=263 or be.PROPERTY_503 is NULL),1,0) as addDate,
f.SUBDIR as fdir,
f.FILE_NAME as fname,f.ID as fileID,
fsig.SUBDIR as fsigdir,
fsig.FILE_NAME as fsigname,fsig.ID as filesigID,
mt.UF_SCOUP_NAME,
mt.UF_SORT1,
mt.UF_XML_ID as xmlId,
group_concat(bs.name separator ",") as sectionName,
group_concat(bs.id separator ",") as sectionId,
bs.name as sectionName,
bs.id as sectionId,
case
when mt.UF_USE_NAME=1 then el.name
when mt.UF_USE_NAME=0 and mt.UF_ADDNAME!="" then mt.UF_ADDNAME
else mt.UF_NAME
end as name,
case
when mt.UF_GROUP and not mt.UF_USE_NAME and mt.UF_ADDNAME!='' then mt.UF_ADDNAME
when mt.UF_GROUP and not mt.UF_USE_NAME and mt.UF_ADDNAME='' then mt.UF_NAME
else "NaN"
end as itempropgroupname,
concat(
(if (mt.UF_SORT is null,0, mt.UF_SORT*100000000)+if(mt.UF_SORT1 is null,0,mt.UF_SORT1*1000000) + if(mt.UF_SORT2 is null,0,mt.UF_SORT2)),'-',
(if(el.SORT is null,0,el.SORT)),
(if(be.PROPERTY_508 is null,0,DATE_FORMAT(be.PROPERTY_508,'%Y%m%d')))) as globalsort,
1 as sigreg
FROM `b_hlbd_mikrotegi` mt
LEFT JOIN `b_iblock_element_prop_s67` be on be.PROPERTY_507=mt.UF_XML_ID
LEFT JOIN `b_iblock_element` el on el.ID=be.IBLOCK_ELEMENT_ID
LEFT JOIN `b_iblock_section_element` se on se.IBLOCK_ELEMENT_ID=el.id
LEFT JOIN `b_iblock_section` bs on bs.id=se.IBLOCK_SECTION_ID
LEFT JOIN `b_file` f on be.PROPERTY_506=f.ID
LEFT JOIN `b_file` fsig on be.PROPERTY_505=fsig.ID
WHERE ((el.ACTIVE="Y") or (el.id is not NULL))
and (mt.UF_DISABLED is NULL OR mt.UF_DISABLED=0)
AND true and ( mt.UF_ITEMPROP ='licenseDocLink') group by el.id order by bs.id, mt.UF_SORT, mt.UF_SORT1,mt.UF_SORT2,el.SORT