-- ABS-101819

----------------- CUSTOMERGARMENTDISPENSER-----------------

ALTER TABLE CUSTOMERGARMENTDISPENSER ADD (SEQUENCENUMBER NUMBER DEFAULT 1 NOT NULL);

COMMENT ON COLUMN CUSTOMERGARMENTDISPENSER.SEQUENCENUMBER 
    IS  'Used to define same interface for customer mulltiple times with different attributes like communication type';

begin
  execute immediate 'ALTER TABLE CUSTOMERGARMENTDISPENSER DROP CONSTRAINT XPKCUSTOMERGARMENTDISPENSER';
exception
  when others then
    null;
end;
/

ALTER TABLE CUSTOMERGARMENTDISPENSER  ADD  CONSTRAINT XPKCUSTOMERGARMENTDISPENSER PRIMARY KEY (CUSTOMER_ID, INTERFACE, SEQUENCENUMBER);
  
CREATE INDEX XIF4CUSTOMERGARMENTDISPENSER ON CUSTOMERGARMENTDISPENSER (CUSTOMER_ID ASC);  


---------------GARMENTDISPENSER-----------------------
ALTER TABLE GARMENTDISPENSER ADD (SEQUENCENUMBER NUMBER DEFAULT 1 NOT NULL,
                        COMMUNICATIONTYPE NUMBER DEFAULT 0 NOT NULL CONSTRAINT GMTDISPENSER_COMMTYPE_CHK CHECK (COMMUNICATIONTYPE IN (0, 1)));

COMMENT ON COLUMN GARMENTDISPENSER.INTERFACE
    IS  '1 = LCT KUA 2 = Kannegiesser 3 = UNIMAT 4 = UNIMAT - Check in 5 = ABS Standard 6 = WalknCloset';
                
COMMENT ON COLUMN GARMENTDISPENSER.SEQUENCENUMBER 
    IS  'Used to define same interface for customer mulltiple times with different attributes like communication type'; 

COMMENT ON COLUMN GARMENTDISPENSER.COMMUNICATIONTYPE 
    IS  '0 = Flat File, 
1 = Message Broker';  
ALTER TABLE GARMENTDISPENSER DROP CONSTRAINT CHK_GARMENTDISPENSER_INTERFACE;

UPDATE WEAREREMPLOYMENT WE
SET WE.DELIVERYPOINT_ID = (SELECT GD1.DELIVERYPOINT_ID FROM GARMENTDISPENSER GD1 WHERE GD1.GARMENTDISPENSER_ID = WE.GARMENTDISPENSER_ID ),
WE.GARMENTDISPENSER_ID = NULL
WHERE WE.GARMENTDISPENSER_ID IS NOT NULL
AND WE.GARMENTDISPENSER_ID IN (SELECT GD.GARMENTDISPENSER_ID FROM GARMENTDISPENSER GD WHERE GD.CUSTOMER_ID NOT IN (SELECT CDG.CUSTOMER_ID FROM CUSTOMERGARMENTDISPENSER CDG));
COMMIT;

DELETE FROM GARMENTDISPENSER GD WHERE NOT EXISTS (SELECT * FROM CUSTOMERGARMENTDISPENSER CDG WHERE CDG.CUSTOMER_ID = GD.CUSTOMER_ID);
COMMIT;

create or replace procedure update_garmentDispenser is
   cdg2 customergarmentdispenser%rowtype;
   gd1 garmentdispenser%rowtype;
   gv_defaultuserid systemuser.systemuser_id%type;
   gv_userexception exception;
   gv_abslogmessage abslog.message%type;
   count_rows integer:=0;

   procedure write_log(fp_message  in varchar2,
                      fp_priority in number default 1,
                      fp_plantid  in plant.plant_id%type default null) is
  begin
    /* Logging: write message in ABSLOG with these priorities:
    1 = Message
    2 = Warning
    3 = Error */
    abslogwrite.abslog_write(5, 'update_garmentDispenser ', fp_plantid, fp_priority, fp_message,
                             gv_defaultuserid);

  end write_log;
