/*
* Автор: Маркелов С.В.
* Дата: 2014-04-23
* Задача: http://jira.softage.ru/jira/browse/SDFO-297
* Назначение: Запрет авторизации уволенного пользователя
* Изменяются объекты:
		CREATE OR REPLACE PACKAGE BODY KFM."PKG_USERS"
*/

/*
  ****** Инструкции для SQLPlus (обязательно) ******
  Для печати сообщений:
  DBMS_OUTPUT.put_line('<Message>');
*/
whenever sqlerror exit 1;
set serveroutput on;
set ECHO off;
set DEFINE off;

declare
	filename      varchar2(256) := 'sfm.database-update.2014-04-23.UpdatePKG_USERS-ForbidLogonOfDismissedUser.sql';
	purpose       varchar2(4000) := 'Запрет авторизации уволенного пользователя';
	SCRIPT_EXISTS number := 0;
begin

	--delete from DB_Update_History where script_name = filename;	commit;
	select count(*)
		into script_exists
		from DB_Update_History
	 where script_name = filename;

	if (script_exists > 0) then
		RAISE_APPLICATION_ERROR(-20001, '"' || filename || '" уже присутствует в базе');
	else
		insert into DB_Update_History
			(script_name, purpose)
		values
			(filename, purpose);
	end if;

	/*
	****** Begin update script ******
	*/
	/*
	****** End update script ******
	*/
	commit;

exception
	when others then
		rollback;
		raise;
end;

/
	/*
	****** Begin update script ******
	*/
		-- Не транзакционный апдейт
