-- Generation Time: Nov 6, 2007
-- Last update: Nov 6, 2007
-- Database: `flib`
--
-- BY JULIO RAFFO (EPFL/CEMI)
--
-- version: 1.0
--
-- ----------------------------------------------------------------------------------------------
-- compareipc(IPC1, IPC2) returns-> 0 to 6 result
-- Compares ipc1 and ipc2 at section, class, subclass, group and subgroup returning respectevely
-- 1,2,3,4 and 5-6 values. 5 is returned when there is match at 2 digits subgroup level, but not
-- at higher one, 6 is retuned when subgroup is matched at full extent. Notice that if IPC1 and
-- IPC2 match at 3 but not at 4 subgroup digits then a two digit level result (5) is returned.
-- This is due to the fact that hyerchy is not straight forward in IPC coding. In current version
-- subgroup 00 is considered as any other 2 digit subgroup. Zero is returned if there no match at
-- all.
-- ----------------------------------------------------------------------------------------------

DELIMITER $$

DROP FUNCTION IF EXISTS flib.compareipc $$
CREATE FUNCTION  flib.compareipc(ipc1 VARCHAR(20), ipc2 VARCHAR(20)) RETURNS int
BEGIN

declare ipclevel int;
declare ipclength1 int;
declare ipclength2 int;
declare ipclimit int;

set ipclength1=CHAR_LENGTH(ipc1);
set ipclength2=CHAR_LENGTH(ipc2);

-- sets ipclimit
if ipclength1>ipclength2 then
  set ipclimit=ipclength2;
else
  set ipclimit=ipclength1;
end if;

-- comparison

-- compares at IPC section
  if (ipclimit>0 and left(ipc1,1)=left(ipc2,1)) then

  -- compares at IPC class
    if (ipclimit>2 and left(ipc1,3)=left(ipc2,3)) then

    -- compares at IPC subclass
      if (ipclimit>3 and left(ipc1,4)=left(ipc2,4)) then

      -- compares at IPC group
        if (ipclimit>7 and left(ipc1,8)=left(ipc2,8)) then

        -- compares at IPC subgroup
          if (ipclimit>10 and left(ipc1,11)=left(ipc2,11)) then

          -- compares at IPC subgroup full extent
            if (ipclimit>11 and left(ipc1,13)=left(ipc2,13)) then RETURN 6;

            else RETURN 5;
            end if;

          else RETURN 4;
          end if;

        else RETURN 3;
        end if;

      else RETURN 2;
      end if;

    else RETURN 1;
    end if;

  else RETURN 0;
  end if;

END;

 $$

DELIMITER ;