begin
 for GDISP in (select gd.GARMENTDISPENSER_ID, gd.interface  from garmentdispenser gd order by gd.CUSTOMER_ID asc, gd.INTERFACE asc, gd.DISPENSERNUMBER asc) loop
                begin
                    if gdisp.interface = 0 then
                          begin
                            gv_abslogmessage:= 'GD interface is 0 for '|| gdisp.garmentdispenser_id;
                            write_log (gv_abslogmessage,1);
                          update garmentdispenser GD
                          set gd.interface = (select cdg.interface from customergarmentdispenser cdg where cdg.sequencenumber = 1 and cdg.customer_id = gd.customer_id)
                          where gd.garmentdispenser_id = gdisp.garmentdispenser_id;
                          commit;
                          end;
                         else
                           begin
                           if gdisp.interface = 6 then
                           begin
                             gv_abslogmessage:= 'GD interface is 6 for '|| gdisp.garmentdispenser_id;
                            write_log (gv_abslogmessage,1);
                           select gd.*
                           into gd1
                           from garmentdispenser gd
                           where gd.customer_id in (select cdg.customer_id from customergarmentdispenser cdg
                                                  where cdg.customer_id = gd.customer_id
                                                  and cdg.interface = 6)
                                                  and gd.garmentdispenser_id = gdisp.garmentdispenser_id;
                          exception
                            when no_data_found then
                              begin
                                 gv_abslogmessage:= 'GD interface is 6 for '|| gdisp.garmentdispenser_id ||'but no respective customerGarmentDispensers for this';
                                 write_log (gv_abslogmessage,1);
                                select cdg.* into cdg2 from customergarmentdispenser cdg join garmentdispenser gd 
                                on gd.customer_id = cdg.customer_id
                                where cdg.interface<>6
                                and gd.garmentdispenser_id = gdisp.garmentdispenser_id;
                                cdg2.interface := 6;
                                cdg2.sequencenumber := cdg2.sequencenumber+1;
                                insert into customergarmentdispenser
                                values cdg2;
                                commit;
                                update garmentdispenser gd
                                set gd.sequencenumber = cdg2.sequencenumber
                                where gd.customer_id = cdg2.customer_id
                                and gd.garmentdispenser_id = gdisp.garmentdispenser_id;
                                commit;
                                end;
                              end;
                         end if;
                         end;
                          end if;
                               count_rows:=count_rows+1;
                               end;
                        end loop;
  if count_rows = 0 then
    begin
      gv_abslogmessage:= 'No Garment Dispenser Found';
      write_log (gv_abslogmessage,1);
    end;
   end if;

end update_garmentDispenser;

/

execute update_garmentDispenser;

drop procedure update_garmentDispenser;


ALTER TABLE GARMENTDISPENSER ADD CONSTRAINT CHK_GARMDISP_INTERFACE CHECK (INTERFACE IN (1, 2, 3, 4, 5, 6));

DROP INDEX XAK1GARMENTDISPENSER;

CREATE UNIQUE INDEX XAK1GARMENTDISPENSER ON GARMENTDISPENSER (CUSTOMER_ID ASC, INTERFACE ASC, SEQUENCENUMBER ASC, CODE ASC);

CREATE UNIQUE INDEX XAK3GARMENTDISPENSER ON GARMENTDISPENSER (CUSTOMER_ID ASC, CODE ASC);

ALTER TABLE  GARMENTDISPENSER
  ADD CONSTRAINT FK_GARMDISP_CUSTGD FOREIGN KEY (CUSTOMER_ID, INTERFACE, SEQUENCENUMBER)
  REFERENCES CUSTOMERGARMENTDISPENSER(CUSTOMER_ID, INTERFACE, SEQUENCENUMBER) ON DELETE CASCADE;


  
-- GDTRANSACTION
ALTER TABLE GDTRANSACTION ADD (INITIALUPLOAD NUMBER DEFAULT 0 NOT NULL CONSTRAINT GDTRANS_INITUP_CHK CHECK (INITIALUPLOAD IN (0, 1)),
                GDMESSAGECONTENT CLOB NULL CONSTRAINT GDTRANS_GDMESSCONT_CHK CHECK (GDMESSAGECONTENT IS JSON));
                
