Please enable JavaScript.

Coggle requires JavaScript to display documents.

Скрипты (procedure [dbo].[cm_load_users_5] (PZKD_PROD..PZKD_VIEW.ALL_USER…

- - - - dbo.Users
        
        Insert into
        Phone << internal_phone
        Nick << username
        DisplayName << last_name_ru + first_name_ru + middle_name_ru (! возможно ошибки)
        NewStatusNotifyBit << 1
        ExtParamNotifyBit << 1
        SMSNewComment << 0
        SMSNewTask << 0
        Email << email
        ViewFullName << 0
        newTaskCenter << 0
        PreviewPos << 0
        OverdueNotification << 0
        LanguageID << 1
        IsFired_2 << 0
        NewStatusCustomer << 1
        RemoveFromSubscribers << 0
        ShowRestInsteadOrderedTime << 0
        SignsAllOwnedTasks << 0
        LogFailsCount << 0
        IsEmployee << 1
        CanEditOwnInfo << 0
        Country << country_code
        City << city
        UseStartUpPage << 1
        DisableMail << 0
        IsInMaternityLeave << 0
        ExternalEmail << ''
        DisableMailWhenOnline << 0
        ExternalDisplayName << smp_userid
        AutoReadComments << 0
        Gender << gender
        SID << user_adsid
        ShowUserTooltip << 0
        RestrictCellPhoneView << 0
        AutoReadCommentsInMTF << 0
        UseJSUploader << 0
        ShowIAndMeInsteadOfDisplayName << 0
        LastName << last_name_ru
        FirstName << first_name_ru (! возможны ошибки)
        MiddleName << middle_name_ru (! возможны ошибки)
        UserNameMode << 1
        AllowChangeDisplayNameMode << 0
        ShowImgPreviews << 1
        EmailFilesAsAttachments << 0
        GUID ? (user_guid = 36, user_guid, newid())
        DoNotUseGeolocation << 0
        DoSyncWithExchange << 0
        NotNotifyAboutOverdueTasks << 0
        ShowStartupPageOnLogoClick << 1
        MuteSound << 0
        ForceChangePassword << 0
        ShowAgoInsteadTaskStartTime << 0
        SendCommentByEnter << 0
        SyncContactsWithExchange << 0
        DisableMailWhenWorkTime << 0
        CanEditWorkInfo << 0
        CanEditGeoInfo << 0
        CanEditContactsInfo << 0
        CanEditPrivateInfo << 0
        HideBirthYear << 0
        NewTaskNotifyBit << 1
        DueChangedNotifyBit << 1
        NewCommentNotifyBit << 1
        TaskPerformerNotifyBit << 0
        SignatureNotifyBit << 1
        SubscribersNotifyBit << 0
        DisableMailWhenAbsence << 0
        OpenFirstTaskInTableView << 0
        RemoveFinishedTasksFromFavourites << 0
        TimeZone << 'Russian Standard Time'
        AutoReadCommentWhenTaskCloses << 0
        CloseFinishedTasks << 0
        LastPersonalInfoUpdateTime << getdate()
        NotSetNeedAnswerCheckboxAutomatically << 0
        CanEditAvatar << 0
        CanEditPassword << 0
        DisableWebDavOfficeEditing << 0
        FilterCommentsDependByRoleInTask << 0
        UserIPPhone << 0
        ServiceNotifyBit << 1
        SubtasksNotifyBit << 1
        TaskTextChangedNotifyBit << 1
        TaskFileAddedNotifyBit << 1
        TaskSubCatChangedNotifyBit << 1
        OverdueNotifyBit << 1
        OwnerChangedNotifyBit << 1
        TaskFileDeletedNotifyBit << 1
        WorkAmountNotifyBit << 1
        EnglishDisplayName << first_name_en + " " + last_name_en
      - PZKD_PROD..SED4_PZKD.SMP_POSITION p
        
        select
        SCOPE_IDENTITY() - последнее значение в идентифицирующем столбце таблицы, созданный в этом сеансе и области
        o.Id
        1
        join dbo.OrgStructureUnit o on o.ExternalID = p.id
        where p.employee_id = Smp_UserID and not exists (select * from dbo.UserOrgStructureUnit p where p.IsPrimary =1 and p.UserID = SCOPE_IDENTITY())
        
        dbo.UserOrgStructureUnit
        
        insert into
        UserID
        OrgStructureUnitId
        IsPrimary
    - - dbo.Users
        
        Update
        
        Возможны ошибке в передачи неймов (ферст ласт)
        
        Nick << p.UserName
        SID << p.user_adsid
        email << p.email
        FirstName << p.first_name_ru
        LastName << p.last_name_ru
        MiddleName << p.middle_name_ru
        Phone << p.internal_phone
        Country << p.country_code
        City << p.city
        Gender << p.gender
        EnglishDisplayName << p.last_name_ru + " " + p.first_name_ru
        Where ExternalDisplayName = smp_userid
  - - - *dbo.UserOrgStructureUnit
        Update
        OrgStructureUnitId = o.ID
        Where UserID = u.UserID;
  - - - dbo.UserOrgStructureUnit
        
        Delete from
        where id in
- - - - insert0
        
        Select
        u.UserID
        949
        left join UserOrgStructureUnit uo on uo.UserID=u.UserID
        left join OrgStructureUnit ou on uo.OrgStructureUnitId=ou.id
        where (ou.Name like ''%Директор округа%'' or ou.Name like ''%Директор по продажам округа%'') and uo.IsPrimary=1'
        and not exists (select * from userGroupsActual g where g.UserID = u.UserID and g.GroupID = 949
        
        dbo.UserGroopsActual
        
        insert into
        UserID << UserID
        GroupID << 949
        
        Select
        u.UserID
        949
        left join UserOrgStructureUnit uo on uo.UserID=u.UserID
        left join OrgStructureUnit ou on uo.OrgStructureUnitId=ou.id
        where (ou.Name like ''%Директор округа%'' or ou.Name like ''%Директор по продажам округа%'') and uo.IsPrimary=1'
        and not exists (select * from userGroups g where g.UserID = u.UserID and g.GroupID = 949
        
        dbo.UserGroops
        
        insert into
        UserID << UserID
        GroupID << 949
      - insert1
        
        Select
        u.UserID
        959
        left join UserOrgStructureUnit uo on uo.UserID=u.UserID
        left join OrgStructureUnit ou on uo.OrgStructureUnitId=ou.id
        where (ou.Name like ''%Директор отделения%'' or ou.Name like ''%Управляющий отделением%'') and uo.IsPrimary=1'
        and not exists (select * from userGroups g where g.UserID = u.UserID and g.GroupID = 959
        
        dbo.UserGroops
        
        insert into
        UserID << UserID
        GroupID << 959
        
        Select
        u.UserID
        959
        left join UserOrgStructureUnit uo on uo.UserID=u.UserID
        left join OrgStructureUnit ou on uo.OrgStructureUnitId=ou.id
        where (ou.Name like ''%Директор отделения%'' or ou.Name like ''%Управляющий отделением%'') and uo.IsPrimary=1'
        and not exists (select * from userGroupsActual g where g.UserID = u.UserID and g.GroupID = 959
        
        dbo.UserGroopsActual
        
        insert into
        UserID << UserID
        GroupID << 959
      - insertN
        
        ................
        То же самое для должностей
        Технический директор - 948
        Директор Филиала - 950
        u.nick like ADM - 940
        u.nick like DIR - 941
    - - -- - лицензии, группа users (971)
        
        Select
        u.UserId
        3, 6
        Where 1 = 1
        and not exists (select * from UserClientAccessLicense c where c.UserID = u.UserID and Module = 3, 6
        
        *Dbo.UserClientAccessLicense
        Insert into
        UserId << UserId
        Module << 3, 6
        
        Select
        u.UserId
        971
        Where 1 = 1
        and not exists (select * from userGroups g where g.UserID = u.UserID and g.GroupID = 971
        
        dbo.UserGroups
        Insert into
        UserID << UserID
        GroupID << 971
        
        Select
        u.UserId
        971
        Where 1 = 1
        and not exists (select * from userGroupsActual g where g.UserID = u.UserID and g.GroupID = 971
        
        dbo.userGroupsActual
        Insert into
        UserID << UserID
        GroupID << 971
      - --БЦ Авиатор - лицензии, группа Авиатор (972)
        --БЦ Линкор - лицензии, группа Линкор (973)
        
        Select u.UserID
        3,6
        where UserText like ''%Авиатор%''' OR
        where UserText like ''%Линкор%''';
        and not exists (select * from UserClientAccessLicense c where c.UserID = u.UserID and Module = 3, 6
        
        *Dbo.UserClientAccessLicense
        Insert into
        UserId << UserId
        Module << 3, 6
        
        Select
        u.UserID
        972, 973, 943
        where UserText like ''%Авиатор%''' OR
        where UserText like ''%Линкор%''';
        and not exists (select * from userGroups g where g.UserID = u.UserID and g.GroupID = 972, 973, 943
        
        dbo.UserGroups
        Insert into
        UserID << UserID
        GroupID << 972, 973, 943
        
        Select
        u.UserID
        972, 973, 943
        where UserText like ''%Авиатор%''' OR
        where UserText like ''%Линкор%''';
        and not exists (select * from userGroupsActual g where g.UserID = u.UserID and g.GroupID = 972,973,943
        
        dbo.userGroupsActual
        Insert into
        UserID << UserID
        GroupID << 972, 973, 943
      - --департамент персонала - лицензии и группа 943
        --Отдел кадров - лицензии и группа 943
        --УИС - лицензии и группа 943
        --ДРРС - лицензии и группа 943
        --ДУТК - лицензии и группа 943
        ----ТехноОтдел ФУ - лицензии и группа 961
        
        Select
        u.UserID
        3,6
        where exists (select t.id from tc_GetUsersOrgUnitsRecurcive (u.UserID) t where t.id = 9349, 9723, 9354, 9710, 9344, 9736
        and not exists (select * from UserClientAccessLicense c where c.UserID = u.UserID and Module = 3, 6
        
        dbo.UserClientAccessLicense
        Insert into
        UserID << UserID
        Module << 3,6
        
        Select
        u.UserID
        943, 961
        where exists (select t.id from tc_GetUsersOrgUnitsRecurcive (u.UserID) t where t.id = 9349, 9723, 9354, 9710, 9344, 9736
        and not exists (select * from userGroups g where g.UserID = u.UserID and g.GroupID = 943, 961
        
        dbo.UserGroups
        Insert into
        UserID << UserID
        GroupID << 943, 961
        
        Select
        u.UserID
        943, 961
        where exists (select t.id from tc_GetUsersOrgUnitsRecurcive (u.UserID) t where t.id = 9349, 9723, 9354, 9710, 9344, 9736
        and not exists (select * from userGroupsActual g where g.UserID = u.UserID and g.GroupID = 943, 961
        
        dbo.userGroupsActual
        Insert into
        UserID << UserID
        GroupID << 943, 961
      - --ПиР- минус РС СМ - лицензии и группа 943
        
        Select
        u.UserID
        3,6
        where exists (select t.id from tc_GetUsersOrgUnitsRecurcive (u.UserID) t where t.id = 9346)
        and not exists (select t.id from tc_GetUsersOrgUnitsRecurcive (u.UserID) t where t.id = 9708)
        and not exists (select * from UserClientAccessLicense c where c.UserID = u.UserID and Module = 3, 6
        
        dbo.UserClientAccessLicense
        Insert into
        UserID << UserID
        Module << 3,6
        
        Select
        u.UserID
        943
        where exists (select t.id from tc_GetUsersOrgUnitsRecurcive (u.UserID) t where t.id = 9346)
        and not exists (select t.id from tc_GetUsersOrgUnitsRecurcive (u.UserID) t where t.id = 9708)'
        and not exists (select * from userGroups g where g.UserID = u.UserID and g.GroupID = 943
        
        dbo.UserGroups
        Insert into
        UserID << UserID
        GroupID << 943
        
        Select
        u.UserID
        943
        where exists (select t.id from tc_GetUsersOrgUnitsRecurcive (u.UserID) t where t.id = 9346)
        and not exists (select t.id from tc_GetUsersOrgUnitsRecurcive (u.UserID) t where t.id = 9708)
        and not exists (select * from userGroupsActual g where g.UserID = u.UserID and g.GroupID = 943
        
        dbo.userGroupsActual
        insert into
        UserID << UserID
        GroupID << 943
  - - - Select
        u.UserID
        3,6
        join tasks t on t.TaskID = u.TaskID
        where t.SubcatID = 122,128,131
        and ExtParamID = 434'
        and not exists (select * from UserClientAccessLicense c where c.UserID = u.UserID and Module = 3, 6
        
        dbo.UserClientccessLicense
        insert into
        UserID << UserID
        Module << 3,6
      - Select
        u.UserID
        946, 945, 935
        join tasks t on t.TaskID = u.TaskID
        where t.SubcatID = 122,128,131
        and ExtParamID = 434'
        and not exists (select * from userGroups g where g.UserID = u.UserID and g.GroupID = 946, 945, 935
        
        dbo.UserGroups
        insert into
        UserID << UserID
        GroupID << 946, 945, 935
      - Select
        u.UserID
        946,945,935
        join tasks t on t.TaskID = u.TaskID
        where t.SubcatID = 122,128,131
        and ExtParamID = 434'
        and not exists (select * from userGroupsActual g where g.UserID = u.UserID and g.GroupID = 946,945,935
        
        dbo.userGroupsActual
        insert into
        UserID << UserID
        GroupID << 946, 945, 935
- - - - dbo.OrgStructureUnit
        
        --затем - апдейтим уже существующие, если у них изменились свойства (все кроме id - parent и name)
        update OrgStructureUnit
        where id = o.id
        Name << name_ru
        ParentExternalID << p.parent_id
        ParentID << (select min(parentID) from OrgStructureUnit where ExternalID = o.ParentExternalID
    - - dbo.OrgStructureUnit
        
        Процедура обновления оргструктуры предприятия.
        вначале - вставляем новые.
        Insert into
        Name << name_ru
        ParentId << ?(parent_id is null, 42, ID from OrgStructureUnit where ExternalID = parent_id)
        OrgStructureTypeID << 5
        IsActual << 1
        DoNotShowInOrgStructure << 0
        IsDirector << 0
        LinkedGroupId << null
        GuidFrom1C << null
        IsFuncGroup << 0
        AppointmentId << null
        Description << null
        ParentGuidFrom1C << null
        GUID << NEWID()
        ExternalID << dep_id
        ParentExternalID << ?(parent_id is null, 42, ID from OrgStructureUnit where ExternalID = parent_id)
        IsCommercialInfo << 0
        HideInUserInfo << 0
    - - dbo.OrgStructureUnit
        
        --и помечаем удаленные из ПЗКД
        update OrgstructureUnit
        IsActual << 0
        Where id NOT In id
- - - - dbo.OrgStructureUnit
        
        Процедура обновления оргструктуры предприятия.
        вначале вставляем новые
        Indert into
        Name << name_ru
        ParentId << ID from OrgStructureUnit where ExternalID = department_id
        OrgStructureTypeID << 6
        IsActual << 1
        DoNotShowInOrgStructure << 0
        IsDirector << 0
        LinkedGroupId << null
        GuidFrom1C << null
        IsFuncGroup << 0
        AppointmentId << null
        Description << null
        ParentGuidFrom1C << null
        GUID << NEWID()
        ExternalID << id
        ParentExternalID << ID from OrgStructureUnit where ExternalID = department_id
        IsCommercialInfo << 0
        HideInUserInfo << 0
    - - dbo.OrgStructureUnit
        
        --затем - апдейтим уже существующие, если у них изменились
        update OrgStructureUnit where id = o.id
        Name << name_ru
        ParentExternalID << p.department_id
        ParentID << (select min(parentID) from OrgStructureUnit where ExternalID = o.ParentExternalID)
    - - dbo.OrgStructureUnit
        
        --и помечаем удаленные из ПЗКД
        update OrgstructureUnit
        IsActual << 0
        Where id NOT In id
- - - - *dbo.OrgstructureUnit
        --вставляем новые должности директоров
        Insert into
        Name << p.name_uni
        ParentId << = ID from OrgStructureUnit where ExternalID = p.dep_id
        OrgStructureTypeID << 6
        IsActual << 1
        DoNotShowInOrgStructure << 0
        IsDirector << 1
        LinkedGroupId << null
        GuidFrom1C << null
        IsFuncGroup << 0
        AppointmentId << null
        Description << null
        ParentGuidFrom1C << null
        GUID << NEWID()
        ExternalID << p.pos_id
        ParentExternalID << p.dep_id
        IsCommercialInfo << 0
        HideInUserInfo << 0
    - - dbo.OrgstructureUnit
        
        --?????????? Как проапдейть?
        --Измениться у дополнительной должности начальника может только родитель (т.е. тот же ID, но в другом месте дерева). По-видимому, апдейт не требуется.
        --убираем удаленные (помечаем)
        Update
        IsActual << 0
        Where Id in
- - - - dbo.OrstructureUnit
        
        Update
        IsDirector << 1
        Where ExternalId = pos_id
- - - - *dbo.Users
        Update
        *rtrim - Возвращает строку символов, из которой удалены все завершающие пробелы.
        
        UserText << (select top 1
        rtrim(
        (select top 1 p.short_name from [1c1-new].SMP_PUBLISHER2.dbo.view_ofc p where p.id = o.parent_id order by o.date_time_change desc))+ ' '+
        short_name from [1c1-new].SMP_PUBLISHER2.dbo.view_ofc o where o.id = u.ofc_id order by o.date_time_change desc)
        from [1c1-new].SMP_PUBLISHER2.dbo.view_units u where u.id = ExternalDisplayName
        order by u.DATE_TIME_CHANGE desc
        where ExternalDisplayName = ExternalDisplayName;