Процедура

licprod.sql

CREATE DEFINER = ''
PROCEDURE `license-prod-new`.iu_Licenses_WEB(INOUT _LicId INT,IN _lic_LicId INT
 ,IN _LicKindId INT,IN _LicSer VARCHAR(10),IN _LicNumb VARCHAR(100)
 ,IN _BlankNumb VARCHAR(100),IN _NumbInReestr VARCHAR(100),IN _BlankDate DATE
 ,IN _LicINN VARCHAR(9),IN _LicQrCode BIT,IN _LicPin SMALLINT(6),IN _LicensiatName_RU VARCHAR(1000),IN _LicensiatName_UZ VARCHAR(1000)
 ,IN _GuvNumb VARCHAR(100),IN _GuvNumbInReestr VARCHAR(100),IN _GuvRegDate DATE,IN _GuvOutDate DATE
 ,IN _LicOPF VARCHAR(1024),IN _OPFId INT,IN _LicPostIndex VARCHAR(10),IN _LicAddress VARCHAR(1000), IN _AddressOfActivity VARCHAR(1000)
 ,IN _LicPaspSer VARCHAR(10),IN _LicPaspNumb VARCHAR(100),IN _LicPaspOuter VARCHAR(1000)
 ,IN _LicPaspOutDate DATE,IN _LicPaspEndDate DATE,IN _LicKind TINYINT,IN _IsLicContr TINYINT
 ,IN _RegisterTypeId int,IN _LicensiarId int,IN _UserId int,IN _PersonId int, IN _OrgId int, IN _LicensiatId int
  ,IN _LSRId INT, IN _lic_LSRId INT
 ,IN _LSRBeg DATE,IN _LSREnd DATE,IN _LSRPeriod TINYINT,IN _LSRMinSal DECIMAL(8, 2)
 ,IN _LSRStateDutyInMS DECIMAL(10, 1),IN _LSRStateDuty DECIMAL(20, 2)
 ,IN _LSRStateDutyDate DATE,IN _LSRStateDutyPeriod TINYINT,IN _LSRStateId INT
 ,IN _LSRStateDate datetime,IN _LSRStateReason VARCHAR(1024),IN _LSRApplayInpDate DATE
 ,IN _LSRBankAccount VARCHAR(20),IN _BankId INT,IN _LSRBank VARCHAR(1024)
 ,IN _LSREMail VARCHAR(50),IN _LSRPhone VARCHAR(50),IN _LSRFax VARCHAR(1024)
 ,IN _LSRHead VARCHAR(60),IN _LSRRem VARCHAR(1024),IN _OperCode varchar(30), IN _StopDays int,IN _IsBegInput bit, 
  IN _LSRNumber varchar(30), IN _LSRDate DATE, IN _LSROrdNumber int, IN _LicKindText text,
  IN _LicensiatShortName_RU VARCHAR(255),IN _LicensiatShortName_UZ VARCHAR(255), IN _ID_REGION int, IN _ReqId INT, IN _TimesCount int,
  IN _IsPerionInYearLicense BIT)