COMMENT ON COLUMN GDTRANSACTION.INITIALUPLOAD 
    IS  '0 = Not Applicable
1 = Initial Upload
';  

COMMENT ON COLUMN GDTRANSACTION.GDMESSAGECONTENT 
    IS  'JSON message that was sent via message broker';

  
--GD_I_TRANSACTION  
CREATE SEQUENCE SEQ_GD_I_TRANSACTION
START WITH 1
INCREMENT BY 1;

CREATE TABLE GD_I_TRANSACTION
(
GD_I_TRANSACTION_ID   NUMBER  NOT NULL,
CUSTOMER_ID   NUMBER  NOT NULL,
INTERFACE   NUMBER   NOT NULL  CONSTRAINT GD_I_TRANS_INTERFACE_CHK CHECK (INTERFACE  IN (1, 2, 3, 4, 5, 6)),
SEQUENCENUMBER   NUMBER  NOT NULL,
GDMESSAGECONTENT  CLOB NULL CONSTRAINT GD_I_TRANS_GDMESSCONT_CHK CHECK (GDMESSAGECONTENT IS JSON),
GDTRANSACTIONTYPE   NUMBER  NULL,
TRANSACTIONNUMBER  NUMBER NOT NULL,
STATUS_ID   NUMBER    NOT NULL,
CREATIONTIMESTAMP     DATE NULL ,
RESPONSESENT  DATE NULL ,
SYSTEMUSER_ID     NUMBER NOT NULL,
TIMESTAMP   DATE  NOT NULL
);

COMMENT ON COLUMN GD_I_TRANSACTION.INTERFACE IS '1 = LCT KUA 2 = Kannegiesser 3 = UNIMAT 4 = UNIMAT - Check in 5 = ABS Standard 6 = WalknCloset';
COMMENT ON COLUMN GD_I_TRANSACTION.GDMESSAGECONTENT   IS 'JSON message that was received from message broker';
COMMENT ON COLUMN GD_I_TRANSACTION.GDTRANSACTIONTYPE  IS 'TransactionType of the GD Transaction as used at ABS for the GD dispenser interface';
COMMENT ON COLUMN GD_I_TRANSACTION.TRANSACTIONNUMBER  IS 'TransactionType of the GD Transaction as used by the vendor of the GD dispenser';



ALTER TABLE GD_I_TRANSACTION 
  ADD CONSTRAINT XPKCGD_I_TRANSACTION  PRIMARY KEY (GD_I_TRANSACTION_ID);

CREATE INDEX XIF1GD_I_TRANSACTION  ON GD_I_TRANSACTION (CUSTOMER_ID ASC, INTERFACE ASC, SEQUENCENUMBER ASC);
CREATE INDEX XIF2GD_I_TRANSACTION  ON GD_I_TRANSACTION (STATUS_ID ASC);
CREATE INDEX XIE1GD_I_TRANSACTION  ON GD_I_TRANSACTION (CREATIONTIMESTAMP ASC);
CREATE INDEX XIE2GD_I_TRANSACTION  ON GD_I_TRANSACTION (RESPONSESENT ASC);
CREATE INDEX XIE3GD_I_TRANSACTION  ON GD_I_TRANSACTION (GDTRANSACTIONTYPE ASC);
CREATE INDEX XIE4GD_I_TRANSACTION  ON GD_I_TRANSACTION (TRANSACTIONNUMBER  ASC);


ALTER TABLE GD_I_TRANSACTION 
  ADD CONSTRAINT FK_GD_I_TRANS_CUSTGD FOREIGN KEY (CUSTOMER_ID, INTERFACE, SEQUENCENUMBER)
  REFERENCES CUSTOMERGARMENTDISPENSER(CUSTOMER_ID, INTERFACE, SEQUENCENUMBER) ON DELETE CASCADE;

ALTER TABLE GD_I_TRANSACTION 
  ADD CONSTRAINT FK_GD_I_TRANS_STATUS FOREIGN KEY (STATUS_ID)
  REFERENCES STATUS(STATUS_ID);



