Zeoslib Portal

Code: Select all

--
-- Structure for procedure `AddCreditToCard`
--

DROP PROCEDURE IF EXISTS [TARGET_DB].`AddCreditToCard` $$

CREATE DEFINER=`root`@`%` PROCEDURE [TARGET_DB].`AddCreditToCard`(CARD Int, ADD_CREDIT float)
    COMMENT 'Procedura za dodajanje kredita na kartice'
BEGIN
  Update CARD_ACTIVITY Set CREDIT=CREDIT+ADD_CREDIT Where CARD_ID=CARD;
  # Logiranje za zgodovino - spremembe na stanju kartice
  REPLACE INTO CARD_HISTORY Values (
    NULL,
    CARD,
    NOW(),
    ADD_CREDIT
  );
END $$

--
-- Structure for procedure `UseCardCredit`
--

DROP PROCEDURE IF EXISTS [TARGET_DB].`UseCardCredit` $$

CREATE DEFINER=`root`@`%` PROCEDURE [TARGET_DB].`UseCardCredit`(IN_BOX_ID int, IN_SOCKET_ID Int, IN_CARD_ID Int, IN_OLD_COUNTER BigInt, IN_NEW_COUNTER Bigint)
    COMMENT 'Poraba kredita na kartici glede na spremembe stevcov prikljucka'
BEGIN
  DECLARE Val FLOAT;
  DECLARE SocketType INT;

  If IN_NEW_COUNTER IS NULL THEN
    SET IN_NEW_COUNTER = 0;
  END IF;

  If IN_NEW_COUNTER IS NULL THEN
    SET IN_NEW_COUNTER = 0;
  END IF;

  Call WriteLog(1, Concat('(UseCardCredit) IN_NEW_COUNTER: ', IN_NEW_COUNTER, ', IN_OLD_COUNTER: ', IN_OLD_COUNTER));

  IF IN_NEW_COUNTER-IN_OLD_COUNTER > 0 THEN
    SET @Val = GetMoneyFromUsage(IN_BOX_ID, IN_SOCKET_ID, IN_NEW_COUNTER-IN_OLD_COUNTER);
    Call WriteLog(1, Concat('(UseCardCredit) Val= ', @Val));

    IF @Val > 0 THEN
      Call WriteLog(1, Concat('(UseCardCredit) Sprememba cene: ', @Val));
      # Izracun odbitka cene
      UPDATE CARD_ACTIVITY Set CREDIT = CREDIT - @Val Where CARD_ID=IN_CARD_ID;
      # Logiranje za zgodovino - spremembe na stanju kartice
      REPLACE INTO CARD_HISTORY Values (
        NULL,
        IN_CARD_ID,
        NOW(),
        @Val
      );
    END IF;

    Call WriteLog(1, Concat('(UseCardCredit) Sprememba stevca: ', IN_NEW_COUNTER-IN_OLD_COUNTER));
    # Logiranje za zgodovino - spremembe na stanju stevcev
    REPLACE INTO SOCKET_HISTORY Values (
      NULL,
      IN_BOX_ID,
      IN_SOCKET_ID,
      IN_CARD_ID,
      NOW(),
      IN_OLD_COUNTER,
      IN_NEW_COUNTER
    );
  END IF;
END $$

--
-- Structure for procedure `WriteLog`
--

DROP PROCEDURE IF EXISTS [TARGET_DB].`WriteLog` $$

CREATE DEFINER=`root`@`%` PROCEDURE [TARGET_DB].`WriteLog`(MsgLevel int, MsgTxt varchar(255))
BEGIN
  Insert Into LOG Values (NULL, NOW(), MsgLevel, MsgTxt, 0);
  if MsgTxt = "CLEAR" THEN
    Delete From LOG;
  End If;
END $$

--
-- Structure for function `GetMoneyFromUsage`
--

DROP FUNCTION IF EXISTS [TARGET_DB].`GetMoneyFromUsage` $$