BEGIN
 -- DECLARE _LicensiatId int;
  DECLARE _OldLicensiatId int;
  DECLARE _OldLicKindId int;
  DECLARE _RegisterId int;
  DECLARE _InLic_LSRId int;
  DECLARE _LicensiatNameRu varchar(100);
  DECLARE _LicensiatNameUz varchar(100);
  DECLARE _RegistrarId int;
  DECLARE _CustLsrBegDate DATE;
  DECLARE _ReformedLsrBegDate DATE;
  DECLARE _CustLsrEndDate DATE;
  DECLARE _isAdd bit;

  DECLARE _ParentLicId int;
  DECLARE _ParentLicLsrId int;
  DECLARE _ParentOperCode varchar(30);

  DECLARE _InLicId int;
  DECLARE _InLSRId int;

  DECLARE _PrevLSRBeg date;
  DECLARE _PrevLSREnd date; 
  DECLARE _PrevLSRPeriod int; 
  DECLARE _PrevLSRMinSal DECIMAL(8, 2);
  DECLARE _PrevLSRStateDutyInMS DECIMAL(10, 1);
  DECLARE _PrevLSRStateDuty DECIMAL(20, 2); 
  DECLARE _PrevLSRStateDutyDate DATE;
  DECLARE _PrevLSRStateDutyPeriod tinyint;
  DECLARE _PrevLSRStateId int; 
  DECLARE _PrevLSRBankAccount varchar(20);
  DECLARE _PrevBankId int;
  DECLARE _PrevLSRBank varchar(1024);
  DECLARE _PrevLSREMail varchar(50); 
  DECLARE _PrevLSRPhone varchar(50); 
  DECLARE _PrevLSRFax varchar(1024); 
  DECLARE _PrevLSRHead varchar(60); 
  DECLARE _PrevLSRRem varchar(1024); 
  DECLARE _PrevStopDays int; 
  DECLARE _PrevLSRNumber varchar(30); 
  DECLARE _PrevLSRDate date; 
  DECLARE _PrevLSROrdNumber int; 
  DECLARE _PrevIsBegInput bit;
  DECLARE _PrevReqId int; 
  DECLARE _PrevTimesCount int; 
  DECLARE _PrevIsPerionInYearLicense bit;
  DECLARE _PrevLSRStateDate date;
  DECLARE _PrevLSRStateReason varchar(1024);
  DECLARE _PrevLSRApplayInpDate date;  

  SET _InLicId=_LicId;
  SET _InLSRId=_LSRId;
  SET _isAdd = _LicId IS NULL;

  IF IFNULL(_LicId,0)>0 
    AND EXISTS(SELECT * FROM md_licenses ml WHERE ml.lic_LicId=_LicId) THEN
    -- SET @msg = CONCAT('Редактирование невозможно т.к. имеется лицензия порожденная от данной!');
    SET @msg = Concat('<ERROR><CODE>ERROR_EXISTS_GEN_LICENSE</CODE></ERROR>');
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = @msg;
  END IF;
  

  IF (IFNULL(_LicId,0)>0 OR (_LicId IS NULL AND IFNULL(_lic_LicId,0)>0))
    AND EXISTS(SELECT * FROM md_licstateregrad 
      WHERE (LicId=_LicId OR (IFNULL(_LicId,0)=0 AND LicId=_lic_LicId)) AND LSRId<>IFNULL(_LSRId,0) 
        AND LSRDate>_LSRDate) THEN
    -- SET @msg = CONCAT('Дата приказа не должна быть меньше дат предыдуших операций!');
    SET @msg = Concat('<ERROR><CODE>ERROR_LSR_DATE</CODE></ERROR>');
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = @msg;
  END IF;

  IF IFNULL(_LicId,0)<>0 AND IFNULL(_LSRId,0) = 0 THEN 
    IF(SELECT ml.REGER IS NULL 
        FROM md_licstateregrad ml 
        WHERE ml.LicId=_LicId 
        ORDER BY IF(ml.LSRDate IS NULL, 1, 0) DESC, ml.LSRDate DESC, ml.LSRId DESC LIMIT 1) THEN
      -- SET @msg = CONCAT('Для смены статуса лицензии необходимо подтвердить предыдущий статус!');
      SET @msg = Concat('<ERROR><CODE>ERROR_MUST_CONFIRM_PREV_STATUS</CODE></ERROR>');
      SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = @msg;
    END IF;
  END IF;

  -- Если операция продление, то дата начала следующей операции всегда на день больше конца предыдущей
  IF (_OperCode = "LONG") THEN 
    SELECT IF(ml.LSREnd IS NULL, ml.LSRBeg, DATE_ADD(ml.LSREnd, INTERVAL 1 DAY)) INTO _CustLsrBegDate 
      FROM md_licstateregrad ml 
      WHERE ml.LSRId<>IFNULL(_LSRId, 0) AND ml.LicId=_LicId
      ORDER BY IF(ml.LSRDate IS NULL, 1, 0) DESC, ml.LSRDate DESC, ml.LSRId DESC LIMIT 1;
    SET _CustLsrBegDate = IFNULL(_CustLsrBegDate, _LSRBeg);
  ELSE
    SET _CustLsrBegDate = _LSRBeg;
  END IF;

  SET _CustLsrEndDate = _LSREnd;-- IF(IFNULL(_LSRPeriod, 0)=0, _LSREnd, DATE_ADD(_CustLsrBegDate, INTERVAL _LSRPeriod YEAR));



  SELECT RegisterId INTO _RegisterId FROM md_registers 
    WHERE RegistrTypeId=_RegisterTypeId AND LicensiarId=_LicensiarId LIMIT 1;

  IF(IFNULL(_NumbInReestr, '')<>'' AND EXISTS(SELECT * FROM md_licenses ml 
              WHERE ml.RegisterId=_RegisterId AND  ml.LicKindId=_LicKindId  AND
                ml.NumbInReestr=_NumbInReestr AND ml.LicId<>IFNULL(_LicId, 0))) THEN
    SET @msg = Concat('<ERROR><CODE>ERROR_NUMB_IN_REGISTR_EXISTS</CODE></ERROR>');
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = @msg;
  END IF;

  IF IFNULL(_LicId,0)=0 THEN
    INSERT INTO md_licenses
    (LicensiatId,lic_LicId,LicKindId,LicSer,LicNumb,BlankNumb,NumbInReestr,BlankDate
     ,LicINN,LicQrCode,LicPin,LicensiatName_RU,LicensiatName_UZ,GuvNumb,GuvRegDate,GuvOutDate,LicOPF
     ,LicPostIndex,LicAddress,LicPaspSer,LicPaspNumb,LicPaspOuter,LicPaspOutDate
     ,LicPaspEndDate,LicKind,IsLicContr,RegisterId,DATE_CREATE,ID_USER_CREATE,AddressOfActivity,GuvNumbInReestr,OPFId
      ,IsBegInput,LicKindText, LicensiatShortName_RU,LicensiatShortName_UZ, ID_REGION)
    VALUES
    (_LicensiatId,_lic_LicId,_LicKindId,_LicSer,_LicNumb,_BlankNumb,_NumbInReestr,_BlankDate
     ,_LicINN,_LicQrCode,_LicPin,_LicensiatName_RU,_LicensiatName_UZ,_GuvNumb,_GuvRegDate,_GuvOutDate,_LicOPF
     ,_LicPostIndex,_LicAddress,_LicPaspSer,_LicPaspNumb,_LicPaspOuter,_LicPaspOutDate
     ,_LicPaspEndDate,_LicKind,_IsLicContr,_RegisterId,NOW(),_UserId,_AddressOfActivity,_GuvNumbInReestr,_OPFId
     ,_IsBegInput, _LicKindText, _LicensiatShortName_RU, _LicensiatShortName_UZ, _ID_REGION);
    SELECT LAST_INSERT_ID() INTO _LicId;
    UPDATE md_licenses SET RootID=_LicId WHERE LicId=_LicId;
  ELSE
    UPDATE md_licenses 
    SET
      LicensiatId = _LicensiatId
     -- ,lic_LicId = _lic_LicId
     ,LicKindId = _LicKindId
     ,LicSer = _LicSer
     ,LicNumb = _LicNumb
     ,BlankNumb = _BlankNumb
     ,NumbInReestr = _NumbInReestr
     ,BlankDate = _BlankDate
     ,LicINN = _LicINN
     ,LicQrCode = _LicQrCode
     ,LicPin = _LicPin
     ,LicensiatName_RU = _LicensiatName_RU
     ,LicensiatName_UZ = _LicensiatName_UZ
     ,GuvNumb = _GuvNumb
     ,GuvRegDate = _GuvRegDate
     ,GuvOutDate = _GuvOutDate
     ,LicOPF = _LicOPF
     ,LicPostIndex = _LicPostIndex
     ,LicAddress = _LicAddress
     ,LicPaspSer = _LicPaspSer
     ,LicPaspNumb = _LicPaspNumb
     ,LicPaspOuter = _LicPaspOuter
     ,LicPaspOutDate = _LicPaspOutDate
     ,LicPaspEndDate = _LicPaspEndDate
     ,LicKind = _LicKind
     ,IsLicContr = _IsLicContr
     ,RegisterId = _RegisterId
     ,DATE_EDIT = NOW()
     ,ID_USER_EDIT = _UserId
     ,AddressOfActivity=_AddressOfActivity
     ,GuvNumbInReestr=_GuvNumbInReestr
     ,OPFId=_OPFId,
      LicKindText=_LicKindText,
      LicensiatShortName_RU = _LicensiatShortName_RU,
      LicensiatShortName_UZ = _LicensiatShortName_UZ,
      ID_REGION=_ID_REGION
    WHERE
      LicId = _LicId;
  END IF;

  

  IF _isAdd AND IFNULL(_IsBegInput,0)=0 AND IFNULL(_lic_LicId,0)>0 AND _OperCode IN('DOUBLE', 'REFORM', 'LONG_WITH_NEW_BLANK') THEN
      
    IF(_OperCode = "REFORM") THEN
      SELECT ml.LSRBeg INTO _ReformedLsrBegDate
        FROM md_licstateregrad ml 
        WHERE ml.LSRId<>IFNULL(_LSRId, 0) AND ml.LicId=_lic_LicId
      ORDER BY IF(ml.LSRDate IS NULL, 1, 0) DESC, ml.LSRDate DESC, ml.LSRId DESC LIMIT 1;
    END IF;

      SELECT  ml.LSRBeg, ml.LSREnd, ml.LSRPeriod, ml.LSRMinSal, ml.LSRStateDutyInMS, ml.LSRStateDuty, 
              ml.LSRStateDutyDate, ml.LSRStateDutyPeriod, ml.LSRStateId,  ml.LSRStateDate, ml.LSRStateReason, ml.LSRApplayInpDate, 
              ml.LSRBankAccount, ml.BankId, ml.LSRBank, ml.LSREMail, ml.LSRPhone, ml.LSRFax, ml.LSRHead, ml.LSRRem, ml.StopDays, ml.LSRNumber, 
              ml.LSRDate, ml.LSROrdNumber, ml.IsBegInput, ml.ReqId, ml.TimesCount, ml.IsPerionInYearLicense
        INTO _PrevLSRBeg, _PrevLSREnd, _PrevLSRPeriod, _PrevLSRMinSal, _PrevLSRStateDutyInMS, _PrevLSRStateDuty, 
            _PrevLSRStateDutyDate, _PrevLSRStateDutyPeriod, _PrevLSRStateId, _PrevLSRStateDate , _PrevLSRStateReason ,
             _PrevLSRApplayInpDate, _PrevLSRBankAccount, _PrevBankId,
            _PrevLSRBank, _PrevLSREMail, _PrevLSRPhone, _PrevLSRFax, _PrevLSRHead, _PrevLSRRem, _PrevStopDays, _PrevLSRNumber, 
            _PrevLSRDate, _PrevLSROrdNumber, _PrevIsBegInput, _PrevReqId, _PrevTimesCount, _PrevIsPerionInYearLicense
        FROM md_licstateregrad ml 
        WHERE ml.LSRId<>IFNULL(_LSRId, 0) AND ml.LicId=_lic_LicId
      ORDER BY IF(ml.LSRDate IS NULL, 1, 0) DESC, ml.LSRDate DESC, ml.LSRId DESC LIMIT 1;

    CALL iu_LicStateRegrad_WEB(_InLSRId, NULL,_lic_LicId
       ,IF(_OperCode = "REFORM" AND _ReformedLsrBegDate<>_CustLsrBegDate,_ReformedLsrBegDate,_PrevLSRBeg)
       ,IF(_OperCode = "REFORM" AND _ReformedLsrBegDate<>_CustLsrBegDate, _CustLsrBegDate, _PrevLSREnd) 
--        ,_PrevLSRBeg,_PrevLSREnd
       ,_PrevLSRPeriod , _PrevLSRMinSal 
       ,_PrevLSRStateDutyInMS , _PrevLSRStateDuty 
       ,_PrevLSRStateDutyDate , _PrevLSRStateDutyPeriod , _PrevLSRStateId 
       ,_PrevLSRStateDate , _PrevLSRStateReason , _PrevLSRApplayInpDate 
       ,_PrevLSRBankAccount , _PrevBankId , _PrevLSRBank 
       ,_PrevLSREMail, _PrevLSRPhone , _PrevLSRFax 
       ,_PrevLSRHead , _PrevLSRRem, _UserId , 
      CASE _OperCode WHEN 'DOUBLE' THEN 'DOUBLED' 
      WHEN 'REFORM' THEN 'REFORMED' 
      WHEN 'LONG_WITH_NEW_BLANK' THEN 'LONGED_WITH_NEW_BLANK' END,_PrevStopDays, 
        _PrevLSRNumber, _LSRDate,-- _PrevLSRDate,
        _PrevLSROrdNumber, _PrevIsBegInput, _PrevReqId, _PrevTimesCount, _PrevIsPerionInYearLicense);

    CALL iu_LicStateRegrad_WEB(@id,_InLSRId,_LicId
           ,_CustLsrBegDate , _CustLsrEndDate , _LSRPeriod , _LSRMinSal 
           ,_LSRStateDutyInMS , _LSRStateDuty 
           ,_LSRStateDutyDate , _LSRStateDutyPeriod , _LSRStateId 
           ,_LSRStateDate , _LSRStateReason , _LSRApplayInpDate 
           ,_LSRBankAccount , _BankId , _LSRBank 
           ,_LSREMail, _LSRPhone , _LSRFax 
           ,_LSRHead , _LSRRem, _UserId , _OperCode,_StopDays, 
            _LSRNumber, _LSRDate, _LSROrdNumber, _IsBegInput, _ReqId, _TimesCount, _IsPerionInYearLicense);
  ELSE
    CALL iu_LicStateRegrad_WEB(_InLSRId,_lic_LSRId,_LicId
           ,_CustLsrBegDate , _CustLsrEndDate , _LSRPeriod , _LSRMinSal 
           ,_LSRStateDutyInMS , _LSRStateDuty 
           ,_LSRStateDutyDate , _LSRStateDutyPeriod , _LSRStateId 
           ,_LSRStateDate , _LSRStateReason , _LSRApplayInpDate 
           ,_LSRBankAccount , _BankId , _LSRBank 
           ,_LSREMail, _LSRPhone , _LSRFax 
           ,_LSRHead , _LSRRem, _UserId , _OperCode,_StopDays, 
            _LSRNumber, _LSRDate, _LSROrdNumber, _IsBegInput, _ReqId, _TimesCount, _IsPerionInYearLicense);

    IF(_lic_LSRId is NOT NULL) THEN
        SELECT ml.LicId, ml.lic_LSRId, ml.OperCode, ml.LSRBeg INTO _ParentLicId, _ParentLicLsrId, _ParentOperCode , _ReformedLsrBegDate
          FROM md_licstateregrad ml WHERE ml.LSRId=_lic_LSRId;

        SELECT  ml.LSRBeg, ml.LSREnd, ml.LSRPeriod, ml.LSRMinSal, ml.LSRStateDutyInMS, ml.LSRStateDuty, 
              ml.LSRStateDutyDate, ml.LSRStateDutyPeriod, ml.LSRStateId,  ml.LSRStateDate, ml.LSRStateReason, ml.LSRApplayInpDate, 
              ml.LSRBankAccount, ml.BankId, ml.LSRBank, ml.LSREMail, ml.LSRPhone, ml.LSRFax, ml.LSRHead, ml.LSRRem, ml.StopDays, ml.LSRNumber, 
              ml.LSRDate, ml.LSROrdNumber, ml.IsBegInput, ml.ReqId, ml.TimesCount, ml.IsPerionInYearLicense
        INTO _PrevLSRBeg, _PrevLSREnd, _PrevLSRPeriod, _PrevLSRMinSal, _PrevLSRStateDutyInMS, _PrevLSRStateDuty, 
            _PrevLSRStateDutyDate, _PrevLSRStateDutyPeriod, _PrevLSRStateId, _PrevLSRStateDate , _PrevLSRStateReason ,
             _PrevLSRApplayInpDate, _PrevLSRBankAccount, _PrevBankId,
            _PrevLSRBank, _PrevLSREMail, _PrevLSRPhone, _PrevLSRFax, _PrevLSRHead, _PrevLSRRem, _PrevStopDays, _PrevLSRNumber, 
            _PrevLSRDate, _PrevLSROrdNumber, _PrevIsBegInput, _PrevReqId, _PrevTimesCount, _PrevIsPerionInYearLicense
        FROM md_licstateregrad ml WHERE ml.LSRId=_lic_LSRId;

        CALL iu_LicStateRegrad_WEB(_lic_LSRId,_ParentLicLsrId,_ParentLicId
               ,IF(_OperCode = "REFORM" AND _ReformedLsrBegDate<>_CustLsrBegDate,_ReformedLsrBegDate,_PrevLSRBeg) 
               ,IF(_OperCode = "REFORM" AND _ReformedLsrBegDate<>_CustLsrBegDate, _CustLsrBegDate, _PrevLSREnd)
--                ,_PrevLSRBeg,_PrevLSREnd
               ,_LSRPeriod , _LSRMinSal 
--                ,_PrevLSRPeriod , _PrevLSRMinSal 
               ,_PrevLSRStateDutyInMS , _PrevLSRStateDuty 
               ,_PrevLSRStateDutyDate , _PrevLSRStateDutyPeriod , _PrevLSRStateId 
               ,_PrevLSRStateDate , _PrevLSRStateReason , _PrevLSRApplayInpDate 
               ,_PrevLSRBankAccount , _PrevBankId , _PrevLSRBank 
               ,_PrevLSREMail, _PrevLSRPhone , _PrevLSRFax 
               ,_PrevLSRHead , _PrevLSRRem, _UserId , _ParentOperCode, _PrevStopDays, 
              _PrevLSRNumber, _PrevLSRDate,
              _PrevLSROrdNumber, _PrevIsBegInput, _PrevReqId, 
              _PrevTimesCount, _PrevIsPerionInYearLicense);
    END IF;
  END IF;
  CALL MarkNotCorrectLicense(_LicId);
END