-- 品目構成テーブル 変更
DROP FUNCTION f_item_struct_update(IN kubun integer, IN itemcode integer, IN structcode integer, IN variationnm character varying, IN makeritemcode character varying, IN jancode character varying, IN saledate date, IN orderdate date, IN teika integer, IN coment text, IN _option01 character varying, IN _option02 character varying, IN _option03 character varying, IN _option04 character varying, IN _option05 character varying, IN _option06 character varying, IN _option07 character varying, IN _option08 character varying, IN _option09 character varying, IN _option10 character varying, IN usr character varying, IN ipaddr character varying, IN count integer, OUT errcd integer, OUT errmsg character varying, OUT code integer);

CREATE OR REPLACE FUNCTION f_item_struct_update(
  IN kubun integer,
  IN itemcode integer,
  IN structcode integer,
  IN variationnm character varying,
  IN makeritemcode character varying,
  IN jancode character varying,
  IN saledate date,
  IN orderdate date,
  IN teika integer,
  IN coment text,
  IN _option01 character varying,
  IN _option02 character varying,
  IN _option03 character varying,
  IN _option04 character varying,
  IN _option05 character varying,
  IN _option06 character varying,
  IN _option07 character varying,
  IN _option08 character varying,
  IN _option09 character varying,
  IN _option10 character varying,
  IN usr character varying,
  IN ipaddr character varying,
  IN count integer,
  OUT errcd integer,
  OUT errmsg character varying,
  OUT code integer) AS
$BODY$

DECLARE
    sql1 varchar;
    sql2 varchar;
    cntcheck integer;
    makerc integer;
    labelc integer;
    MES_001 varchar := '品目コード' || itemcode || 'は登録済みです';
    MES_002 varchar := '品名が指定されていません';
BEGIN



    -- 必須チェック --
