change log change_log

delimiter $$
drop trigger if exists SALES_ORDER_INV_DESC_UpdateActionAfterUpdate;
CREATE TRIGGER SALES_ORDER_INV_DESC_UpdateActionAfterUpdate
After Update ON SALES_ORDER_INV_DESC
FOR EACH ROW
BEGIN
DECLARE logs char(255); 
DECLARE thisLog char(255); 
/* SET logs='Start ';*/
SET logs='' ;
SET thisLog='' ;
insert into SALES_ORDER_INV_DESC_BAK (STATUS_UPDATE,PO_REMARK,PARENT_RECORDID,PRAMOUNT,DNAMT,REC_DATE,DUE_DATE,USER,SALES_TYPE,NO,DESCRIPTION,MAINTAIN_MTHS,MAINTAIN_TYPE,MAINTAIN_RENEW_NOTICE,QTY,Unit,CUR,RATE,PRICE,DISCOUNT,AMOUNT,DELIVERY_LOCATION,COMMITED_COMPLETION_DATE,COST_FILE,CONFIRM,recordID,PRODUCT_RECORDID)
values(NEW.STATUS_UPDATE,NEW.PO_REMARK,NEW.PARENT_RECORDID,NEW.PRAMOUNT,NEW.DNAMT,NEW.REC_DATE,NEW.DUE_DATE,NEW.USER,NEW.SALES_TYPE,NEW.NO,NEW.DESCRIPTION,NEW.MAINTAIN_MTHS,NEW.MAINTAIN_TYPE,NEW.MAINTAIN_RENEW_NOTICE,NEW.QTY,NEW.Unit,NEW.CUR,NEW.RATE,NEW.PRICE,NEW.DISCOUNT,NEW.AMOUNT,NEW.DELIVERY_LOCATION,NEW.COMMITED_COMPLETION_DATE,NEW.COST_FILE,NEW.CONFIRM,NEW.recordID,NEW.PRODUCT_RECORDID); 
IF (NEW.ITEM_CONFIRM=1 AND OLD.ITEM_CONFIRM!=NEW.ITEM_CONFIRM) THEN INSERT INTO MESSAGE_HEADER( STATUS, DATE,ACTION,DOCUMENT, USER_RECORDID, USER, RECORDID, PARENT_RECORDID,CONTENT) VALUES ('U',CURDATE(),'Item Approve','SOI',NEW.USER_RECORDID,NEW.USER, round(unix_timestamp(now())*10+rand()*10000,0), NEW.PARENT_RECORDID,CONCAT('Approve : ',NEW.DESCRIPTION)); END IF;
IF (NEW.ITEM_CONFIRM=0 AND OLD.ITEM_CONFIRM!=NEW.ITEM_CONFIRM) THEN INSERT INTO MESSAGE_HEADER( STATUS, DATE,ACTION,DOCUMENT, USER_RECORDID, USER, RECORDID, PARENT_RECORDID,CONTENT) VALUES ('U',CURDATE(),'Item Revoke ','SOI',NEW.USER_RECORDID,NEW.USER, round(unix_timestamp(now())*10+rand()*10000,0), NEW.PARENT_RECORDID,CONCAT('Revoke  : ',NEW.DESCRIPTION)) ; END IF;

SET thisLog='' ;
if (NEW.ROW_NO <> OLD.ROW_NO) then set thisLog=CONCAT('ROW_NO [ ' ,OLD.ROW_NO ,'->', NEW.ROW_NO,']\n');end if; 
if (NEW.ROW_NO IS NOT NULL AND OLD.ROW_NO IS NULL) then set thisLog=CONCAT('ROW_NO [ ' ,'NULL' ,'->', NEW.ROW_NO,']\n');end if;
if (NEW.ROW_NO IS NULL AND OLD.ROW_NO IS NOT NULL) then set thisLog=CONCAT('ROW_NO [ ' ,OLD.ROW_NO ,'->', 'NULL',']\n');end if;  
set logs=if(thislog='','',CONCAT(logs,if(logs='','',' ,'),thisLog));

SET thisLog='' ;
if (NEW.DESCRIPTION <> OLD.DESCRIPTION) then set thisLog=CONCAT('DESCRIPTION [ ' ,OLD.DESCRIPTION ,'->', NEW.DESCRIPTION,']\n');end if; 
if (NEW.DESCRIPTION IS NOT NULL AND OLD.DESCRIPTION IS NULL) then set thisLog=CONCAT('DESCRIPTION [ ' ,'NULL' ,'->', NEW.DESCRIPTION,']\n');end if;
if (NEW.DESCRIPTION IS NULL AND OLD.DESCRIPTION IS NOT NULL) then set thisLog=CONCAT('DESCRIPTION [ ' ,OLD.DESCRIPTION ,'->', 'NULL',']\n');end if;  
set logs=if(thislog='','',CONCAT(logs,if(logs='','',' ,'),thisLog));

if (length(logs)>1) then
insert into CHANGE_LOG(LOG,date,time,user,status,RECORDID,PARENT_RECORDID,HEADER_RECORDID,LINE_RECORDID) 
values (concat(logs),curdate(),curtime(),NEW.USER,'',ROUND(unix_timestamp(now())*1000+rand()*1000,0),NEW.PARENT_RECORDID,NEW.PARENT_RECORDID,NEW.RECORDID);
end if;
END;
$$
delimiter ;