#sonic ip mails

SELECT A.UM_GROUP AS INDX, A.UM_USER, B.UM_GROUP AS ROLE
  FROM UM_GROUP_UM_USER AS A
  LEFT JOIN UM_GROUP_UM_USER AS B
  ON A.UM_USER = B.UM_USER
  WHERE A.UM_GROUP = %s AND B.UM_GROUP = %s

SELECT * FROM RUSPOST_OFFICE_CARD WHERE ufps_name LIKE '%БАШ%'

SELECT * FROM um_user WHERE id = 9967;

//autorun queries
SELECT DISTINCT UFPS_INDEX, UFPS_NAME FROM POSTAMT_VIEW
SELECT ID AS DEPARTMENT_CONTROLLER_GROUP_ID FROM UM_GROUP WHERE NAME = 'RUSPOST-DEPARTMENT-CONTROLLER'
//61497
SELECT POSTAMT_INDEX, POSTAMT_NAME FROM POSTAMT_VIEW WHERE UFPS_INDEX = 450700
//450700 - bashkortostan ufps
SELECT A.UM_GROUP AS UFPS_INDEX, A.UM_USER, B.UM_GROUP AS DEPARTMENT_CONTROLLER_GROUP_ID
    FROM UM_GROUP_UM_USER A
    LEFT JOIN UM_GROUP_UM_USER B
    ON A.UM_USER = B.UM_USER
    WHERE A.UM_GROUP = 450700 AND
        B.UM_GROUP = 61497

SELECT COUNT(*) FROM RT_SUBINST

SELECT ID AS LATEST_VER_ID
    FROM RT_PROCDESC
    WHERE UPLOADDATE = (
        SELECT MAX(UPLOADDATE)
            FROM RT_PROCDESC
            WHERE JPASS_ID = 'ProcessDescription_fNqQQWBEEea_v8jd3FSzfA')

SELECT ID AS OPERATOR_ID FROM UM_GROUP WHERE NAME = 'RUSPOST-OPERATOR'

SELECT ID FROM UM_GROUP WHERE NAME LIKE '%БИЧ%'
#61495
#бичура-почтамт 671389

SELECT A.UM_GROUP AS POSTAMT_INDEX, A.UM_USER, B.UM_GROUP AS RUSPOST_OPERATOR_GROUP_ID
    FROM UM_GROUP_UM_USER A
    LEFT JOIN UM_GROUP_UM_USER B
    ON A.UM_USER = B.UM_USER    WHERE A.UM_GROUP = 671389
        AND B.UM_GROUP = 61495

#bob 145935
#max 145952

SELECT COUNT(*) AS ACTIVE_TASK_NUM
    FROM (SELECT RT_PROCDESC, RT_SUBDESC, UM_USER_EDITOR
        FROM RT_SUBINST
        WHERE DROPPED = 0 AND
            AVAILABLETRANSITIONS = 1
            AND UM_USER_EDITOR = 145935) A
        JOIN (SELECT ID, JPASS_ID, NAME
            FROM RT_SUBDESC
            WHERE JPASS_ID = 'InternalSubjectDescription_xRtowSvvEea91xRuMXueEA') B
        ON A.RT_SUBDESC = B.ID


SELECT COUNT(*) AS ACTIVE_TASK_NUM
    FROM (SELECT RT_SUBDESC
            FROM ((SELECT RT_SUBDESC, CURRENT_STATE_RT_STDESC
                    FROM RT_SUBINST
                    WHERE UM_USER_EDITOR = 9967) A      /*all subjects for concrete user*/
            JOIN (SELECT ID
                    FROM RT_STDESC
                    WHERE END_STATE = 0) B                /*all active states*/
            ON A.CURRENT_STATE_RT_STDESC = B.ID)) C       /*active states for concrete user' subject*/
    JOIN (SELECT ID, JPASS_ID, NAME
            FROM RT_SUBDESC
            WHERE JPASS_ID = 'InternalSubjectDescription_xRtowSvvEea91xRuMXueEA') D /*concrete subjects*/
    ON C.RT_SUBDESC = D.ID                                /*active states for concrete user's concrete subject*/

