-- Function: uaction.gf_func_datamatrix_info(character varying)

-- DROP FUNCTION uaction.gf_func_datamatrix_info(character varying);

CREATE OR REPLACE FUNCTION uaction.gf_func_datamatrix_info(IN buf character varying, OUT line character, OUT gtin numeric, OUT serialnum character varying, OUT mrc numeric, OUT testcode character varying, OUT kassa character, OUT line_full character, OUT codedate date, OUT codedatatime timestamp without time zone)
  RETURNS record AS
$BODY$
DECLARE
  lang integer; -- 0 - английский, 1 - русский
  lang_conf text;
  line_tmp character varying; 
  line_full_tmp character varying; 

  sbarcode text; --штрих-код
  iparam integer; --тип штрих-кода

  i integer;
  
  lineEN character varying;
  lineEN_mrc character varying;
  lineRU character varying;
BEGIN
  --1) line - строка на английсчкой раскладке(урезаная)
  --2) gtin - штрих-код
  --3) serialnum - серийный номер
  --4) mrc - МРЦ
  --5) testcode - проверочный код
  --6) kassa - строка для кассы
  --7) line_full - строка на английсчкой раскладке
  --8) codedate - дата окончания срока годности
  --9) codedatatime - дата время окончания срока годности
  
  lang_conf = sf_get_one_local_conf(165);
  
  lineEN_mrc = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789!"%&''*+-./_,:;=<>?';
  
  lineEN = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789!"%&''*+-./_,:;=<>?()]';
  lineRU = 'ФИСВУАПРШОЛДЬТЩЗЙКЫЕГМЦЧНЯфисвуапршолдьтщзйкыегмцчня0123456789!Э%?э*+-ю._бЖж=БЮ,()]';

  IF length(buf)=0 THEN
    RETURN;
  END IF;

  IF left(buf,1)='"' THEN
    lang = 1;
    line = SUBSTRING(buf , 2);
  ELSIF left(buf,1)='@' THEN
    lang = 0;
    line = SUBSTRING(buf , 2);
  ELSE
    lang = 0;
    line = buf;
  END IF;

  IF LENGTH(line)=29 THEN
    gtin = TO_NUMBER(substring(line, 1, 14), '99999999999999');
    sbarcode = repeat('0',13-length(gtin::text))||gtin::text;
  ELSE 
    gtin = TO_NUMBER(substring(line, 3, 14), '99999999999999');
    sbarcode = repeat('0',13-length(gtin::text))||gtin::text;
  END IF;

   --Казахстан табак
  IF lang_conf='1' THEN
    IF lang = 1 THEN
      line_tmp = '';
      i=1;

      WHILE i<=LENGTH(line) LOOP
        line_tmp = line_tmp || substring( lineEN, strpos(lineRU, substring(line, i, 1)) ,1);
        i=i+1;
      END LOOP;
    
      line = line_tmp;
    END IF;
  
    serialnum = substring(line, 15, 7);
    mrc = NULL;
    testcode = substring(line, 22, 8);
    kassa = line;
    codedate = NULL;
    codedatatime = NULL;
    
    RETURN;
  END IF;
  
  --Узбекистан табак
  IF lang_conf='2' THEN
    IF lang = 1 THEN
      line_tmp = '';
      i=1;

      WHILE i<=LENGTH(line) LOOP
        line_tmp = line_tmp || substring( lineEN, strpos(lineRU, substring(line, i, 1)) ,1);
        i=i+1;
      END LOOP;
    
      line = line_tmp;
    END IF;
    
    serialnum = NULL;
    mrc = NULL;
    testcode = NULL;
    IF LENGTH(line)=29 THEN
      kassa = substring(line, 1, 21);
    ELSIF LENGTH(line)=37 THEN
      kassa = substring(line, 1, 31);
    ELSE
      RAISE EXCEPTION 'Отсканированный штрих-код не поддерживается.';
    END IF;
    codedate = NULL;
    codedatatime = NULL;
    
    RETURN;
  END IF;
  
  SELECT INTO iparam param FROM gd_identification WHERE id=sbarcode LIMIT 1;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'Отсканированный штрих-код не внесен в справочник.';
  END IF;

  IF iparam=0 THEN
    line = sbarcode;
    serialnum = NULL;
    mrc = NULL;
    testcode = NULL;
    kassa = line;
    line_full = NULL;
    codedate = NULL;
    codedatatime = NULL;
    
    RETURN;
  END IF;

  --Табак
  IF iparam=1 THEN
    IF LENGTH(line)!=29 THEN
      RAISE EXCEPTION 'Ошибка длины DataMatrix кода';
    END IF;

    IF lang = 0 THEN
      IF length(translate(line,lineEN,''))!=0 THEN
        RAISE EXCEPTION 'Ошибка символа в DataMatrix коде';
      END IF;
    ELSE
      IF length(translate(line,lineRU,''))!=0 THEN
        RAISE EXCEPTION 'Ошибка символа в DataMatrix коде';
      END IF;
      line_tmp = '';
      i=1;

      WHILE i<=LENGTH(line) LOOP
        line_tmp = line_tmp || substring( lineEN, strpos(lineRU, substring(line, i, 1)) ,1);
        i=i+1;
      END LOOP;
    
      line = line_tmp;
    END IF;

    serialnum = substring(line, 15, 7);
    mrc = ((strpos( lineEN_mrc, substring(line, 22, 1))-1)*80*80*80
          + (strpos( lineEN_mrc, substring(line, 23, 1))-1)*80*80
          + (strpos( lineEN_mrc, substring(line, 24, 1))-1)*80
          + (strpos( lineEN_mrc, substring(line, 25, 1))-1))/100;
    testcode = substring(line, 26, 4);

    IF sf_get_one_local_conf(156)!='3' THEN
      kassa = '8A040F000005'||lpad(to_hex(gtin::bigint),12,'0');
      i=1;
      WHILE i<=LENGTH(serialnum) LOOP
        kassa = kassa || lpad(to_hex(ascii(SUBSTRING(serialnum,i,1))),2,'0');
        i=i+1;
      END LOOP;
    ELSE
      kassa='8A041D000005';
      i=1;
      WHILE i<=LENGTH(line) LOOP
        kassa = kassa || lpad(to_hex(ascii(SUBSTRING(line,i,1))),2,'0');
        i=i+1;
      END LOOP;
    END IF;
    line_full = line;
    codedate = NULL;
    codedatatime = NULL;
  
    RETURN;
  END IF;

  --Шины
  IF iparam=2 THEN
    line_full = line;
    line = substring(line,1,31);
    
    IF LENGTH(line)<31 THEN
      RAISE EXCEPTION 'Ошибка длины DataMatrix кода';
    END IF;

    IF lang = 0 THEN
      IF length(translate(line_full,lineEN,''))!=0 THEN
        RAISE EXCEPTION 'Ошибка символа в DataMatrix коде';
      END IF;
    ELSE
      IF length(translate(line_full,lineRU,''))!=0 THEN
        RAISE EXCEPTION 'Ошибка символа в DataMatrix коде';
      END IF;
      line_tmp = '';
      line_full_tmp = '';
      
      i=1;
      WHILE i<=LENGTH(line) LOOP
        line_tmp = line_tmp || substring( lineEN, strpos(lineRU, substring(line, i, 1)) ,1);
        i=i+1;
      END LOOP;
      
      i=1;
      WHILE i<=LENGTH(line_full) LOOP
        line_full_tmp = line_full_tmp || substring( lineEN, strpos(lineRU, substring(line_full, i, 1)) ,1);
        i=i+1;
      END LOOP;
    
      line = line_tmp;
      line_full = line_full_tmp;
    END IF;

    serialnum = substring(line, 19, 13);
    mrc = NULL;
    testcode = substring(line, 28, 4); 

    IF sf_get_one_local_conf(191)!='3' THEN
      kassa = '8A041500444D'||lpad(to_hex(gtin::bigint),12,'0');
      --kassa = '8A041500444D';--||lpad(gtin::text,12,'0');
      i=1;
      WHILE i<=LENGTH(serialnum) LOOP
        kassa = kassa || lpad(to_hex(ascii(SUBSTRING(serialnum,i,1))),2,'0');
      --WHILE i<=LENGTH( substring(line, 1, 27)) LOOP
      --  kassa = kassa || lpad(to_hex(ascii(SUBSTRING( substring(line, 1, 27),i,1))),2,'0');
        i=i+1;
      END LOOP;
    ELSE
      kassa='8A044B00444D';
      i=1;
      WHILE i<=LENGTH(line) LOOP
        kassa = kassa || lpad(to_hex(ascii(SUBSTRING(line,i,1))),2,'0');
        i=i+1;
      END LOOP;
    END IF;
    codedate = NULL;
    codedatatime = NULL;
 
    RETURN;
  END IF;

  --Молочка и вода
  IF iparam=3 THEN
    IF LENGTH(line) NOT IN (30, 31, 32, 36, 37, 38, 44, 45, 51) THEN
      RAISE EXCEPTION 'Ошибка длины DataMatrix кода';
    END IF;
  
    IF lang = 0 THEN
      IF length(translate(line,lineEN,''))!=0 THEN
        RAISE EXCEPTION 'Ошибка символа в DataMatrix коде';
      END IF;
    ELSE
      IF length(translate(line,lineRU,''))!=0 THEN
        RAISE EXCEPTION 'Ошибка символа в DataMatrix коде';
      END IF;
      line_tmp = '';
      i=1;

      WHILE i<=LENGTH(line) LOOP
        line_tmp = line_tmp || substring( lineEN, strpos(lineRU, substring(line, i, 1)) ,1);
        i=i+1;
      END LOOP;
    
      line = line_tmp;
    END IF;
    
    line_full = line;
    kassa = line;
    serialnum = NULL;
    mrc = NULL;
    testcode = NULL; 
    codedate = NULL;
    codedatatime = NULL;
    
    IF length(line_full)=30 THEN
      serialnum = substring(line_full, 19, 6);
      mrc = NULL;
      testcode = substring(line_full, 27, 4); 
      codedate = NULL;
      codedatatime = NULL;
    END IF;
    
    --Безалкогольное пиво
    IF length(line_full)=31 THEN
      serialnum = substring(line_full, 19, 7);
      mrc = NULL;
      testcode = substring(line_full, 28, 4); 
      codedate = NULL;
      codedatatime = NULL;
    END IF;
    
    --Белорусское мороженое
    IF length(line_full)=32 THEN
      serialnum = substring(line_full, 19, 8);
      mrc = NULL;
      testcode = substring(line_full, 29, 4); 
      codedate = NULL;
      codedatatime = NULL;
    END IF;
    
    IF length(line_full)=36 THEN
      serialnum = substring(line_full, 19, 12);
      mrc = NULL;
      testcode = substring(line_full, 33, 4); 
      codedate = NULL;
      codedatatime = NULL;
    END IF;
    
    IF length(line_full)=37 THEN
      serialnum = substring(line_full, 19, 13);
      mrc = NULL;
      testcode = substring(line_full, 34, 4); 
      codedate = NULL;
      codedatatime = NULL;
    END IF;

    IF length(line_full)=38 THEN
      serialnum = substring(line_full, 19, 6);
      mrc = NULL;
      testcode = substring(line_full, 36, 4); 
      codedate = TO_DATE(substring(line_full, 27, 6),'YYMMDD');
      codedatatime = NULL;
    END IF;
    
    IF length(line_full)=45 THEN
      serialnum = substring(line_full, 19, 13);
      mrc = NULL;
      testcode = substring(line_full, 43, 4); 
      codedate = TO_DATE(substring(line_full, 34, 6),'YYMMDD');
      codedatatime = NULL;
    END IF;
    
    IF length(line_full)=44 THEN
      serialnum = substring(line_full, 19, 6);
      mrc = NULL;
      testcode = substring(line_full, 42, 4); 
      codedate = NULL;
      codedatatime = TO_TIMESTAMP(substring(line_full, 29, 10),'YYMMDDHH24MI')::timestamp without time zone;
    END IF;
 
    IF length(line_full)=51 THEN
      serialnum = substring(line_full, 19, 13);
      mrc = NULL;
      testcode = substring(line_full, 49, 4); 
      codedate = NULL;
      codedatatime = TO_TIMESTAMP(substring(line_full, 36, 10),'YYMMDDHH24MI')::timestamp without time zone;
    END IF;
    
    RETURN;
  END IF;
  
  RETURN;
END;
$BODY$
  LANGUAGE plpgsql;
GRANT EXECUTE ON FUNCTION uaction.gf_func_datamatrix_info(character varying) TO public;
COMMENT ON FUNCTION uaction.gf_func_datamatrix_info(character varying) IS 'v6.5.2 Получение информации из DataMatrix
  Входные данные(строка DataMatrix)
  Выходные данные - Штрих-код(GTIN), Серийный номер, МРЦ, Код проверки, строка для кассы, дата срока годности, дата/время срока годности';