CREATE OR REPLACE PACKAGE BODY KFM."PKG_USERS" as
  function GetORALogins(pORALoginsCur out sys_refcursor) return number is
    Result number;
  begin
    Result := 0;

    open pORALoginsCur for
      select a.username   as ORALoginName,
             a.created    as ORACreateDate,
             u.userid,
             u.loginname,
             u.lastname,
             u.firstname,
             u.middlename
        from all_users a
        left join users u
          on a.username = u.loginname;
          
    return(Result);
  end;

  -- Если даты начала и конца блокировки не заданы они устанавливаются автоматически в '01.01.3000'
  function AddUser(pUserID            out number,
                   pLOGINNAME         in VARCHAR2,
                   pLASTNAME          in VARCHAR2,
                   pFIRSTNAME        in VARCHAR2,
                   pMIDDLENAME        in VARCHAR2,
                   pPers_Number       in VARCHAR2,
                   pPers_DocType      in number,
                   pPers_DocSeries    in VARCHAR2,
                   pPers_DocNumber    in VARCHAR2,
                   pPers_DocDate      in date,
                   pPers_DocWhoGive   in VARCHAR2,
                   pJobName           in VARCHAR2,
                   pPhones            in VARCHAR2,
                   pEmails            in VARCHAR2,
                   pIsEnable          in number,
                   pbegin_blockeddate in date,
                   pEnd_blockeddate   in date,
                   pPassword          in varchar2 := NULL,
                   pDEPARTMENT_ID     in number default NULL,
                   pSertificate       in blob default null,
                   pSertificateName   varchar2 default null,
                   pSertificateSize   number,
                   pIsDismissed       in number default 0) return number is
    Result         number default 0;
    vCountUser     number;
    vLoginName     varchar2(100 char);
    vUserId        number;
    vDate_registry date;
    vIsEnable      number;
  begin

    --Проверка входных данных на корректность
    Result := CheckUserData(pUserID,
                            pLOGINNAME,
                            pLASTNAME,
                            pFIRSTNAME,
                            pMIDDLENAME,
                            pPers_Number,
                            pPers_DocType,
                            pPers_DocSeries,
                            pPers_DocNumber,
                            pPers_DocDate,
                            pPers_DocWhoGive,
                            pJobName,
                            pPhones,
                            pEmails,
                            vIsEnable,
                            pbegin_blockeddate,
                            pend_blockeddate,
                            pIsDismissed);
    if (Result <> 0) then
      return Result;
    end if;

    --Проверка существуют ли пользователи с заданным логином
    vLoginName := UPPER(TRIM(pLOGINNAME));
    select count(*)
      into vCountUser
      from users ur
     where UPPER(TRIM(ur.loginname)) = vLoginName;

    if vCountUser = 0 then
      --Попытка добавления пользователя Oracle
      Result := pkg_systasks.AddUser(pLOGINNAME, pPassword);
      if (Result <> 0) then
        return - 1111; --Ошибка добавления пользователя Oracle
      end if;

      --Вставка данных в users
      insert into users
        (loginname,
         lastname,
         firstname,
         middlename,
         pers_number,
         pers_doctype,
         pers_docseries,
         pers_docnumber,
         pers_docdate,
         pers_docwhogive,
         jobname,
         phones,
         emails,
         isenable,
         begin_blockeddate,
         end_blockeddate,
         date_registry,
         sertificate,
         sertificate_name,
         sertificate_size,
         DEPARTMENT_ID,
         IS_DISMISSED)
      values
        (pLoginName,
         pLastName,
         pFirstName,
         pMiddleName,
         pPers_Number,
         pPers_DocType,
         pPers_DocSeries,
         pPers_DocNumber,
         pPers_DocDate,
         pPers_DocWhoGive,
         pJobName,
         pPhones,
         pEmails,
         vIsEnable,
         NVL(pbegin_blockeddate, to_date('01.01.3000', 'DD.MM.YYYY')),
         NVL(pEnd_blockeddate, to_date('01.01.3000', 'DD.MM.YYYY')),
         sysdate,
         pSertificate,
         pSertificateName,
         pSertificateSize,
         pDEPARTMENT_ID,
         pIsDismissed)
      returning UserID, date_registry into vUserID, vDate_Registry;

      pUserID := vUserID;
    else
      return - 1105; --Пользователь с данным логином уже существует
    end if;
    return(Result);

  exception
    when others then
      -- pkg_exceptions.AddException(sqlcode,sqlerrm, 'pkg_users.AddUser');
      raise;
      --return -1;
  end;

  function DeleteUser(pUserID in number) return number is
    Result     number;
    vCountUser number;
  begin
    Result := 0;

    select count(*)
      into vCountUser
      from users ur
     where ur.userid = pUserID;

    --?? Вставить проверку на уже удален???

    if vCountUser = 1 then

      --Добавление новой информации в таблицу
      update users
         set begin_blockeddate = sysdate,
             end_blockeddate   = to_date('01.01.3000', 'DD.MM.YYYY'),
             isenable          = 0
       where USERID = pUserID;
    else
      Result := -1100; --//Нет такого пользователя
    end if;

    return(Result);
  end;

  function DismissUser(pUserID in number) return number is
    Result     number;
    vCountUser number;
  begin
    Result := 0;

    select count(*)
      into vCountUser
      from users ur
     where ur.userid = pUserID;

    --?? Вставить проверку на уже удален???

    if vCountUser = 1 then

      --Добавление новой информации в таблицу
      update users
         set IS_DISMISSED = 1
       where USERID = pUserID;
    else
      Result := -1100; --//Нет такого пользователя
    end if;

    return(Result);
  end;

  -- Если даты начала и конца блокировки не заданы они устанавливаются автоматически в '01.01.3000'
  function SaveUserData(pUserID            in number,
                        pLOGINNAME         in VARCHAR2,
                        pLASTNAME          in VARCHAR2,
                        pFIRSTNAME        in VARCHAR2,
                        pMIDDLENAME        in VARCHAR2,
                        pPers_Number       in VARCHAR2,
                        pPers_DocType      in number,
                        pPers_DocSeries    in VARCHAR2,
                        pPers_DocNumber    in VARCHAR2,
                        pPers_DocDate      in date,
                        pPers_DocWhoGive   in VARCHAR2,
                        pJobName           in VARCHAR2,
                        pPhones            in VARCHAR2,
                        pEmails            in VARCHAR2,
                        pIsEnable          in number,
                        pbegin_blockeddate date,
                        pEnd_blockeddate   in date,
                        pDEPARTMENT_ID     in number default NULL,
                        pSertificate       in blob default null,
                        pSertificateName   varchar2 default null,
                        pSertificateSize   number,
                        pIsDismissed       in number default 0) return number is
    Result          number;
    vCountUser      number default 0;
    vCountUserLogin number default 0;
    vLoginName      varchar2(100 char);
    vIsEnable       number;
  begin

    Result    := 0;
    vIsEnable := pIsEnable;
    select count(*)
      into vCountUser
      from users ur
     where ur.userid = pUserID;

    if vCountUser = 1 then
      -- Пользователь с данным pUserID существует в таблице users
      vLoginName := UPPER(TRIM(pLOGINNAME));
      select count(*)
        into vCountUserLogin
        from users us
       where (us.userid != pUserID)
         and (UPPER(TRIM(us.loginname)) = vLoginName);

      if (vCountUserLogin = 0) then
        -- Логин пользователя   pLOGINNAME уникальный

        -- Проверка данных на корректность
        Result := CheckUserData(pUserID,
                                pLOGINNAME,
                                pLASTNAME,
                                pFIRSTNAME,
                                pMIDDLENAME,
                                pPers_Number,
                                pPers_DocType,
                                pPers_DocSeries,
                                pPers_DocNumber,
                                pPers_DocDate,
                                pPers_DocWhoGive,
                                pJobName,
                                pPhones,
                                pEmails,
                                vIsEnable,
                                pbegin_blockeddate,
                                pend_blockeddate,
                                pIsDismissed);
        if (Result <> 0) then
          return Result;
        end if;

        -- Обновление данных в таблице users
        update users du
           set du.loginname         = pLOGINNAME,
               du.lastname          = pLastName,
               du.firstname         = pFIRSTNAME,
               du.middlename        = pMiddleName,
               du.pers_number       = pPers_Number,
               du.pers_doctype      = pPers_DocType,
               du.pers_docseries    = pPers_DocSeries,
               du.pers_docnumber    = pPers_DocNumber,
               du.pers_docdate      = pPers_DocDate,
               du.pers_docwhogive   = pPers_DocWhoGive,
               du.jobname           = pJobName,
               du.phones            = pPhones,
               du.emails            = pEmails,
               du.isenable          = vIsEnable,
               du.begin_blockeddate = nvl(pbegin_blockeddate,
                                          to_date('01.01.3000', 'DD.MM.YYYY')),
               du.end_blockeddate   = nvl(pEnd_blockeddate,
                                          to_date('01.01.3000', 'DD.MM.YYYY')),
               du.sertificate       = pSertificate,
               du.sertificate_name  = pSertificateName,
               du.sertificate_size  = pSertificateSize,
               du.DEPARTMENT_ID     = pDEPARTMENT_ID,
               du.IS_DISMISSED      = pIsDismissed
         where du.userid = pUserID;

      else
        Result := -1105; --Пользователь с таким логином уже  существует
      end if;

    else
      Result := -1100; --//Нет такого пользователя
    end if;

    return(Result);
  exception
    when others then
      -- pkg_exceptions.AddException(sqlcode,sqlerrm, 'pkg_users.SaveUserData');
      return - 1;
  end SaveUserData;

  function GetUsersList(pUserListType in NUMBER, pCur in out sys_refcursor)
    return number is
    Result number;
  begin
    Result := 0;

    case pUserListType
      when 2 then
        -- Все
        open pCur for
          SELECT du.userid,
                 du.loginname,
                 du.lastname,
                 du.firstname,
                 du.middlename,
                 du.isenable,
                 du.IS_DISMISSED
            FROM users du;
      when 1 then
        -- Открытые
        open pCur for
          SELECT du.userid,
                 du.loginname,
                 du.lastname,
                 du.firstname,
                 du.middlename,
                 du.isenable,
                 du.IS_DISMISSED
            FROM users du
           where du.isenable = 1 and du.IS_DISMISSED = 0;
      when 0 then
        -- Закрытые/заблокированные
        open pCur for
          SELECT du.userid,
                 du.loginname,
                 du.lastname,
                 du.firstname,
                 du.middlename,
                 du.isenable,
                 du.IS_DISMISSED
            FROM users du
           where du.isenable = 0 and du.IS_DISMISSED = 0;
      when -1 then
        -- Уволенные
        open pCur for
          SELECT du.userid,
                 du.loginname,
                 du.lastname,
                 du.firstname,
                 du.middlename,
                 du.isenable,
                 du.IS_DISMISSED
            FROM users du
           where du.IS_DISMISSED != 0;
    else
      Result := -1101; --//Пользователь может находиться только в статусах открыт, закрыт, заблокирован
    end case;

    return(Result);
  end;

  function GetUserByID(pUserID in NUMBER, pUserDataCur out sys_refcursor)
    return number is
    Result     number;
    vCountUser number;
  begin
    Result := 0;

    select count(*)
      into vCountUser
      from users ur
     where ur.userid = pUserID;

    if vCountUser = 1 then
      open pUserDataCur for
        SELECT du.loginname,
               du.lastname,
               du.firstname,
               du.middlename,
               du.pers_number,
               du.pers_doctype,
               du.pers_docseries,
               du.pers_docnumber,
               du.pers_docdate,
               du.pers_docwhogive,
               du.jobname,
               du.phones,
               du.emails,
               du.isenable,
               du.begin_blockeddate,
               du.end_blockeddate,
               du.department_id,
               Sertificate_Name     as SertificateName,
               Sertificate_Size     as SertificateSize,
               du.sertificate       as Sertificate,
               du.IS_DISMISSED
          FROM users du
         where du.userid = pUserID;
    end if;

    if vCountUser <> 1 then
      Result := -1100; --//Нет такого пользователя
    end if;

    return(Result);
  end;

  function GetUserByLogin(pUserLogin    in varchar2,
                          pUserDataFull out sys_refcursor) return number is
    Result     number default 0;
    vCountUser number;
    vLogin     varchar(32767);
  begin
    vLogin := UPPER(TRIM(pUserLogin));

    select count(*)
      into vCountUser
      from users ur
     where UPPER(TRIM(ur.loginname)) = vLogin;

    if vCountUser = 1 then
      open pUserDataFull for --Полные данные о пользователе
        SELECT du.userid,
               du.loginname,
               du.lastname,
               du.firstname,
               du.middlename,
               du.pers_number,
               du.pers_doctype,
               du.pers_docseries,
               du.pers_docnumber,
               du.pers_docdate,
               du.pers_docwhogive,
               du.jobname,
               du.phones,
               du.emails,
               du.isenable,
               du.begin_blockeddate,
               du.end_blockeddate,
               du.department_id,
               du.IS_DISMISSED
          FROM users du
         where UPPER(TRIM(du.loginname)) = vLogin;
    end if;

    if vCountUser <> 1 then
      Result := -1100; --//Нет такого пользователя
    end if;

    return(Result);
  end;

  function ChangePassword(pLoginName in VARCHAR2, pPassword in VARCHAR2)
    return number is
    Result number;
  begin
    Result := 0;

    --Попытка смены пароля полльзователя в Oracle
    Result := pkg_systasks.ChangePassword(pLOGINNAME, pPassword);
    if (Result <> 0) then
      return - 1112; -- Ошибка смены пароля пользователя в Oracle
    end if;
    pkg_audits.WriteUserAudit(5,
                              'ChangePassword',
                              'Логин - ' || pLoginName);
    return(Result);
  exception
    when others then
      -- pkg_exceptions.AddException(sqlcode,sqlerrm, 'pkg_users.ChangePassword');
      return - 1;
  end;

  function GetUsersRoles(pUserID    in number,
                         pRolesType in number,
                         pCur       in out sys_refcursor) return number is
    Result number;
  begin
    Result := 0;

    --//Buchihina 27/07/2009
    if pRolesType = 1 then
      -- Назначенные
      open pCur for
        SELECT ur.roleid, r.rolename, r.description
          FROM user_roles ur, roles r
         where ur.userid = pUserID
           and ur.roleid = r.roleid
           and sysdate between ur.datebegin and ur.dateend
         order by 2;

    elsif pRolesType = 0 then
      -- Доступные
      open pCur for
        SELECT r.roleid, r.rolename, r.description
          FROM roles r
         where not exists
         (select 1
                  from user_roles ur
                 where ur.roleid = r.roleid
                   and ur.userid = pUserID
                   and sysdate between ur.datebegin and ur.dateend);
    else
      Result := -1102; -- Возможны только "Назначенные" и "Доступные" роли
    end if;

    return(Result);
  end;

  function AddUserRole(pUserID    in number,
                       pRoleID    in number,
                       pDateBegin in date) return number is
    Result     number;
    vCountRole number;
    pCount     number;
  begin
    Result := 0;

    select count(*)
      into vCountRole
      from user_roles ur
     where ur.userid = pUserID
       and ur.roleid = pRoleID
       and sysdate between ur.datebegin and ur.dateend;

    if vCountRole = 0 then
      if bitand(pROLEID, 4) = 4 then
        select count(*)
          into pCount
          from users
         where userid = pUserID
           and (sertificate is null OR sertificate_name IS NULL OR
               sertificate_size = 0 OR sertificate_size IS NULL);
        IF pCount = 1 then
          return - 1104;
        end if;
      end if;
      insert into user_roles
        (USERID, ROLEID, datebegin, dateend)
      values
        (pUserID, pRoleID, sysdate, to_date('01.01.3000', 'DD.MM.YYYY'));
    else
      Result := -1103; -- Попытка добавить уже назначенную для данного пользователя роль
    end if;

    return(Result);
  end;

  function DeleteUserRole(pUserID in number, pRoleID in number) return number is
    Result     number;
    vCountRole number;
  begin
    Result := 0;

    select count(*)
      into vCountRole
      from user_roles ur
     where ur.userid = pUserID
       and ur.roleid = pRoleID
       and ur.datebegin <= sysdate
       and ur.dateend = to_date('01.01.3000', 'DD.MM.YYYY');

    if vCountRole = 1 then
      update user_roles
         set dateend = sysdate
       where USERID = pUserID
         and ROLEID = pRoleID
         and datebegin <= sysdate
         and dateend = to_date('01.01.3000', 'DD.MM.YYYY');
    else
      Result := -1104; -- Удаление не существующей для данного пользователя роли
    end if;
    return(Result);
  end;

  function GetCurrentUserId return number --ID пользователя из таблицы users по логину входа в систему
   is
    Result     number default 0;
    vLoginName varchar2(100 char);
    pragma autonomous_transaction;
  begin

    vLoginName := UPPER(TRIM(user));

    select userid
      into Result
      from users ur
     where UPPER(TRIM(ur.loginname)) = vLoginName;
    commit;
    return Result;
  exception
    when no_data_found then
      commit;
      return - 1110; -- Недопустимый пользователь?  ??????возможна ли такая ситуация - пока да
  end GetCurrentUserId;

  function CheckUserData(pUserID            in number,
                         pLOGINNAME         in VARCHAR2,
                         pLASTNAME          in VARCHAR2,
                         pFIRSTNAME        in VARCHAR2,
                         pMIDDLENAME        in VARCHAR2,
                         pPers_Number       in VARCHAR2,
                         pPers_DocType      in number,
                         pPers_DocSeries    in VARCHAR2,
                         pPers_DocNumber    in VARCHAR2,
                         pPers_DocDate      in date,
                         pPers_DocWhoGive   in VARCHAR2,
                         pJobName           in VARCHAR2,
                         pPhones            in VARCHAR2,
                         pEmails            in VARCHAR2,
                         pIsEnable          in out number,
                         pbegin_blockeddate in date,
                         pEnd_blockeddate   in date,
                         pIsDismissed       in number default 0) return number is
    Result number default 0;
  begin
    if length(TRIM(pLOGINNAME)) is null or length(TRIM(pLASTNAME)) is null or
       length(TRIM(pFIRSTNAME)) is null then
      return - 1107; --Не все обязательные поля данны хо пользователе заполнены
    end if;

    if NVL(pbegin_blockeddate, to_date('01.01.3000', 'DD.MM.YYYY')) >
       NVL(pend_blockeddate, to_date('01.01.3000', 'DD.MM.YYYY')) then
      return - 1108; -- Дата начала блокирвки больше даты конца блокировки либо не задана
    else
      if sysdate between
         NVL(pbegin_blockeddate, to_date('01.01.3000', 'DD.MM.YYYY')) and
         NVL(pend_blockeddate, to_date('01.01.3000', 'DD.MM.YYYY')) then
        pIsEnable := 0; --Заблокирован
      else
        PiSeNABLE := 1; --Не заблокирован
      end if;
    end if;
    return Result;
  end CheckUserData;

  procedure CheckIsEnabled is
    CURSOR vUserTable IS
      select ur.userid,
             ur.loginname,
             ur.lastname,
             ur.firstname,
             ur.middlename,
             ur.pers_number,
             ur.pers_doctype,
             ur.pers_docseries,
             ur.pers_docnumber,
             ur.pers_docdate,
             ur.pers_docwhogive,
             ur.jobname,
             ur.phones,
             ur.emails,
             ur.isenable,
             ur.begin_blockeddate,
             ur.end_blockeddate,
             ur.department_id
        from users ur;
    vIsEnable number;
  begin
    for vUserTableRow in vUserTable loop

      if (sysdate between
         nvl(vUserTableRow.begin_BlockedDate,
              to_date('01.01.3000', 'DD.MM.YYYY')) and
         nvl(vUserTableRow.end_BlockedDate,
              to_date('01.01.3000', 'DD.MM.YYYY'))) then
        vIsEnable := 0;
      else
        vIsEnable := 1;
      end if;

      if (vUserTableRow.IsEnable is not null) and
         nvl(vUserTableRow.IsEnable, 2) = vIsEnable then
        null;
      else

        -- Если произошли изменения
        --  pragma autonomous_transaction;
        execute immediate 'ALTER TRIGGER TR_USERSHISTORY DISABLE';
        --  commit;
        update users
           set isenable = visenable
         where USERID = vUserTableRow.UserID;

        execute immediate 'ALTER TRIGGER TR_USERSHISTORY ENABLE';

        insert into usershistory
          (userid,
           loginname,
           lastname,
           firstname,
           middlename,
           pers_number,
           pers_doctype,
           pers_docseries,
           pers_docnumber,
           pers_docdate,
           pers_docwhogive,
           jobname,
           phones,
           emails,
           isenable,
           begin_blockeddate,
           end_blockeddate,
           USERID_WHOCHANGED,
           DATE_CHANGED,
           department_id)
        values
          (vUserTableRow.UserID,
           vUserTableRow.LOGINNAME,
           vUserTableRow.LASTNAME,
           vUserTableRow.FIRSTNAME,
           vUserTableRow.MIDDLENAME,
           vUserTableRow.Pers_Number,
           vUserTableRow.Pers_DocType,
           vUserTableRow.Pers_DocSeries,
           vUserTableRow.Pers_DocNumber,
           vUserTableRow.Pers_DocDate,
           vUserTableRow.Pers_DocWhoGive,
           vUserTableRow.JobName,
           vUserTableRow.Phones,
           vUserTableRow.Emails,
           vIsEnable,
           vUserTableRow.Begin_blockeddate,
           vUserTableRow.end_blockeddate,
           -70,
           sysdate,
           vUserTableRow.department_id);

      end if;
    end loop;
  end CheckIsEnabled;

  function GetUsersRolesGroup(pRolesGroup out number) return number is
    Result number;
  begin
    Result := 0;

    SELECT nvl(sum(ur.roleid), 0)
      into pRolesGroup
      FROM user_roles ur, users u
     where ur.userid = u.userid
       and UPPER(u.loginname) = UPPER(user)
       and sysdate between ur.datebegin and ur.dateend;

    return(Result);
  end;

end PKG_Users;
/

CREATE OR REPLACE PUBLIC SYNONYM PKG_USERS FOR KFM.PKG_USERS;
GRANT EXECUTE ON KFM.PKG_USERS TO KFM_ROLE;

-- Необходимы свои транзакции, если требуются
/*
  ****** End update script ******
*/