--SELECT itemcd INTO cntcheck FROM t_item_basic_attr WHERE itemcd=itemcode AND structcd = structcode;

    -- 処理区分
    IF kubun IS NULL OR kubun NOT IN (1,2,3) THEN
        RAISE EXCEPTION '処理区分が不正です';
    END IF;
    IF kubun IN (2,3) THEN
        IF structcode IS NULL THEN
            RAISE EXCEPTION '構成コードの引数は必須です';
        END IF;
        IF count IS NULL THEN
            RAISE EXCEPTION '更新カウンタの引数は必須です';
        END IF;
    END IF;
    IF kubun IN (1,2,3) THEN
        IF itemcode IS NULL THEN
            RAISE EXCEPTION '品目コードの引数は必須です';
        END IF;
        IF usr IS NULL THEN
            RAISE EXCEPTION '更新ユーザが指定されていません';
        END IF;
    END IF;

    -- 更新マシン(IPアドレス)のチェックはとりあえずしない

    -- 存在チェック
    IF kubun IN (2,3) THEN
        SELECT itemcd INTO cntcheck FROM t_item_struct WHERE itemcd = itemcode AND structcd = structcode;
          IF NOT FOUND THEN
            RAISE EXCEPTION 'その品目コードと構成コードのレコードは存在しません';
        END IF;
    END IF;


    -- 更新カウンタチェック
    IF kubun IN (2,3) THEN
        SELECT updcnt INTO cntcheck FROM t_item_struct WHERE itemcd = itemcode AND structcd = structcode;
          IF NOT FOUND OR cntcheck <> count THEN
            errcd := -11;
            errmsg := '更新カウンタが不正';
            RETURN;
          END IF;
    END IF;





 ---更新処理 ---


    IF kubun = 1 THEN
        -- 追加
        sql1 := 'INSERT INTO t_item_struct(itemcd';
        sql2 := 'VALUES(' || itemcode ;

        -- バリエーション名
        IF variationnm IS NOT NULL THEN
            sql1 := sql1 || ', variationnm';
            sql2 := sql2 || ', \'' || variationnm || '\'';
        END IF;

        -- メーカー品番
        IF makeritemcode IS NOT NULL THEN
            sql1 := sql1 || ', makeritemcd';
            sql2 := sql2 || ', s_get_nocase_text_lower(\'' || makeritemcode || '\')';
        END IF;

        -- JANコード
        IF jancode IS NOT NULL THEN
            sql1 := sql1 || ', jancd';
            sql2 := sql2 || ', \'' || jancode || '\'';
        END IF;

        -- 発売日
        IF saledate IS NOT NULL THEN
            sql1 := sql1 || ', salestartdate';
            sql2 := sql2 || ', \'' || saledate || '\'';
        END IF;

        -- 発注締切日
        IF orderdate IS NOT NULL THEN
            sql1 := sql1 || ', orderclosedate';
            sql2 := sql2 || ', \'' || orderdate || '\'';
        END IF;

        -- メーカー定価
        IF teika IS NOT NULL THEN
            sql1 := sql1 || ', makerprice';
            sql2 := sql2 || ', ' || teika ;
        END IF;

        -- コメント
        IF coment IS NOT NULL THEN
            sql1 := sql1 || ', comment';
            sql2 := sql2 || ', \'' || coment || '\'';
        END IF;

        -- オプション情報
        IF _option01 IS NOT NULL THEN
            sql1 := sql1 || ', option01';
            sql2 := sql2 || ', \'' || _option01 || '\'';
        END IF;
        IF _option02 IS NOT NULL THEN
            sql1 := sql1 || ', option02';
            sql2 := sql2 || ', \'' || _option02 || '\'';
        END IF;
        IF _option03 IS NOT NULL THEN
            sql1 := sql1 || ', option03';
            sql2 := sql2 || ', \'' || _option03 || '\'';
        END IF;
        IF _option04 IS NOT NULL THEN
            sql1 := sql1 || ', option04';
            sql2 := sql2 || ', \'' || _option04 || '\'';
        END IF;
        IF _option05 IS NOT NULL THEN
            sql1 := sql1 || ', option05';
            sql2 := sql2 || ', \'' || _option05 || '\'';
        END IF;
        IF _option06 IS NOT NULL THEN
            sql1 := sql1 || ', option06';
            sql2 := sql2 || ', \'' || _option06 || '\'';
        END IF;
        IF _option07 IS NOT NULL THEN
            sql1 := sql1 || ', option07';
            sql2 := sql2 || ', \'' || _option07 || '\'';
        END IF;
        IF _option08 IS NOT NULL THEN
            sql1 := sql1 || ', option08';
            sql2 := sql2 || ', \'' || _option08 || '\'';
        END IF;
        IF _option09 IS NOT NULL THEN
            sql1 := sql1 || ', option09';
            sql2 := sql2 || ', \'' || _option09 || '\'';
        END IF;
        IF _option10 IS NOT NULL THEN
            sql1 := sql1 || ', option10';
            sql2 := sql2 || ', \'' || _option10 || '\'';
        END IF;

        -- 変更マシン(IPアドレス)
        IF ipaddr IS NOT NULL THEN
            sql1 := sql1 || ', updpcnm';
            sql2 := sql2 || ', \'' || ipaddr|| '\'';
        END IF;

        -- 変更ユーザ
        IF usr IS NOT NULL THEN
            sql1 := sql1 || ', updusrnm';
            sql2 := sql2 || ', \'' || usr|| '\'';
        END IF;

        sql1 := sql1 || ' ) ';
        sql2 := sql2 || ');';

         EXECUTE sql1 || sql2;
         
         
         errcd := 1;
         SELECT MAX(structcd) INTO code FROM t_item_struct WHERE itemcd = itemcode;
         RETURN;

    -- 変更
    ELSIF kubun = 2 THEN

        -- 更新ユーザ
        sql1 := 'UPDATE t_item_struct SET updusrnm=' || '\'' || usr || '\'';

        -- バリエーション名
        IF variationnm IS NOT NULL THEN
            sql1 := sql1 || ', variationnm=' || '\'' || variationnm || '\'';
        END IF;

         -- メーカー品番
        IF makeritemcode IS NOT NULL THEN
            sql1 := sql1 || ', makeritemcd=s_get_nocase_text_lower(' || '\'' || makeritemcode || '\')';
        END IF;

        -- JANコード
        IF jancode IS NOT NULL THEN
            sql1 := sql1 || ', jancd=' || '\'' || jancode || '\'';
        END IF;

        -- 発売日
        IF saledate IS NOT NULL THEN
            sql1 := sql1 || ', salestartdate=' || '\'' || saledate || '\'';
        ELSE
            sql1 := sql1 || ', salestartdate=NULL';
        END IF;

        -- 発注締切日
        IF orderdate IS NOT NULL THEN
            sql1 := sql1 || ', orderclosedate=' || '\'' || orderdate || '\'';
        ELSE
            sql1 := sql1 || ', orderclosedate=NULL';
        END IF;

        -- メーカー定価
        IF teika IS NOT NULL THEN
            sql1 := sql1 || ', makerprice=' || teika ;
        ELSE
            sql1 := sql1 || ', makerprice=NULL';
        END IF;

        -- コメント
        IF coment IS NOT NULL THEN
            sql1 := sql1 || ', comment=' || '\'' || coment || '\'';
        END IF;

        -- オプション情報
        IF _option01 IS NOT NULL THEN
            sql1 := sql1 || ', option01=' || '\'' || _option01 || '\'';
        END IF;
        IF _option02 IS NOT NULL THEN
            sql1 := sql1 || ', option02=' || '\'' || _option02 || '\'';
        END IF;
        IF _option03 IS NOT NULL THEN
            sql1 := sql1 || ', option03=' || '\'' || _option03 || '\'';
        END IF;
        IF _option04 IS NOT NULL THEN
            sql1 := sql1 || ', option04=' || '\'' || _option04 || '\'';
        END IF;
        IF _option05 IS NOT NULL THEN
            sql1 := sql1 || ', option05=' || '\'' || _option05 || '\'';
        END IF;
        IF _option06 IS NOT NULL THEN
            sql1 := sql1 || ', option06=' || '\'' || _option06 || '\'';
        END IF;
        IF _option07 IS NOT NULL THEN
            sql1 := sql1 || ', option07=' || '\'' || _option07 || '\'';
        END IF;
        IF _option08 IS NOT NULL THEN
            sql1 := sql1 || ', option08=' || '\'' || _option08 || '\'';
        END IF;
        IF _option09 IS NOT NULL THEN
            sql1 := sql1 || ', option09=' || '\'' || _option09 || '\'';
        END IF;
        IF _option10 IS NOT NULL THEN
            sql1 := sql1 || ', option10=' || '\'' || _option10 || '\'';
        END IF;

        -- 変更PC(IPアドレス)
        IF ipaddr IS NOT NULL THEN
            sql1 := sql1 || ', updpcnm=' || '\'' || ipaddr || '\'';
        END IF;

        sql1 := sql1 || ' WHERE itemcd=' || itemcode || ' AND structcd=' || structcode || ';';
        EXECUTE sql1;
        errcd := 1;
        --errmsg := sql1;
        code := itemcode;
        RETURN;
    -- 削除
    ELSIF kubun =3 THEN
        UPDATE t_item_struct
           SET delflg   = TRUE,
               updusrnm = usr,
               updpcnm  = ipaddr
         WHERE itemcd = itemcode AND structcd = structcode;
        errcd := 1;
        RETURN;
    END IF;
END;

$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