CREATE DEFINER=`root`@`%` FUNCTION [TARGET_DB].`GetMoneyFromUsage`(BoxID int, SocketID int, UsageDelta float) RETURNS float
BEGIN
  Declare NoDefinedType int;

  Call WriteLog(1, Concat('(GetMoneyFromUsage) BoxID: ', BoxID, ', SocketID: ', SocketID,', UsageDelta: ', UsageDelta));

  Select IfNull(SOCKET_TYPE.ID,-1) As HasType From SOCKETS Left Outer Join SOCKET_TYPE On TYPE_ID=SOCKET_TYPE.ID
  Where SOCKETS.BOX_ID=BoxID And SOCKETS.SOCKET_ID=SocketID Into @NoDefinedType;

  IF @NoDefinedType = -1 THEN
    call WriteLog(4, Concat('(GetMoneyFromUsage) Socket ', BoxID,'-',SocketID,' (BOX_ID-SOCKET_ID) has no defined type! No Money calculation will be done!'));
  END IF;

  RETURN
  (
  Select If(PRICE Is NULL, 0, PRICE) * UsageDelta As PRICE
  From SOCKETS Left Outer Join SOCKET_TYPE On TYPE_ID=SOCKET_TYPE.ID
               Left Outer Join PRICELIST On SOURCE_TYPE=PRICELIST.SOURCE
  Where SOCKETS.BOX_ID=BoxID And SOCKETS.SOCKET_ID=SocketID
  );
END $$

--
-- Structure for function `GetSocketID`
--

DROP FUNCTION IF EXISTS [TARGET_DB].`GetSocketID`$$

CREATE DEFINER=`root`@`%` FUNCTION [TARGET_DB].`GetSocketID`(IN_BOX_ID Int, IN_DISPLAY_ID Int, IN_BOX_GROUP char(3)) RETURNS int(11)
BEGIN
  RETURN
  (
  Select SOCKET_ID
  From VIEW_SOCKETS
  Where IN_BOX_ID=BOX_ID And IN_DISPLAY_ID=DISPLAY And IN_BOX_GROUP=GROUP_FROM_BOX
  );
END $$

DROP TRIGGER IF EXISTS [TARGET_DB].CalcCena $$

CREATE TRIGGER [TARGET_DB].CalcCena BEFORE UPDATE ON [TARGET_DB].socket_activity
FOR EACH ROW
BEGIN
  DECLARE Val FLOAT;
  DECLARE SocketType INT;

  IF NEW.COUNTER-OLD.COUNTER > 0 THEN

    SET @Val = GetMoneyFromUsage(NEW.BOX_ID, NEW.SOCKET_ID, NEW.COUNTER-OLD.COUNTER);

    IF @Val > 0 THEN
#      Call WriteLog(Concat('Sprememba cene: ', @Val));
      # Izracun odbitka cene
      UPDATE CARD_ACTIVITY Set CREDIT = CREDIT - @Val Where CARD_ID=NEW.CARD_BINDED;
      # Logiranje za zgodovino - spremembe na stanju kartice
      REPLACE INTO CARD_HISTORY Values (
        NULL,
        NEW.CARD_BINDED,
        NOW(),
        @Val
      );
    END IF;

#    Call WriteLog(Concat('Sprememba stevca: ', NEW.COUNTER-OLD.COUNTER));
    # Logiranje za zgodovino - spremembe na stanju stevcev
    REPLACE INTO SOCKET_HISTORY Values (
      NULL,
      NEW.BOX_ID,
      NEW.SOCKET_ID,
      NEW.CARD_BINDED,
      NOW(),
      OLD.COUNTER,
      NEW.COUNTER
    );
#    Call WriteLog('Sprememba stanja box-a na ACTIVE (3)');
    # Sprememba stanja box-a na ACTIVE (3)
    UPDATE SOCKET_ACTIVITY Set STATUS=3 Where BOX_ID=NEW.BOX_ID And SOCKET_ID=NEW.SOCKET_ID;
  END IF;
END $$