Документы

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, be.PROPERTY_595 as href, 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 sectionNames, group_concat(bs.id separator ",") as sectionIds, bs.name as sectionName, bs.id as sectionId, case when mt.UF_USE_NAME=1 and be.PROPERTY_676!="" then be.PROPERTY_676 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)), (if(el.SORT is null,0,el.SORT)), (if(be.PROPERTY_508 is null,7001,DATE_FORMAT(be.PROPERTY_508,'%y%m'))), '-',el.id) 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 ( bs.id in(393,402,403,404,406,407,408,409,410,411) ) and ( mt.UF_ITEMPROP in('ustavDocLink','localActStud','localActOrder','localActCollec')) or mt.UF_MAIN_ITEM_PROP in('ustavDocLink','localActStud','localActOrder','localActCollec') group by el.id order by bs.id, mt.UF_SORT, mt.UF_SORT1,mt.UF_SORT2,el.SORT

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, be.PROPERTY_595 as href, 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 sectionNames, group_concat(bs.id separator ",") as sectionIds, bs.name as sectionName, bs.id as sectionId, case when mt.UF_USE_NAME=1 and be.PROPERTY_676!="" then be.PROPERTY_676 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)), (if(el.SORT is null,0,el.SORT)), (if(be.PROPERTY_508 is null,7001,DATE_FORMAT(be.PROPERTY_508,'%y%m'))), '-',el.id) 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 ( bs.id in(393,402,403,404,406,407,408,409,410,411) ) and ( mt.UF_ITEMPROP in('priemDocLink','modeDocLink','tekKontrolDocLink','perevodDocLink','vozDocLink')) or mt.UF_MAIN_ITEM_PROP in('priemDocLink','modeDocLink','tekKontrolDocLink','perevodDocLink','vozDocLink') group by el.id order by bs.id, mt.UF_SORT, mt.UF_SORT1,mt.UF_SORT2,el.SORT

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, be.PROPERTY_595 as href, 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 sectionNames, group_concat(bs.id separator ",") as sectionIds, bs.name as sectionName, bs.id as sectionId, case when mt.UF_USE_NAME=1 and be.PROPERTY_676!="" then be.PROPERTY_676 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)), (if(el.SORT is null,0,el.SORT)), (if(be.PROPERTY_508 is null,7001,DATE_FORMAT(be.PROPERTY_508,'%y%m'))), '-',el.id) 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 ( bs.id in(393,402,403,404,406,407,408,409,410,411) ) and ( mt.UF_ITEMPROP in('reportEduDocLink','prescriptionDocLink','localAct','localActPersonal')) or mt.UF_MAIN_ITEM_PROP in('reportEduDocLink','prescriptionDocLink','localAct','localActPersonal') group by el.id order by bs.id, mt.UF_SORT, mt.UF_SORT1,mt.UF_SORT2,el.SORT