SELECT COUNT(*) AS ACTIVE_TASK_NUMBER
    FROM (SELECT A.RT_SUBDESC
            FROM RT_SUBINST A
            JOIN RT_STDESC B
            ON A.CURRENT_STATE_RT_STDESC = B.ID
            WHERE A.UM_USER_EDITOR = 345976 AND B.END_STATE = 0) C
    JOIN RT_SUBDESC D
    ON C.RT_SUBDESC = D.ID
    WHERE JPASS_ID = 'InternalSubjectDescription_xRtowSvvEea91xRuMXueEA'



SELECT RT_SUBDESC
    FROM ((SELECT RT_SUBDESC, CURRENT_STATE_RT_STDESC
            FROM RT_SUBINST
            WHERE UM_USER_EDITOR = 145935) A      /*get all subjects for concrete user*/
    JOIN (SELECT ID
            FROM RT_STDESC
            WHERE END_STATE = 0) B                /*get all active states for subject*/
    ON A.CURRENT_STATE_RT_STDESC = B.ID)


  SELECT A.UM_GROUP AS UFPS_INDEX, A.UM_USER, B.UM_GROUP AS DEPARTMENT_CONTROLLER_GROUP_ID
    FROM UM_GROUP_UM_USER A
    LEFT JOIN UM_GROUP_UM_USER B
    ON A.UM_USER = B.UM_USER
    WHERE A.UM_GROUP = 450700 AND
      B.UM_GROUP = 61497

SELECT
      A.UM_GROUP AS UFPS_INDEX,
      A.UM_USER,
      B.UM_GROUP AS DEPARTMENT_CONTROLLER_GROUP_ID
  FROM UM_GROUP_UM_USER A
  LEFT JOIN UM_GROUP_UM_USER B
  ON A.UM_USER = B.UM_USER
WHERE A.UM_GROUP = 248700 AND B.UM_GROUP = 12


SELECT a.id
    FROM um_group a
    JOIN (SELECT um_group, attributename, attributevalue
            FROM um_group_attributes
            WHERE attributevalue = 'region') b
    ON a.id = b.um_group


--UPDATE (SELECT ID, NAME
          FROM UM_GROUP A
          WHERE ID IN (SELECT UM_GROUP
                          FROM UM_GROUP_ATTRIBUTES
                          WHERE ATTRIBUTEVALUE = 'region'))
    SET NAME = NAME || ' ' || ID;


--ALTER TABLE UM_USER_ATTRIBUTES
    DROP CONSTRAINT PK_UM_USER_ATTRIBUTES

--UPDATE UM_USER_ATTRIBUTES
    SET ATTRIBUTENAME = 'mail',
        ATTRIBUTEVALUE = '[email protected]'
    WHERE ATTRIBUTENAME  IN ('mailDisabled', 'mail', 'mailnon');

--ALTER TABLE UM_USER_ATTRIBUTES
    ADD CONSTRAINT PK_UM_USER_ATTRIBUTES PRIMARY KEY (UM_USER, ATTRIBUTENAME);

SELECT a.UM_USER, a.CNT
    FROM (
        SELECT UM_USER, COUNT(uua.ATTRIBUTENAME) AS CNT
            FROM UM_USER_ATTRIBUTES uua
            WHERE uua.ATTRIBUTENAME IN ('mailDisabled', 'mail', 'mailnon')
            GROUP BY uua.UM_USER) a
    WHERE CNT != 1;

SELECT a.UM_USER, a.CNT,
    FROM (
        SELECT UM_USER, COUNT(uua.ATTRIBUTENAME) AS CNT
            FROM UM_USER_ATTRIBUTES uua
            WHERE uua.ATTRIBUTENAME NOT LIKE '%mail%'
            GROUP BY uua.UM_USER) a
    WHERE CNT = 1 AND


INSERT INTO UM_USER_ATTRIBUTES(UM_USER, ATTRIBUTENAME, ATTRIBUTEVALUE)
    SELECT UM_USER, 'mail', '[email protected]'
        FROM (
            SELECT UM_USER, CNT
                FROM (
                    SELECT UM_USER, COUNT(uua.ATTRIBUTENAME) AS CNT
                        FROM UM_USER_ATTRIBUTES uua
                        GROUP BY uua.UM_USER
                )
                WHERE CNT = 1
        )
        WHERE UM_USER NOT IN (366, 46906);