DROP TRIGGER `shopordmst_after_insert`;;
CREATE DEFINER=`root`@`%` TRIGGER `shopordmst_after_insert` AFTER INSERT ON SHOPORDMST FOR EACH ROW BEGIN
	IF (SELECT EXISTS (SELECT 1  FROM SHOPPRODIN  
					    WHERE COMPNO = NEW.COMPNO
						  AND SHOPCD = NEW.SHOPID
						  AND SHOPPRODNO = SUBSTRING_INDEX(NEW.MALL_PRODUCT_ID, ',', 1)
						  AND COMPAYNY_GOODS_CD = NEW.COMPAYNY_GOODS_CD) AS FLAG) THEN
		UPDATE SHOPPRODIN SET DESCRITION = ifnull(DESCRITION,0) + ifnull(NEW.SALE_CNT,0)  
		 WHERE COMPNO = NEW.COMPNO
		   AND SHOPCD = NEW.SHOPID 
		   AND SHOPPRODNO = SUBSTRING_INDEX(NEW.MALL_PRODUCT_ID, ',', 1)
			AND COMPAYNY_GOODS_CD = NEW.COMPAYNY_GOODS_CD;
	END IF;   
END;;
DROP TRIGGER `shopordmst_after_update`;;
CREATE DEFINER=`root`@`%` TRIGGER `shopordmst_after_update` AFTER UPDATE ON SHOPORDMST FOR EACH ROW BEGIN
	IF (SELECT EXISTS (SELECT 1  FROM SHOPPRODIN  
					    WHERE COMPNO = NEW.COMPNO
						  AND SHOPCD = NEW.SHOPID
						  AND SHOPPRODNO = SUBSTRING_INDEX(NEW.MALL_PRODUCT_ID, ',', 1) 
						  AND COMPAYNY_GOODS_CD = NEW.COMPAYNY_GOODS_CD) AS FLAG) THEN
		UPDATE SHOPPRODIN SET DESCRITION = ifnull(DESCRITION,0) + ifnull(NEW.SALE_CNT,0)  
		 WHERE COMPNO = NEW.COMPNO
		   AND SHOPCD = NEW.SHOPID 
		   AND SHOPPRODNO = SUBSTRING_INDEX(NEW.MALL_PRODUCT_ID, ',', 1) 
		   AND COMPAYNY_GOODS_CD = NEW.COMPAYNY_GOODS_CD;
	END IF; 
END;;
DROP TRIGGER `shopordmst_before_delete`;;
CREATE DEFINER=`root`@`%` TRIGGER `shopordmst_before_delete` BEFORE DELETE ON SHOPORDMST FOR EACH ROW BEGIN
	IF (SELECT EXISTS (SELECT 1  FROM SHOPPRODIN  
					    WHERE COMPNO = OLD.COMPNO
					  	  AND SHOPCD = OLD.SHOPID
						  AND SHOPPRODNO = SUBSTRING_INDEX(OLD.MALL_PRODUCT_ID, ',', 1) 
						  AND COMPAYNY_GOODS_CD = OLD.COMPAYNY_GOODS_CD) AS FLAG) THEN
		UPDATE SHOPPRODIN SET DESCRITION = ifnull(DESCRITION,0) - ifnull(OLD.SALE_CNT,0)  
		 WHERE COMPNO = OLD.COMPNO
		   AND SHOPCD = OLD.SHOPID
		   AND SHOPPRODNO = SUBSTRING_INDEX(OLD.MALL_PRODUCT_ID, ',', 1) 
		   AND COMPAYNY_GOODS_CD = OLD.COMPAYNY_GOODS_CD;
	END IF; 
END;;
DROP TRIGGER `shopordmst_before_update`;;
CREATE DEFINER=`root`@`%` TRIGGER `shopordmst_before_update` BEFORE UPDATE ON SHOPORDMST FOR EACH ROW BEGIN
	IF (SELECT EXISTS (SELECT 1  FROM SHOPPRODIN  
					    WHERE COMPNO = OLD.COMPNO
						  AND SHOPCD = OLD.SHOPID
						  AND SHOPPRODNO = SUBSTRING_INDEX(OLD.MALL_PRODUCT_ID, ',', 1) 
						  AND COMPAYNY_GOODS_CD = OLD.COMPAYNY_GOODS_CD) AS FLAG) THEN
		UPDATE SHOPPRODIN SET DESCRITION = ifnull(DESCRITION,0) - ifnull(OLD.SALE_CNT,0)
		 WHERE COMPNO = OLD.COMPNO
		   AND SHOPCD = OLD.SHOPID
		   AND SHOPPRODNO = SUBSTRING_INDEX(OLD.MALL_PRODUCT_ID, ',', 1) 
		   AND COMPAYNY_GOODS_CD = OLD.COMPAYNY_GOODS_CD;
	END IF; 
END;;
DROP TRIGGER `shopprodin_after_update`;;
CREATE DEFINER=`root`@`%` TRIGGER `shopprodin_after_update` AFTER UPDATE ON SHOPPRODIN FOR EACH ROW BEGIN

	IF (SELECT EXISTS (SELECT 1  FROM SUM_PRODINCOUNTS  
							  WHERE COMPNO = NEW.COMPNO 
								AND GOODS_KEYWORD = ifnull(NEW.GOODS_KEYWORD,'')
								AND SHOPCD = NEW.SHOPCD 
								 AND SHOPID = ifnull(NEW.SHOP_USERID,'') 
								 AND SHOPSEQ = ifnull(NEW.SHOPSEQ,0)
								 AND SHOPSENDSTATUS = NEW.SHOPSENDSTATUS) AS FLAG) THEN
		UPDATE SUM_PRODINCOUNTS SET COUNTS = COUNTS + 1  
		 WHERE COMPNO = NEW.COMPNO 
		   AND GOODS_KEYWORD = ifnull(NEW.GOODS_KEYWORD,'')
			AND SHOPCD = NEW.SHOPCD 
			AND SHOPID = ifnull(NEW.SHOP_USERID,'') 
			AND SHOPSEQ = ifnull(NEW.SHOPSEQ,0) 
			AND SHOPSENDSTATUS = NEW.SHOPSENDSTATUS;
	ELSE 
		INSERT INTO SUM_PRODINCOUNTS(COMPNO,GOODS_KEYWORD,SHOPCD,SHOPID,SHOPSEQ,SHOPSENDSTATUS,COUNTS) 
		VALUES (NEW.COMPNO,ifnull(NEW.GOODS_KEYWORD,''),NEW.SHOPCD,ifnull(NEW.SHOP_USERID,''),ifnull(NEW.SHOPSEQ,0),NEW.SHOPSENDSTATUS,1);
	END IF;

	
	IF (SELECT EXISTS (SELECT 1  FROM SHOPPRODINFO WHERE PRODSEQ = NEW.PRODSEQ) AS FLAG2) THEN
		UPDATE SHOPPRODINFO 
	      SET DESCRITION = ifnull(DESCRITION,0) + IFNULL(NEW.DESCRITION,0)  		 
	    WHERE PRODSEQ= NEW.PRODSEQ
	      AND IFNULL(NEW.DESCRITION,0) > 0;
	END IF;
	
END;;

DROP TRIGGER `shopprodin_before_delete`;;
CREATE DEFINER=`root`@`%` TRIGGER `shopprodin_before_delete` BEFORE DELETE ON SHOPPRODIN FOR EACH ROW BEGIN

	UPDATE SUM_PRODINCOUNTS SET COUNTS = COUNTS - 1 
	 WHERE COMPNO = OLD.COMPNO AND GOODS_KEYWORD = OLD.GOODS_KEYWORD
	   AND SHOPCD = OLD.SHOPCD AND SHOPID = OLD.SHOP_USERID AND SHOPSEQ = OLD.SHOPSEQ 
		AND SHOPSENDSTATUS = OLD.SHOPSENDSTATUS;

	IF (SELECT EXISTS (SELECT 1  FROM SHOPPRODINFO WHERE PRODSEQ = OLD.PRODSEQ) AS FLAG2) THEN
		UPDATE SHOPPRODINFO 
		   SET DESCRITION = ifnull(DESCRITION,0) - IFNULL(OLD.DESCRITION,0)  		 
    	 WHERE COMPNO = OLD.COMPNO
           AND PRODSEQ= OLD.PRODSEQ
           AND IFNULL(OLD.DESCRITION,0) > 0;
	END IF;
	
END;;

DROP TRIGGER `shopprodin_before_update`;;
CREATE DEFINER=`root`@`%` TRIGGER `shopprodin_before_update` BEFORE UPDATE ON SHOPPRODIN FOR EACH ROW BEGIN

	UPDATE SUM_PRODINCOUNTS SET COUNTS = COUNTS - 1 
	 WHERE COMPNO = OLD.COMPNO 
	   AND GOODS_KEYWORD = OLD.GOODS_KEYWORD
	   AND SHOPCD = OLD.SHOPCD 
		AND SHOPID = OLD.SHOP_USERID 
		AND SHOPSEQ = OLD.SHOPSEQ 
		AND SHOPSENDSTATUS = OLD.SHOPSENDSTATUS;

	IF (SELECT EXISTS (SELECT 1  FROM SHOPPRODINFO WHERE PRODSEQ = OLD.PRODSEQ) AS FLAG2) THEN
		UPDATE SHOPPRODINFO 
           SET DESCRITION = ifnull(DESCRITION,0) - IFNULL(OLD.DESCRITION,0)  		 
         WHERE COMPNO = OLD.COMPNO
           AND PRODSEQ= OLD.PRODSEQ
           AND IFNULL(OLD.DESCRITION,0) > 0;
	END IF;	

END;;

DROP PROCEDURE `YWM_IMSI7`;;
CREATE DEFINER=`root`@`%` PROCEDURE `YWM_IMSI7`()
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
	DECLARE _compno         		INT;
	DECLARE _shopid     				varchar(50);
	DECLARE _mall_product_id		varchar(50);
	DECLARE _compayny_goods_cd    varchar(50);
	DECLARE _sale_cnt   				INT;
	
	DECLARE done2 INT DEFAULT 0;
	DECLARE c1 CURSOR FOR  SELECT COMPNO,SHOPID,SUBSTRING_INDEX(MALL_PRODUCT_ID, ',', 1) as MALL_PRODUCT_ID,
											COMPAYNY_GOODS_CD,SUM(SALE_CNT) AS SALE_CNT
									 FROM SHOPORDMST
									WHERE compno=4
								GROUP BY COMPNO,SHOPID,MALL_PRODUCT_ID,COMPAYNY_GOODS_CD
								  HAVING SUM(SALE_CNT)>0;
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done2 = 1;
	BEGIN
		OPEN c1;
		REPEAT
		FETCH NEXT FROM c1 INTO _compno,_shopid,_mall_product_id,_compayny_goods_cd,_sale_cnt;
		IF not done2 THEN
			UPDATE SHOPPRODIN SET DESCRITION = _sale_cnt 
			 WHERE compno = _compno 
			   AND shopcd = _shopid
			   AND shopprodno = _mall_product_id
			   AND compayny_goods_cd = _compayny_goods_cd;
		END if;
		COMMIT;
		UNTIL done2 END REPEAT;
		close c1;
	END;
END;;

DROP PROCEDURE `YWM_IMSI8`;;
CREATE DEFINER=`root`@`%` PROCEDURE `YWM_IMSI8`()
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
	DECLARE _compno         		INT;
	DECLARE _prodseq     			INT;
	DECLARE _sale_cnt   				INT;
	
	DECLARE done2 INT DEFAULT 0;
	DECLARE c1 CURSOR FOR  SELECT COMPNO,PRODSEQ,SUM(DESCRITION) AS SALE_CNT
									 FROM SHOPPRODIN
									WHERE compno=4
									  AND ifnull(DESCRITION,0) > 0
								GROUP BY COMPNO,PRODSEQ
								  HAVING SUM(DESCRITION)>0;
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done2 = 1;
	BEGIN
		OPEN c1;
		REPEAT
		FETCH NEXT FROM c1 INTO _compno,_prodseq,_sale_cnt;
		IF not done2 THEN
			UPDATE SHOPPRODINFO SET DESCRITION = _sale_cnt 
			 WHERE compno	= _compno 
			 	AND prodseq	= _prodseq;
		END if;
		COMMIT;
		UNTIL done2 END REPEAT;
		close c1;
	END;
END;;

CREATE TABLE SHOPCATEGORYS_ESM (
	`SHOPCD` VARCHAR(50) NOT NULL COMMENT '쇼핑몰코드',
	`ESMCODE` VARCHAR(50) NOT NULL,
	`ESMSHOPCATENM` TEXT NULL,
	`CODE` VARCHAR(50) NOT NULL COMMENT '카테고리코드',
	`SHOPCATENM` TEXT NULL COMMENT '카테고리이름' COLLATE 'utf8mb4_0900_ai_ci',
	PRIMARY KEY (`SHOPCD`, `ESMCODE`, `CODE`),
	FULLTEXT INDEX `SHOPCATENM` (`SHOPCATENM`),
	FULLTEXT INDEX `ESMSHOPCATENM` (`ESMSHOPCATENM`)
)
COLLATE='utf8mb4_0900_ai_ci'
ENGINE=InnoDB;;

ALTER TABLE SHOPCATEGORYS
	ADD COLUMN `ESMCODE` VARCHAR(50) NOT NULL AFTER `CODE`,
	ADD COLUMN `ESMSHOPCATENM` TEXT NULL AFTER `SHOPCATENM`,
	DROP PRIMARY KEY,
	DROP INDEX `SHOPCD_CODE`,
	ADD PRIMARY KEY (`SHOPCD`, `CODE`, `ESMCODE`),
	ADD UNIQUE INDEX `SHOPCD_CODE_ESMCODE` (`SHOPCD`, `CODE`, `ESMCODE`),
	ADD FULLTEXT INDEX `ESMSHOPCATENM` (`ESMSHOPCATENM`);;
	
DROP PROCEDURE IF EXISTS `YWM_SALES_STATISTICS`;;
CREATE DEFINER=`root`@`%` PROCEDURE `YWM_SALES_STATISTICS`(
	IN `V_COMPNO` INT,
	IN `V_SDATE` VARCHAR(8),
	IN `V_EDATE` VARCHAR(8),
	IN `V_OPT` INT,
	IN `V_SHOPID` VARCHAR(10),
	IN `V_SHOP_USERID` VARCHAR(50)

)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT '쇼핑몰 통계정보'
BEGIN
	drop TEMPORARY TABLE if EXISTS tmp;
	CREATE TEMPORARY TABLE IF NOT EXISTS tmp ENGINE=MEMORY AS
	(SELECT DATE_FORMAT(a.reg_date,'%Y%m%d') as reg_date, DATE_FORMAT(left(a.order_date,19),'%Y%m%d') as order_date,a.shopid,
			  (SELECT shopnm FROM SHOPINFO WHERE shopcd=a.shopid) AS shopnam,(SELECT nicknm1 FROM shopdtl WHERE shopseq=a.shopseq and shopcd = a.shopid and compno=a.compno) as shop_nickname,
			  a.shop_userid,a.order_id,a.order_status,IFNULL(a.mall_exp_cd,'0'),
			  a.mall_product_id,a.compayny_goods_cd,a.product_name,sku_id,a.p_product_name,a.p_sku_id,a.p_sku_value,
	        a.user_name,a.sale_cnt,a.total_cost,0 as ret_cnt,0 as ret_cost,0 as cnc_cnt,0 as cnc_cost,
			  0 as goods_cost, 0.00 commmis, 0.00 AS compare,
	         a.won_cost, a.mall_won_cost,a.delv_cost,0 as profit
	   FROM SHOPORDMST a
	  WHERE a.compno=V_COMPNO and order_status <> '202' AND order_status <> 'E02'
	    AND DATE_FORMAT(left(a.order_date,19),'%Y%m%d') BETWEEN V_SDATE AND V_EDATE 
		AND a.shopid like V_SHOPID AND a.shopseq like V_SHOP_USERID 
		order by 2);
		
	drop TEMPORARY TABLE if EXISTS tmp2;
	CREATE TEMPORARY TABLE IF NOT EXISTS tmp2 ENGINE=MEMORY AS (select * from tmp LIMIT 1);
	delete from tmp2;
	
	BEGIN

		DECLARE _reg_date varchar(8);
		DECLARE _order_date varchar(8);
		DECLARE _shopid varchar(20);
		DECLARE _shopnam varchar(200);
		DECLARE _shop_nickname varchar(200);
		DECLARE _shop_userid varchar(200);
		DECLARE _order_id varchar(50);
		DECLARE _order_status varchar(5);
		DECLARE _mall_exp_cd varchar(200);
		DECLARE _mall_product_id VARCHAR(50);
		DECLARE _compayny_goods_cd varchar(50);
		DECLARE _product_name varchar(200);
		DECLARE _sku_id varchar(200);
		DECLARE _p_product_name varchar(200);
		DECLARE _p_sku_id varchar(20);
		DECLARE _p_sku_value varchar(200);
		DECLARE _user_name varchar(50);
		DECLARE _sale_cnt INT;
		DECLARE _total_cost INT;
		DECLARE _ret_cnt INT;
		DECLARE _ret_cost INT;
		DECLARE _cnc_cnt INT;
		DECLARE _cnc_cost INT;
		DECLARE _goods_cost int;
		DECLARE _commmis float; 
		DECLARE _compare float;
		
		DECLARE _won_cost INT;
		DECLARE _mall_won_cost INT; 
		DECLARE _delv_cost INT;
		DECLARE _profit INT;		
		
		DECLARE v_prodseq INT;
		DECLARE v_shopseq INT;
		DECLARE v_shop_rate_amt INT;
		DECLARE v_seq INT;
		
		DECLARE bf_sale_cnt INT;
		DECLARE bf_total_cost double;
		DECLARE af_sale_cnt INT;
		DECLARE af_total_cost double;
		
		Block1: BEGIN
		
			DECLARE done INT DEFAULT 0; 
			DECLARE c1 CURSOR FOR SELECT * FROM tmp;
			
			DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
			
			
			
	
			OPEN c1;
			REPEAT
				FETCH c1 INTO _reg_date,_order_date,_shopid,_shopnam,_shop_nickname,_shop_userid,_order_id,_order_status,_mall_exp_cd,
								  _mall_product_id,_compayny_goods_cd,_product_name,_sku_id,_p_product_name,
								  _p_sku_id,_p_sku_value,_user_name,_sale_cnt,_total_cost,_ret_cnt,_ret_cost,
								  _cnc_cnt,_cnc_cost,_goods_cost,_commmis,
								  _compare,_won_cost,_mall_won_cost,_delv_cost,_profit;
							  
				IF not done THEN 
					
					set _won_cost = ifnull(_won_cost,0);  					
					 
						
					set _profit = ifnull(_mall_won_cost,0) - ifnull(_won_cost,0);					
					
					if _mall_exp_cd = 'R02' then
						
						set _ret_cnt = _sale_cnt; 
						set _ret_cost = _total_cost;
						set _sale_cnt = _sale_cnt;
						set _total_cost = _total_cost;
						set _won_cost = _won_cost  ;
						set _mall_won_cost = _mall_won_cost  ;
					elseif _mall_exp_cd = 'C02' then
						
						set _cnc_cnt = _sale_cnt;
						set _cnc_cost = _total_cost;
						set _sale_cnt = _sale_cnt;
						set _total_cost = _total_cost;
						set _won_cost = _won_cost  ;
						set _mall_won_cost = _mall_won_cost ;
					end if;
 					
					insert into tmp2 values(_reg_date,_order_date,_shopid,_shopnam,_shop_nickname,_shop_userid,_order_id,_order_status,_mall_exp_cd,
													_mall_product_id,_compayny_goods_cd,_product_name,_sku_id,_p_product_name,
													_p_sku_id,_p_sku_value,_user_name,_sale_cnt,_total_cost,_ret_cnt,_ret_cost,
									 				_cnc_cnt,_cnc_cost,_won_cost,_commmis,
													_compare,_won_cost,_mall_won_cost,_delv_cost,_profit);  

				END IF;
				
			UNTIL done END REPEAT;
			CLOSE c1;
		END Block1;	
		
		
		Block2: BEGIN
			drop TEMPORARY TABLE if EXISTS tmp3;
			CREATE TEMPORARY TABLE IF NOT EXISTS tmp3 ENGINE=MEMORY AS (select * from tmp2 LIMIT 1);
			delete from tmp3;
			
			
			SELECT sum(sale_cnt),sum(total_cost) into bf_sale_cnt,bf_total_cost
			  FROM SHOPORDMST
			 WHERE compno=V_COMPNO
			   AND DATE_FORMAT(reg_date,'%Y%m%d') 
				    BETWEEN DATE_FORMAT(DATE_ADD(DATE_FORMAT(V_SDATE,'%Y%m%d'), INTERVAL -1 MONTH),'%Y%m%d') 
				        AND DATE_FORMAT(DATE_ADD(DATE_FORMAT(V_EDATE,'%Y%m%d'), INTERVAL -1 MONTH),'%Y%m%d');
			
			
			SELECT sum(sale_cnt)-sum(ret_cnt)-sum(cnc_cnt), sum(total_cost)-sum(ret_cost)-sum(cnc_cost) 
			  into af_sale_cnt,af_total_cost
			  FROM tmp2; 
			  
			insert into tmp3(sale_cnt,total_cost) values( round(af_sale_cnt/bf_sale_cnt*100),round(af_total_cost/bf_total_cost*100) );
		END Block2;
		
		
		Block3: BEGIN
			if V_OPT = 1 then 
				select distinct compayny_goods_cd,p_product_name,shop_nickname,count(*) as ord_cnt,sum(sale_cnt) as sale_cnt,sum(total_cost) as total_cost,
					    sum(ret_cnt) as ret_cnt, sum(ret_cost) as ret_cost, sum(cnc_cnt) as cnc_cnt,sum(cnc_cost) as cnc_cost,
						 sum(goods_cost) as goods_cost,sum(mall_won_cost) as mall_won_cost,
					    sum(delv_cost) as delv_cost, sum(profit) as profit
				  from tmp2
				 group by compayny_goods_cd,p_product_name,shop_nickname
				 union all 
				select compayny_goods_cd, p_product_name,shop_nickname, 0 as ord_cnt, sale_cnt, total_cost,
					    ret_cnt,ret_cost,cnc_cnt,cnc_cost, goods_cost, mall_won_cost,
					    delv_cost, profit
				  from tmp3;
			elseif V_OPT = 2 then 
				select distinct order_date, count(*) as ord_cnt,sum(sale_cnt) as sale_cnt,sum(total_cost) as total_cost,
					    sum(ret_cnt) as ret_cnt, sum(ret_cost) as ret_cost, sum(cnc_cnt) as cnc_cnt,sum(cnc_cost) as cnc_cost,
						 sum(goods_cost) as goods_cost,sum(mall_won_cost) as mall_won_cost,
					    sum(delv_cost) as delv_cost, sum(profit) as profit
				  from tmp2
				 group by order_date
				 union all 
				select order_date, 0 as ord_cnt, sale_cnt, total_cost,
					    ret_cnt,ret_cost,cnc_cnt,cnc_cost, goods_cost, mall_won_cost,
					    delv_cost, profit
				  from tmp3;
			elseif V_OPT = 3 then 
				select distinct DATE_FORMAT(reg_date,'%Y%m') as reg_ym, count(*) as ord_cnt,sum(sale_cnt) as sale_cnt,sum(total_cost) as total_cost,
					    sum(ret_cnt) as ret_cnt, sum(ret_cost) as ret_cost, sum(cnc_cnt) as cnc_cnt,sum(cnc_cost) as cnc_cost,
						 sum(goods_cost) as goods_cost,sum(mall_won_cost) as mall_won_cost,
					    sum(delv_cost) as delv_cost, sum(profit) as profit
				  from tmp2
				 group by DATE_FORMAT(reg_date,'%Y%m')
				 union all 
				select '' as reg_ym, 0 as ord_cnt, sale_cnt, total_cost,
					    ret_cnt,ret_cost,cnc_cnt,cnc_cost, goods_cost, mall_won_cost,
					    delv_cost, profit 
				  from tmp3;
			elseif V_OPT = 4 then 
				select shopnam,shop_userid,shop_nickname, count(*) as ord_cnt,sum(sale_cnt) as sale_cnt,sum(total_cost) as total_cost,
					    sum(ret_cnt) as ret_cnt, sum(ret_cost) as ret_cost, sum(cnc_cnt) as cnc_cnt,sum(cnc_cost) as cnc_cost,
						 sum(goods_cost) as goods_cost,sum(mall_won_cost) as mall_won_cost,
					    sum(delv_cost) as delv_cost, sum(profit) as profit
				  from tmp2
				 group by shopnam,shop_userid,shop_nickname
				 union all 
				select shopnam,shop_userid,shop_nickname, 0 as ord_cnt, sale_cnt, total_cost,
					    ret_cnt,ret_cost,cnc_cnt,cnc_cost, goods_cost, mall_won_cost,
					    delv_cost, profit
				  from tmp3
				  order by shopnam;
			elseif V_OPT = 5 then 
				select distinct user_name, shop_nickname ,p_product_name,count(*) as ord_cnt,sum(sale_cnt) as sale_cnt,sum(total_cost) as total_cost,
					    sum(ret_cnt) as ret_cnt, sum(ret_cost) as ret_cost, sum(cnc_cnt) as cnc_cnt,sum(cnc_cost) as cnc_cost,
						 sum(goods_cost) as goods_cost,sum(mall_won_cost) as mall_won_cost,
					    sum(delv_cost) as delv_cost, sum(profit) as profit 
				  from tmp2
				 group by user_name,shop_nickname,p_product_name
				 union all 
				select user_name,shop_nickname ,p_product_name, 0 as ord_cnt, sale_cnt, total_cost,
					    ret_cnt,ret_cost,cnc_cnt,cnc_cost, goods_cost, mall_won_cost,
					    delv_cost, profit 
				  from tmp3;
			end if;
		END Block3;
	   
	END;

END;;

CREATE DEFINER=`root`@`%` PROCEDURE `YWM_IMSI11`()
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
	DECLARE _prodseq     			INT;
	DECLARE _full_name  				VARCHAR(200);
	
	DECLARE done2 INT DEFAULT 0;
	DECLARE c1 CURSOR FOR  SELECT a.PRODSEQ,b.FULL_NAME 
									 FROM SHOPPRODINFO AS a JOIN category AS b ON a.CLASS_CD3=b.ID
								   WHERE a.CLASS_CD3 <> '' 
									  AND a.gmcategnm = '';
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done2 = 1;
	BEGIN
		OPEN c1;
		REPEAT
		FETCH NEXT FROM c1 INTO _prodseq,_full_name;
		IF not done2 THEN
			UPDATE SHOPPRODINFO SET gmcategnm = _full_name
			 WHERE prodseq=_prodseq;
		END if;
		COMMIT;
		UNTIL done2 END REPEAT;
		close c1;
	END;
END;;

CREATE DEFINER=`root`@`%` FUNCTION `GET_NAVER_ESCODE`(
	`V_CODE` VARCHAR(50)

)
RETURNS text CHARSET utf8mb4
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
	DECLARE RETURN_VALUE TEXT;
	
	BEGIN
		   
	SELECT concat(ID,'|', SHOPCATE) into RETURN_VALUE 
	 FROM CATEGORY
		WHERE 
	 JSON_EXTRACT(SHOPCATE, '$[5].cid') = V_CODE limit 1;
		   
	END;
 	SET RETURN_VALUE = IFNULL(RETURN_VALUE, '');
   RETURN (RETURN_VALUE);
END;;

CREATE TABLE SHOPATTRINFO (
	`SHOPCD` VARCHAR(50) NOT NULL DEFAULT '',
	`GOSICODE` VARCHAR(50) NOT NULL DEFAULT '',
	`GOSINUM` VARCHAR(50) NOT NULL DEFAULT '',
	`GOSIVAL` VARCHAR(100) NULL DEFAULT NULL,
	PRIMARY KEY (`SHOPCD`, `GOSICODE`, `GOSINUM`)
)
COLLATE='utf8mb4_0900_ai_ci'
ENGINE=InnoDB;;

CREATE TABLE `tally` (
	`n` SMALLINT(5) UNSIGNED NOT NULL,
	PRIMARY KEY (`n`) USING BTREE
)
COLLATE='utf8mb4_0900_ai_ci'
ENGINE=InnoDB
;;

insert into tally SELECT tt.h FROM (
WITH recursive cte AS(
	SELECT 1 AS h
	UNION all
	SELECT h+1 FROM cte WHERE h<1000
	)
SELECT h FROM cte) AS tt;;

CREATE DEFINER=`root`@`%` FUNCTION `GET_SHOPALLCODE_ESCODE`(
	`V_CODE` JSON

)
RETURNS varchar(50) CHARSET utf8mb4
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
	DECLARE _shopid  varchar(50);
	DECLARE _shopcd  varchar(50);
	DECLARE RETURN_VALUE varchar(50);
	DECLARE rownum  int;

	DECLARE c1 CURSOR FOR SELECT ifnull(JSON_EXTRACT(V_CODE, CONCAT('$[', n - 1, '].cid')),'') as shopid, 
										  ifnull(JSON_EXTRACT(V_CODE, CONCAT('$[', n - 1, '].cid')),'') as shopcd
									FROM tally
								  WHERE n <= JSON_LENGTH(V_CODE); 

	Block0: BEGIN	
 		SET RETURN_VALUE = '';
 		SET rownum = 0;
	END Block0;						   
	
	Block1: BEGIN
		
		DECLARE done INT DEFAULT 0;
		DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

		open c1;
		REPEAT
			FETCH NEXT FROM c1 INTO _shopid,_shopcd;
			IF not done THEN			
				if _shopid = 'null' then set _shopid=''; end if;
				if _shopcd = 'null' then set _shopcd=''; end if;
				SELECT ID into RETURN_VALUE 
	 			  FROM CATEGORY
				 WHERE JSON_EXTRACT(SHOPCATE, CONCAT('$[', rownum, '].cid')) = replace(_shopid,'"','')  limit 1;
				
				SET RETURN_VALUE = IFNULL(RETURN_VALUE, '');
				IF RETURN_VALUE <> '' then 
					SET done = 1;
				END IF;	
				SET rownum = rownum + 1;
				
			END IF;
		UNTIL done END REPEAT;
		close c1;			 
	END Block1;
	
	if RETURN_VALUE='' then
		set RETURN_VALUE = '999999999';
	END if;
	
	RETURN (RETURN_VALUE);
	
END;;

CREATE DEFINER=`root`@`%` PROCEDURE `YWM_IMSI13`()
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN

	DECLARE _prodseq     		INT;
	DECLARE _shop_cid_info     JSON;
	DECLARE v_class_cd3			VARCHAR(50);
	
	DECLARE done2 INT DEFAULT 0;
	DECLARE c1 CURSOR FOR SELECT PRODSEQ,SHOP_CID_INFO FROM SHOPPRODINFO AS a left join CATEGORY AS b ON a.class_cd3=b.ID WHERE b.id is NULL;
   
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done2 = 1;
	BEGIN
		OPEN c1;
		REPEAT
		FETCH NEXT FROM c1 INTO _prodseq,_shop_cid_info;
		IF not done2 THEN
			set v_class_cd3 = GET_SHOPALLCODE_ESCODE(_shop_cid_info);
			UPDATE SHOPPRODINFO SET class_cd3 = v_class_cd3 WHERE PRODSEQ = _prodseq;
			UPDATE SHOPPRODIN SET class_cd3 = v_class_cd3 WHERE PRODSEQ = _prodseq;
			commit;
		END if;
		UNTIL done2 END REPEAT;
		close c1;
	END;
	
END;;

CREATE DEFINER=`root`@`%` PROCEDURE `YWM_IMSI14`()
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN

	DECLARE _sendseq     		INT;
	DECLARE _shop_cid_info     JSON;
	DECLARE v_class_cd3			VARCHAR(50);
	
	DECLARE done2 INT DEFAULT 0;
	DECLARE c1 CURSOR FOR SELECT SENDSEQ,SHOP_CID_INFO FROM SHOPPRODIN AS a left join CATEGORY AS b ON a.class_cd3=b.ID WHERE b.id is NULL;
   
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done2 = 1;
	BEGIN
		OPEN c1;
		REPEAT
		FETCH NEXT FROM c1 INTO _sendseq,_shop_cid_info;
		IF not done2 THEN
			set v_class_cd3 = GET_SHOPALLCODE_ESCODE(_shop_cid_info);
			UPDATE SHOPPRODIN SET class_cd3 = v_class_cd3 WHERE SENDSEQ = _sendseq;
			commit;
		END if;
		UNTIL done2 END REPEAT;
		close c1;
	END;
	
END;;

CREATE DEFINER=`ydwms`@`%` PROCEDURE `YWM_CLAIMAUTO_PROC_VER2`(
	IN `V_COMPNO` INT,
	IN `V_BRANDCD` INT,
	IN `V_CLMDT` VARCHAR(8),
	IN `V_CLMSEQ` INT,
	IN `V_INSERTID` VARCHAR(20)
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
	DECLARE	_PROCTYPE	varchar(50);
	DECLARE	_CLMDT		varchar(8);
	DECLARE	_REGDT		varchar(20);
	DECLARE	_RCVNAM		varchar(50);
	DECLARE	_ADDR			varchar(200);
	DECLARE	_CLPNO		varchar(50);
	DECLARE	_PRODCD		varchar(20);
	DECLARE	_PRODCDM		varchar(20);
	DECLARE	_DIRAMT		INT;
	DECLARE	_SUPPNAM		varchar(50);
	DECLARE	_SUPPAMT		INT;
	DECLARE	_QTY			INT;
	DECLARE	_PRICE		INT;
	DECLARE	_PRODNM		varchar(200);
	DECLARE	_SPECDES		varchar(100);
	DECLARE	_CLMCONTS	varchar(200);
	DECLARE	_RESALNAM	varchar(50);
	DECLARE	_GUBUN		char(1);
	
	DECLARE	_inouttype	VARCHAR(100);
	
	DECLARE v_warecd int;
	DECLARE v_neccd1 varchar(200);
	DECLARE v_neccd2 int;
	DECLARE v_neccd3 int;	
	
	DECLARE	v_exists			INT;
	DECLARE	v_maxordseq		INT;
	DECLARE	v_maxrecvseq	INT;
	
	DECLARE	done	INT	DEFAULT	0;
	
	
	DECLARE	c1	CURSOR	FOR	
		SELECT	PROCTYPE,	CLMDT,	REGDT,	RCVNAM,	ADDR,		CLPNO,	
					PRODCD,		QTY,		SUPPNAM,	SUPPAMT,	DIRAMT,	left(CLMCONTS,200),	RESALNAM,
					(SELECT		PRODNM	FROM	V_PRODUCTS	B	WHERE	B.COMPNO=V_COMPNO AND B.PRODCD=A.PRODCD AND B.BRANDCD=V_BRANDCD) AS PRODNM,
					(SELECT		SPECDES	FROM	V_PRODUCTS	B	WHERE B.COMPNO=V_COMPNO AND B.PRODCD=A.PRODCD AND B.BRANDCD=V_BRANDCD) AS SPECDES,
					(SELECT		PRODCDM	FROM	V_PRODUCTS	B	WHERE B.COMPNO=V_COMPNO AND B.PRODCD=A.PRODCD AND B.BRANDCD=V_BRANDCD) AS PRODCDM
		FROM		CLMLIST		A
		WHERE		A.COMPNO		=	V_COMPNO 
	 	AND		A.clmdt		=	V_CLMDT
		AND		A.clmseq		=	V_CLMSEQ
		AND		A.BRANDCD	=	V_BRANDCD;

	
	DECLARE	c2	CURSOR	FOR
		SELECT	A.gubun,		A.prodcd,		A.qty,		ifnull(A.cusprice,1),
					(SELECT	PRODCDM	FROM	V_PRODUCTS	B	WHERE B.COMPNO=V_COMPNO AND B.PRODCD=A.PRODCD AND B.BRANDCD=V_BRANDCD) AS PRODCDM
		FROM		CLMLISTDTL	A
		WHERE		A.COMPNO		=	V_COMPNO 
		AND		A.clmdt		=	V_CLMDT  
		AND		A.clmseq		=	V_CLMSEQ
		AND		A.BRANDCD	=	V_BRANDCD;
		
	DECLARE	CONTINUE	HANDLER	FOR	SQLSTATE	'02000'	SET	done	=	1;

	OPEN	c1;
	
	REPEAT
		FETCH	NEXT	FROM	c1
				INTO	_PROCTYPE,	_CLMDT,	_REGDT,		_RCVNAM,		_ADDR,	_CLPNO,
						_PRODCD,		_QTY,		_SUPPNAM,	_SUPPAMT,	_DIRAMT,	_CLMCONTS,
						_RESALNAM,	_PRODNM,	_SPECDES,	_PRODCDM;
						
		if	ifnull(_SPECDES, '')	<>	''	then
			SET	_PRODNM	=	concat(_PRODNM, '_', _SPECDES);
		END	if;
		
		IF	NOT	done	THEN
			IF	IFNULL(_DIRAMT, 0)	=	0	THEN 
				SET	_DIRAMT = 1;
			END	IF;
			
			
			INOUTTYPE_SELECT_Block:	begin
				DECLARE	sel_done		INT	DEFAULT	0;
				DECLARE	continue		handler	FOR	SQLSTATE	'02000'	SET	sel_done	=	1;
				
				SET	_inouttype	=	"";
				
				SELECT	inouttype
				INTO		_inouttype
				FROM		PROCTYPE
				WHERE		compno		=	v_compno
				AND		brandcd		=	v_brandcd
				AND		proctypecd	=	_PROCTYPE;
			END	INOUTTYPE_SELECT_Block;			
			
			IF	_inouttype	=	"발송"	then
				Block2:	BEGIN
					DECLARE	done2	INT	DEFAULT	0;
					DECLARE	CONTINUE	HANDLER	FOR	SQLSTATE	'02000'	SET	done2	=	1;
					
					OPEN	c2;
					
					REPEAT
						FETCH	NEXT	FROM	c2	
								INTO	_GUBUN,	_PRODCD,	_QTY,	_PRICE,	_PRODCDM;
								
						
						SELECT	MAX(CONCAT(PRODNM,'_',IFNULL(SPECDES,'')))
						INTO		_PRODNM
						FROM		V_PRODUCTS
						WHERE		COMPNO	=	V_COMPNO
						AND		PRODCD	=	_PRODCD
						AND		BRANDCD	=	V_BRANDCD;
						
						IF	NOT	done2	THEN 
							SET	v_maxordseq	=	0;
							SET	v_exists		=	0;
							
							if	_PRICE	=	0	then
								set _PRICE	=	1;	
							END	if;
							
							SELECT	MAX(ORDSEQ)
							INTO		v_exists
							FROM		ORDMST
							WHERE		COMPNO	=	V_COMPNO
							AND		ORDDT		=	_CLMDT
							AND		ORDSEQ	=	3
							AND		BRANDCD	=	V_BRANDCD;
							
							IF	v_exists	>	0	then
								SELECT	MAX(SEQ)
								INTO		v_maxordseq
								FROM		ORDDTL
								WHERE		COMPNO	=	V_COMPNO
								AND		ORDDT		=	_CLMDT
								AND		ORDSEQ	=	3
								AND		BRANDCD	=	V_BRANDCD;

								
								
								INSERT	INTO	ORDDTL	(COMPNO,		BRANDCD,		ORDDT,		ORDSEQ,		SEQ,
																RORDDT,	
																RCVNAM,		ADDR,			CLPNO,		QTY,			SHPFEE,
																CREDIT,		OPTDESC,		PRODCD,		MESSGE,		ORDNM,
																ORDAMT,		CLMDT,		CLMSEQ) 
											VALUES			(V_COMPNO,	V_BRANDCD,	_CLMDT,		3,				IFNULL(v_maxordseq,0)+1,
																DATE_FORMAT(_REGDT,'%Y-%m-%d %H:%i'),
																_RCVNAM,		_ADDR,		_CLPNO,		_QTY,			_SUPPAMT,
																_SUPPNAM,	_PRODNM,		_PRODCD,		_CLMCONTS,	_RESALNAM,
																_PRICE,		V_CLMDT,		V_CLMSEQ);
							ELSE
								
								INSERT	INTO	ORDMST	(COMPNO,		BRANDCD,		ORDDT,	ORDSEQ,		INSERTDT,	INSERTID)
											VALUES			(V_COMPNO,	V_BRANDCD,	_CLMDT,	3,				NOW(),		V_INSERTID);
											
								
								INSERT	INTO	ORDDTL	(COMPNO,		BRANDCD,		ORDDT,		ORDSEQ,		SEQ,
																RORDDT,
																RCVNAM,		ADDR,			CLPNO,		QTY,			SHPFEE,
																CREDIT,		OPTDESC,		PRODCD,		MESSGE,		ORDNM,
																ORDAMT,		CLMDT,		CLMSEQ) 
											VALUES			(V_COMPNO,	V_BRANDCD,	_CLMDT,		3,				1,
																DATE_FORMAT(_REGDT,'%Y-%m-%d %H:%i'),
																_RCVNAM,		_ADDR,		_CLPNO,		_QTY,			_SUPPAMT,
																_SUPPNAM,	_PRODNM,		_PRODCD,		_CLMCONTS,	_RESALNAM,
																_PRICE,		V_CLMDT,		V_CLMSEQ);
							END	if;
							
							
							UPDATE	CLMLIST
							SET		ORDDT		=	_CLMDT,
										ORDSEQ	=	3,
										SEQ		=	IFNULL(v_maxordseq, 0) + 1
							WHERE		COMPNO	=	V_COMPNO
							AND		clmdt		=	V_CLMDT
							AND		clmseq	=	V_CLMSEQ
							AND		BRANDCD	=	V_BRANDCD;
						END	IF;
					UNTIL	done2	END	REPEAT;
					
					close	c2;
				END	Block2;
			ELSEIF	_inouttype	=	"회수_발송"	then
				Block4: BEGIN
					DECLARE	done2	INT	DEFAULT	0;
					DECLARE	CONTINUE	HANDLER	FOR	SQLSTATE	'02000'	SET	done2	=	1;
					
					OPEN	c2;
					
					REPEAT
						FETCH	NEXT	FROM	c2
								INTO	_GUBUN,	_PRODCD,	_QTY,	_PRICE,	_PRODCDM;
								
						SELECT	MAX(CONCAT(PRODNM,'_',IFNULL(SPECDES,'')))
						INTO		_PRODNM
						FROM		V_PRODUCTS
						WHERE		COMPNO	=	V_COMPNO
						AND		PRODCD	=	_PRODCD
						AND		BRANDCD	=	V_BRANDCD;
						
						IF	NOT	done2	THEN 
							if	_PRICE	=	0	then
								set _PRICE	=	1;
							END	if;
							
							
							IF	_GUBUN = "2" THEN
								SET	v_maxordseq	=	0;
								SET	v_exists		=	0;
								
								SELECT	MAX(ORDSEQ)
								INTO		v_exists
								FROM		ORDMST
								WHERE		COMPNO	=	V_COMPNO
								AND		ORDDT		=	_CLMDT
								AND		ORDSEQ	=	3
								AND		BRANDCD	=	V_BRANDCD;
								
								IF	v_exists	>	0	then
									SELECT	MAX(SEQ)
									INTO		v_maxordseq
									FROM		ORDDTL
									WHERE		COMPNO	=	V_COMPNO
									AND		ORDDT		=	_CLMDT
									AND		ORDSEQ	=	3
									AND		BRANDCD	=	V_BRANDCD;
									
									
									INSERT	INTO	ORDDTL	(COMPNO,		BRANDCD,		ORDDT,		ORDSEQ,		SEQ,
																	RORDDT,
																	RCVNAM,		ADDR,			CLPNO,		QTY,			SHPFEE,
																	CREDIT,		OPTDESC,		PRODCD,		MESSGE,		ORDNM,
																	ORDAMT,		CLMDT,		CLMSEQ) 
												VALUES			(V_COMPNO,	V_BRANDCD,	_CLMDT,		3,				IFNULL(v_maxordseq,0) + 1,
																	DATE_FORMAT(_REGDT,'%Y-%m-%d %H:%i'),
																	_RCVNAM,		_ADDR,		_CLPNO,		_QTY,			_SUPPAMT,
																	_SUPPNAM,	_PRODNM,		_PRODCD,		_CLMCONTS,	_RESALNAM,
																	_PRICE,		V_CLMDT,		V_CLMSEQ);
								ELSE	
									
									INSERT	INTO	ORDMST	(COMPNO,		BRANDCD,		ORDDT,	ORDSEQ,	INSERTDT,	INSERTID)
												VALUES			(V_COMPNO,	V_BRANDCD,	_CLMDT,	3,			NOW(),		V_INSERTID);
										
									
									INSERT	INTO	ORDDTL	(COMPNO,		BRANDCD,		ORDDT,		ORDSEQ,		SEQ,
																	RORDDT,
																	RCVNAM,		ADDR,			CLPNO,		QTY,			SHPFEE,
																	CREDIT,		OPTDESC,		PRODCD,		MESSGE,		ORDNM,
																	ORDAMT,		CLMDDT,		CLMSEQ) 
												VALUES			(V_COMPNO,	V_BRANDCD,	_CLMDT,		3,				1,
																	DATE_FORMAT(_REGDT,'%Y-%m-%d %H:%i'),
																	_RCVNAM,		_ADDR,		_CLPNO,		_QTY,			_SUPPAMT,
																	_SUPPNAM,	_PRODNM,		_PRODCD,		_CLMCONTS,	_RESALNAM,
																	_PRICE,		V_CLMDT,		V_CLMSEQ);
								END	if;
								
								SET	v_maxrecvseq	=	0;
								
								SELECT	MAX(RECVSEQ)
								INTO		v_maxrecvseq
								FROM		RECVMST
								WHERE		COMPNO	=	V_COMPNO
								AND		RECVDT	=	_CLMDT
								AND		BRANDCD	=	V_BRANDCD;
								
								
								INSERT	INTO	RECVMST	(COMPNO,		BRANDCD,		RECVDT,		RECVSEQ,
																RECVGUBUN,
																ENTDT,		RECVDESC,	BEFQTY,		AUTOCHK,		INSERTDT,	INSERTID) 
											VALUES			(V_COMPNO,	V_BRANDCD,	_CLMDT,		IFNULL(v_maxrecvseq,0) + 1,
																'반품',	
																_REGDT,		_PRODNM,		_QTY,			'1',			NOW(),		V_INSERTID);
																
								
								INSERT	INTO	RECVDTL	(COMPNO,		BRANDCD,		RECVDT,		RECVSEQ,
																SEQ,			PRODCD,		BEFQTY,		INSERTDT,	INSERTID) 
											 VALUES			(V_COMPNO,	V_BRANDCD,	_CLMDT,		IFNULL(v_maxrecvseq,0) + 1,
											 					1,				_PRODCDM,	_QTY,			NOW(),		V_INSERTID);
											 
								# 추가 park 2021-11-19			 					
								select ifnull(max(WARECD),1), ifnull(max(NECCD1),'A'), ifnull(max(NECCD2),1), ifnull(max(NECCD3),1) into v_warecd, v_neccd1, v_neccd2, v_neccd3 
								  from NECPRODMST 
								 where compno=V_COMPNO 
								   and prodcd=_PRODCDM;
								   
								insert into RECVWARE(COMPNO, BRANDCD, RECVDT, RECVSEQ, SEQ, NECSEQ, PRODCD, WARECD, NECCD1, NECCD2, NECCD3, BEFQTY, AFTQTY)
								              values(V_COMPNO,1,_CLMDT, IFNULL(v_maxrecvseq,0)+1, 1, 1, _PRODCDM, v_warecd, v_neccd1, v_neccd2, v_neccd3, _QTY, _QTY);			 					
								
								UPDATE	CLMLIST	
								SET		RECVDT	=	_CLMDT,
											RECVSEQ	=	IFNULL(v_maxrecvseq,0) + 1
								WHERE		COMPNO	=	V_COMPNO
								AND		clmdt		=	V_CLMDT
								AND		clmseq	=	V_CLMSEQ
								AND		BRANDCD	=	V_BRANDCD;
								
								
								UPDATE	CLMLIST
								SET		ORDDT		=	_CLMDT,
											ORDSEQ	=	3,
											SEQ		=	IFNULL(v_maxordseq,0) + 1
								WHERE		COMPNO	=	V_COMPNO
								AND		clmdt		=	V_CLMDT
								AND		clmseq	=	V_CLMSEQ
								AND		BRANDCD	=	V_BRANDCD;

							
							ELSEIF	_GUBUN	=	"1"	THEN
								SET	v_maxrecvseq	=	0;
								
								SELECT	MAX(RECVSEQ)
								INTO		v_maxrecvseq
								FROM		RECVMST
								WHERE		COMPNO	=	V_COMPNO
								AND		RECVDT	=	_CLMDT
								AND		BRANDCD	=	V_BRANDCD;
								
								
								INSERT	INTO	RECVMST	(COMPNO,		BRANDCD,		RECVDT,	RECVSEQ,
																RECVGUBUN,
																ENTDT,		RECVDESC,	BEFQTY,	AUTOCHK,		INSERTDT,	INSERTID) 
											VALUES			(V_COMPNO,	V_BRANDCD,	_CLMDT,	IFNULL(v_maxrecvseq,0) + 1,
																'반품',
																_REGDT,		_PRODNM,		_QTY,		'1',			NOW(),		V_INSERTID);
								
								
								INSERT	INTO	RECVDTL	(COMPNO,		BRANDCD,		RECVDT,	RECVSEQ,
																SEQ,			PRODCD,		BEFQTY,	INSERTDT,	INSERTID) 
											VALUES			(V_COMPNO,	V_BRANDCD,	_CLMDT,	IFNULL(v_maxrecvseq,0)+1,
																1,				_PRODCDM,	_QTY,		NOW(),		V_INSERTID);
											 
								# park 2021-11-19
								select ifnull(max(WARECD),1), ifnull(max(NECCD1),'A'), ifnull(max(NECCD2),1), ifnull(max(NECCD3),1) into v_warecd, v_neccd1, v_neccd2, v_neccd3 
								  from NECPRODMST 
								 where compno=V_COMPNO 
								   and prodcd=_PRODCDM;
								   
								insert into RECVWARE(COMPNO, BRANDCD, RECVDT, RECVSEQ, SEQ, NECSEQ, PRODCD, WARECD, NECCD1, NECCD2, NECCD3, BEFQTY, AFTQTY)
								              values(V_COMPNO,1,_CLMDT, IFNULL(v_maxrecvseq,0)+1, 1, 1, _PRODCDM, v_warecd, v_neccd1, v_neccd2, v_neccd3, _QTY, _QTY);
											  								
								UPDATE	CLMLIST
								SET		RECVDT	=	_CLMDT,
											RECVSEQ	=	IFNULL(v_maxrecvseq,0) + 1
								WHERE		COMPNO	=	V_COMPNO
								AND		clmdt		=	V_CLMDT
								AND		clmseq	=	V_CLMSEQ
								AND		BRANDCD	=	V_BRANDCD;								

							
							ELSEIF	_GUBUN	=	"3"	THEN
								SET	v_maxordseq	=	0;
								SET	v_exists		=	0;
								
								SELECT	MAX(ORDSEQ)
								INTO		v_exists
								FROM		ORDMST
								WHERE		COMPNO	=	V_COMPNO
								AND		ORDDT		=	_CLMDT
								AND		ORDSEQ	=	3
								AND		BRANDCD	=	V_BRANDCD;
								
								IF	v_exists	>	0	then
									SELECT	MAX(SEQ)
									INTO		v_maxordseq
									FROM		ORDDTL
									WHERE		COMPNO	=	V_COMPNO
									AND		ORDDT		=	_CLMDT
									AND		ORDSEQ	=	3
									AND		BRANDCD	=	V_BRANDCD;
									
									
									INSERT	INTO	ORDDTL	(COMPNO,		BRANDCD,		ORDDT,	ORDSEQ,		SEQ,
																	RORDDT,
																	RCVNAM,		ADDR,			CLPNO,	QTY,			SHPFEE,
																	CREDIT,		OPTDESC,		PRODCD,	MESSGE,		ORDNM,
																	ORDAMT,		CLMDT,		CLMSEQ) 
												VALUES			(V_COMPNO,	V_BRANDCD,	_CLMDT,	3, 			IFNULL(v_maxordseq,0)+1,
																	DATE_FORMAT(_REGDT,'%Y-%m-%d %H:%i'),
																	_RCVNAM,		_ADDR,		_CLPNO,	_QTY,			_SUPPAMT,
																	_SUPPNAM,	_PRODNM,		_PRODCD,	_CLMCONTS,	_RESALNAM,
																	_PRICE,		V_CLMDT,		V_CLMSEQ);
								ELSE
									
									INSERT	INTO	ORDMST	(COMPNO,		BRANDCD,		ORDDT,		ORDSEQ,	INSERTDT,	INSERTID)
												VALUES			(V_COMPNO,	V_BRANDCD,	_CLMDT,		3,			NOW(),		V_INSERTID);
												
									
									INSERT	INTO	ORDDTL	(COMPNO,		BRANDCD,		ORDDT,		ORDSEQ,		SEQ,
																	RORDDT,
																	RCVNAM,		ADDR,			CLPNO,		QTY,			SHPFEE,
																	CREDIT,		OPTDESC,		PRODCD,		MESSGE,		ORDNM,
																	ORDAMT,		CLMDT,		CLMSEQ) 
												VALUES			(V_COMPNO,	V_BRANDCD,	_CLMDT,		3,				1,
																	DATE_FORMAT(_REGDT,'%Y-%m-%d %H:%i'),
																	_RCVNAM,		_ADDR,		_CLPNO,		_QTY,			_SUPPAMT,
																	_SUPPNAM,	_PRODNM,		_PRODCD,		_CLMCONTS,	_RESALNAM,
																	_PRICE,		V_CLMDT,		V_CLMSEQ);
								END	if;
								
								
								UPDATE	CLMLIST
								SET		ORDDT		=	_CLMDT,
											ORDSEQ	=	3,
											SEQ		=	IFNULL(v_maxordseq,0) + 1
								WHERE		COMPNO	=	V_COMPNO
								AND		clmdt		=	V_CLMDT
								AND		clmseq	=	V_CLMSEQ
								AND		BRANDCD	=	V_BRANDCD;	
							END	IF;
						END	IF;
					UNTIL	done2	END	REPEAT;
					
					close	c2;
				END	Block4;	
			ELSEIF	_inouttype	=	"회수"	then
				Block3:	BEGIN
					DECLARE	done2	INT	DEFAULT	0;
					DECLARE	CONTINUE	HANDLER	FOR	SQLSTATE	'02000'	SET	done2	=	1;
					
					OPEN	c2;
					
					REPEAT
						FETCH	NEXT	FROM	c2
								INTO	_GUBUN,	_PRODCD,	_QTY,	_PRICE,	_PRODCDM;
								
						SELECT	MAX(CONCAT(PRODNM,'_',IFNULL(SPECDES,'')))
						INTO		_PRODNM
						FROM		V_PRODUCTS
						WHERE		COMPNO	=	V_COMPNO
						AND		PRODCD	=	_PRODCD
						AND		BRANDCD	=	V_BRANDCD;
						
						IF	NOT	done2	THEN 
							if	_PRICE	=	0	then
								SET	_PRICE	=	1;
							END	if;
							
							SET	v_maxrecvseq	=	0;
							
							SELECT	MAX(RECVSEQ)
							INTO		v_maxrecvseq
							FROM		RECVMST
							WHERE		COMPNO	=	V_COMPNO
							AND		RECVDT	=	_CLMDT
							AND		BRANDCD	=	V_BRANDCD;
							
							
							INSERT	INTO	RECVMST	(COMPNO,		BRANDCD,		RECVDT,		RECVSEQ,
															RECVGUBUN,
															ENTDT,		RECVDESC,	BEFQTY,		AUTOCHK,		INSERTDT,	INSERTID) 
										VALUES			(V_COMPNO,	V_BRANDCD,	_CLMDT,		IFNULL(v_maxrecvseq,0)+1,
															'반품',
															_REGDT,		_PRODNM,		_QTY,			'1',			NOW(),		V_INSERTID);
															
							
							INSERT	INTO	RECVDTL	(COMPNO,		BRANDCD,		RECVDT,		RECVSEQ,								SEQ,
															PRODCD,		BEFQTY,		INSERTDT,	INSERTID) 
										VALUES			(V_COMPNO,	V_BRANDCD,	_CLMDT,		IFNULL(v_maxrecvseq,0) + 1,	1,
															_PRODCDM,	_QTY,			NOW(),		V_INSERTID);
							
							# 입출고 창고위치 테이블 삽입 park 2021-11-19
							select ifnull(max(WARECD),1), ifnull(max(NECCD1),'A'), ifnull(max(NECCD2),1), ifnull(max(NECCD3),1) into v_warecd, v_neccd1, v_neccd2, v_neccd3 
							  from NECPRODMST 
							 where compno=V_COMPNO 
							   and prodcd=_PRODCDM;
							   
							insert into RECVWARE(COMPNO, BRANDCD, RECVDT, RECVSEQ, SEQ, NECSEQ, PRODCD, WARECD, NECCD1, NECCD2, NECCD3, BEFQTY, AFTQTY)
							              values(V_COMPNO,1,_CLMDT, IFNULL(v_maxrecvseq,0)+1, 1, 1, _PRODCDM, v_warecd, v_neccd1, v_neccd2, v_neccd3, _QTY, _QTY);
							              
							UPDATE	CLMLIST
							SET		RECVDT	=	_CLMDT,
										RECVSEQ	=	IFNULL(v_maxrecvseq,0) + 1
							WHERE		COMPNO	=	V_COMPNO
							AND		clmdt		=	V_CLMDT
							AND		clmseq	=	V_CLMSEQ
							AND		BRANDCD	=	V_BRANDCD;
						END	IF;
					UNTIL	done2	END	REPEAT;
					
					close	c2;
				END	Block3;	
			END	IF;
		END	IF;
	UNTIL	done	END	REPEAT;
	
	close	c1;
END;;

CREATE DEFINER=`ydwms`@`%` PROCEDURE `YWM_EXCEL_EXPPICUP_VER2`(
	IN `in_para` VARCHAR(200)
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
	DECLARE	start_pos		INT	DEFAULT	0;
	DECLARE	end_pos			INT	DEFAULT	0;
	DECLARE	cut_pos			INT	DEFAULT	0;
	DECLARE	cut_para			VARCHAR(200);
	
	DECLARE	safe_count		INT	DEFAULT	20;	# 20회 이상 반복 구간이 실행되는 것을 방지
	
	DECLARE	cut_compno		INT;
	DECLARE	cut_brandcd		INT;
	DECLARE	cut_orddt		VARCHAR(10);
	DECLARE	cut_ordseq		INT;
	DECLARE	cut_seq_from	INT;
	DECLARE	cut_seq_to		INT;
	
	DECLARE	first_proc		BOOLEAN	DEFAULT	TRUE;
	
	SET	start_pos	=	1;
	SET	end_pos		=	LENGTH(IN_para);
	
	# SET	tmp_table_size			=	1024	*	1024	*	1024	*	2;
	# SET	max_heap_table_size	=	1024	*	1024	*	1024	*	2;
	
	while	start_pos	<	end_pos	do
		SET	cut_pos	=	LOCATE(",", in_para, start_pos);
		
		if	cut_pos	=	0	then
			SET	cut_para		=	SUBSTRING(in_para, start_pos, end_pos);
			SET	start_pos	=	end_pos;
		else
			SET	cut_para		=	SUBSTRING(in_para, start_pos, cut_pos - start_pos);
			SET	start_pos	=	cut_pos	+	1;
		END	if;
		
		# 하나의 주문차수는 30 Byte
		if	LENGTH(cut_para)		=	30	then
			SET	cut_compno		=	CAST(SUBSTRING(cut_para, 1, 5)	AS	UNSIGNED);
			SET	cut_brandcd		=	CAST(SUBSTRING(cut_para, 6, 5)	AS	UNSIGNED);
			SET	cut_orddt		=	SUBSTRING(cut_para, 11, 8);
			SET	cut_ordseq		=	CAST(SUBSTRING(cut_para, 19, 2)	AS	UNSIGNED);
			SET	cut_seq_from	=	CAST(SUBSTRING(cut_para, 21, 5)	AS	UNSIGNED);
			SET	cut_seq_to		=	CAST(SUBSTRING(cut_para, 26, 5)	AS	UNSIGNED);
			
			# TO 순번이 0이면 99999을 강제로 넣어줘 순번 전체를 조회되게 처리
			if	cut_seq_to	=	0	then
				SET	cut_seq_to	=	99999;
			END	if;
			
			# ==========================================
			# 기존 SP(YWM_EXCEL_EXPPICUP) 붙여넣은 부분
			# ==========================================
			YWM_EXCEL_EXPPICUP_Block:	begin
				DROP	TEMPORARY	TABLE	if	EXISTS	TMP;
		
				# 주문상세(ORDDT)와 상품뷰(V_PRODMST) 
				# 주문수량택배번호(EXPORDQTYTAB)와 택배비용(EXPCOST)
				CREATE	TEMPORARY	TABLE	IF	NOT	EXISTS	TMP	ENGINE=MEMORY	AS
					(SELECT	CAST(SUBSTR(orddt, 7, 8) * 10000000 + ordseq * 1000000 + seq AS unsigned)	AS	seq,	LEFT(d.rorddt,10)	AS	rorddt,	d.rcvnam,	replace(d.pstno,'-','')	AS	pstno,	d.addr,	d.clpno,
								d.telno,d.qty,	 
								IFNULL((SELECT	delivamt	
											FROM	EXPORDQTYTAB	a,	EXPCOST	c
											WHERE	a.COMPNO			=	b.COMPNO
											AND	a.brandcd		=	b.brandcd	
											AND	a.prodcd			=	b.prodcdm	
											AND	a.ordqty			=	d.qty
											AND	a.COMPNO			=	c.COMPNO
											AND	a.brandcd		=	c.brandcd
											AND	a.PRODCD			=	c.prodcd
											AND	a.delivtabno	=	c.tabno), 0)	AS	shpfee,
								d.credit,	concat(ifnull(b.expkindnm,''), "◆", TRIM(ifnull(b.prodnm, '')),	TRIM(ifnull(b.specdes,'')), "-", d.qty, "개")	AS expprodnm,
								d.messge,	d.pkgclss,	ifnull(d.shipcls,'택배'),	d.sabordno,	d.shopid,	d.ordnm,	d.etcmsg,	''	AS	mallcd,	d.ordamt,	d.prodcd,
								d.optdesc,	concat(case when IFNULL(b.expbundcd,'') = "" then b.prodcdm else IFNULL(b.expbundcd,'') END ,
								"|",	LEFT(d.orddt,10),"|",d.rcvnam,"|",d.addr,"|",d.clpno)	AS	expbundnm,	0	AS	boxcnt,	ifnull(b.expfile,"기타")	AS expfile,
								ifnull(b.expcd,'')	AS	expcd,	ifnull(b.flagset,'') AS flagset,	b.prodcdm,	IFNULL(b.expinvqty,0)	AS	expinvqty,
								TRIM(ifnull(b.prodnm,''))	AS	prodnm,	ifnull(b.specdes,'')	AS	specdes,
								
								# 주문수량별 배송비 산출을 위해 주문수량 집계용 2019.09.04
								# 모든 update문에 기존 주문수량에 새로운 주문수량을 합산 하는(qty2=qty2+_qty2)계산식 추가함
								d.qty	AS	qty2,	
								
								concat(case when IFNULL(b.expbundcd,'')	=	""	then	b.prodcdm	ELSE	IFNULL(b.expbundcd,'')	END, "|", LEFT(d.orddt,10),"|",d.rcvnam,"|",d.addr,"|",d.clpno)	AS	racknm,
								b.optprodcd	AS	optprodcd,	ifnull(b.expinner,'')	AS	expinner,	ifnull(b.expcostnm,'')	AS	expcostnm,
								(SELECT	ifnull(a.shopcd,'')
								FROM		SHOPINFO	a	
								WHERE		a.SHOPNM	LIKE	CONCAT('%',trim(MID( shopid,1, CHAR_LENGTH(shopid)-INSTR(REVERSE(shopid),' ')+1 )),'%')  limit 1)	AS	shopcd,
								0	AS	shopseq,	trim(MID( shopid, CHAR_LENGTH(shopid)-INSTR(REVERSE(shopid),' ')+1, 20))	AS	shop_userid,
								d.orddt	AS	order_orddt,	d.ordseq	AS	order_ordseq,	d.seq	AS	order_seq,								# (2021.6.15) 주문상세의 주문일자, 차수, 순번을 사용하기 위해 추가
								ifnull(d.clmdt,'') AS orddtl_clmdt, d.clmseq AS orddtl_clmseq
					FROM		ORDDTL d	JOIN	V_PRODUCTS	b	
											ON		d.compno		=	b.compno
											AND	d.brandcd	=	b.brandcd
											AND	d.prodcd		=	b.prodcd
					WHERE		d.compno		=	cut_compno
					AND		d.brandcd	=	cut_brandcd
					AND		d.orddt		=	cut_orddt
					AND		d.ordseq		=	cut_ordseq
					AND		d.seq			>=	cut_seq_from
					AND		d.seq			<=	cut_seq_to
					AND		d.ordamt		<>	0
					AND		d.asigndt	IS	NULL
					AND		ifnull(b.flagpart, '')	<>	'1'							# (2021.6.15) 부품인 경우 제외
					ORDER	BY	expbundnm,	SUBSTR(orddt, 7, 8) * 10000000 + ordseq * 1000000 + seq);
				
				ALTER	TABLE	TMP	MODIFY	expprodnm	VARCHAR(1000);
					
				DROP	TEMPORARY	TABLE	if	EXISTS	TMP2;
				CREATE	TEMPORARY	TABLE	IF	NOT	EXISTS	TMP2	ENGINE=MEMORY	AS
					(SELECT	*
					FROM		TMP	LIMIT	1);
				DELETE	FROM	TMP2;
				
				DROP	TEMPORARY	TABLE	if	EXISTS	TMP3;
				CREATE	TEMPORARY	TABLE	IF	NOT	EXISTS	TMP3(INDEX index_tmp3(racknm))	ENGINE=MEMORY	AS	
					(SELECT	*	
					FROM		TMP	LIMIT	1);
				DELETE	FROM	TMP3;
				
				DROP	TEMPORARY	TABLE	if	EXISTS	TMP4;
				CREATE	TEMPORARY	TABLE	IF	NOT	EXISTS	TMP4(INDEX index_tmp4(rcvnam, addr))	ENGINE=MEMORY	AS
				(SELECT	*
				FROM		TMP	LIMIT	1);
				DELETE	FROM	TMP4;
				
				DROP	TEMPORARY	TABLE	if	EXISTS	TMP5;
				CREATE	TEMPORARY	TABLE	IF	NOT	EXISTS	TMP5(INDEX index_tmp5(rcvnam, addr, expinner))	ENGINE=MEMORY	AS
					(SELECT	*
					FROM		TMP	LIMIT	1);
				DELETE	FROM	TMP5;
				
				if	first_proc	then
					DROP	TEMPORARY	TABLE	if	EXISTS	TMP6;
					CREATE	TEMPORARY	TABLE	IF	NOT	EXISTS	TMP6	ENGINE=MEMORY	AS
					(SELECT	*
					FROM		TMP	LIMIT	1);
					DELETE	FROM	TMP6;
					
					# (2021.6.28) 묶음배송 이동후 번호 초기화(시작)
					# tmp5를 돌면서 ORDDTL 테이블의 묶음배송 이동후 번호를 초기화 시키는 부분이 있던 것을
					# 속도 문제로 한번에 초기화하는 것으로 수정함
					UPDATE	ORDDTL	
					SET		moveseq	=	0
					WHERE		compno	=	cut_compno
					AND		brandcd	=	cut_brandcd
					AND		orddt		=	cut_orddt
					AND		ordseq	=	cut_ordseq
					AND		seq		>=	cut_seq_from
					AND		seq		<=	cut_seq_to
					AND		ordamt	<>	0
					AND		asigndt	IS	NULL;
					# (2021.6.28) 묶음배송 이동후 번호 초기화(종료)
					
					SET	first_proc	=	FALSE;
				END	if;
	
				BEGIN
					DECLARE	_seq				INT;
					DECLARE	_rorddt			VARCHAR(200);
					DECLARE	_rcvnam			VARCHAR(200);
					DECLARE	c_rcvnam			VARCHAR(200);
					DECLARE	_pstno			VARCHAR(200);
					DECLARE	_addr				VARCHAR(200);
					DECLARE	_clpno			VARCHAR(200);
					DECLARE	_telno			VARCHAR(200);
					DECLARE	_qty				INT;
					DECLARE	_qty2				INT;
					DECLARE	_shpfee			BIGINT;
					DECLARE	_credit			VARCHAR(200);
					DECLARE	v_credit			VARCHAR(2000);
					DECLARE	_optdesc			VARCHAR(200);
					DECLARE	_messge			VARCHAR(200);
					DECLARE	_pkgclss			VARCHAR(200);
					DECLARE	_shipcls			VARCHAR(200);
					DECLARE	_sabordno		VARCHAR(200);
					DECLARE	_shopid			VARCHAR(200);
					DECLARE	_ordnm			VARCHAR(200);
					DECLARE	_etcmsg			VARCHAR(200);
					DECLARE	_mallcd			VARCHAR(200);
					DECLARE	_ordamt			INT;
					DECLARE	_prodcd			VARCHAR(200); 
					DECLARE	v_prodnm			VARCHAR(200);
					DECLARE	v_prodcdm		VARCHAR(200);
					DECLARE	_optprodcd		VARCHAR(20); 
					DECLARE	v_optprodcd		VARCHAR(20);
					DECLARE	_specdes			VARCHAR(200);
					DECLARE	_expprodnm		VARCHAR(1000);
					DECLARE	v_expprodnm		VARCHAR(1000);
					DECLARE	_expbundnm		VARCHAR(200);
					DECLARE	_racknm			VARCHAR(200);
					DECLARE	_boxcnt			INT;
					DECLARE	_expfile			VARCHAR(200);
					DECLARE	_expcd			VARCHAR(200);
					DECLARE	_flagset			VARCHAR(200);
					DECLARE	_prodcdm			VARCHAR(200);
					DECLARE	_expinvqty		INT;
					DECLARE	v_qty				INT;
					DECLARE	v_qty2			INT;
					DECLARE	_prodcdnm		VARCHAR(200);
					DECLARE	_flagplus		CHAR(1);
					DECLARE	_flagrack		CHAR(1);
					DECLARE	_rackconts		CHAR(1);
					DECLARE	_instrcnt		CHAR(1);
					DECLARE	_setcnt			INT;
					DECLARE	_levsetcnt		INT;
					DECLARE	_expinner		CHAR(1);
					DECLARE	_expcostnm		CHAR(1);
					DECLARE	_shopcd			VARCHAR(20);
					DECLARE	_shop_userid	VARCHAR(20);
					DECLARE	v_expminbunqty INT;
			      DECLARE	v_specdes		VARCHAR(200);
					DECLARE	v_exists			INT	DEFAULT	0;
					DECLARE	v_exists2		INT	DEFAULT	0;
					DECLARE	v_exists3		INT	DEFAULT	0;
					DECLARE	v_exists5		INT	DEFAULT	0;
					DECLARE	v_addr			VARCHAR(200);
					DECLARE	v_cnt				INT;
					DECLARE	v_rcvnam			VARCHAR(200);
					
					DECLARE	v_existsexpsite	INT	DEFAULT	0;
					DECLARE	_shopseq				INT;
					
					DECLARE	u_ordseq	INT;
					DECLARE	u_seq		INT;
					
					DECLARE	_order_orddt	VARCHAR(10);
					DECLARE	_order_ordseq	INT;
					DECLARE	_order_seq		INT;
					DECLARE	_delivery_type	VARCHAR(10);
					declare	sel_cnt			int;
					
					# (2021.7.14) 변수 추가
					declare	v_orddtl_clmdt		varchar(8)	default	'';
					declare	v_orddtl_clmseq	int			default	0;
					declare	_orddtl_clmdt		varchar(8)	default	'';
					declare	_orddtl_clmseq		int			default	0;
					declare	clmlist_expcd		varchar(50)	default	'';
					declare	clmlist_proctype	varchar(50)	default	'';
					DECLARE	clmlist_suppamt	int			default	0;
					DECLARE	clmlist_boxcnt	int			default	0;
					
					DECLARE	imsi_seq				int			default	0;
					# (2021.7.14) 변수 추가
					
					Block1: BEGIN
						DECLARE	done	INT	DEFAULT	0; 
				   	DECLARE	c1	CURSOR	FOR
							SELECT	*	FROM	TMP;
							
				   	DECLARE	CONTINUE	HANDLER	FOR	SQLSTATE	'02000'	SET	done	=	1;
				   	
				   	OPEN	c1;
						REPEAT
							FETCH	NEXT	FROM	c1	
							INTO	_seq,				_rorddt,			_rcvnam,		_pstno,		_addr,		_clpno,		_telno,			_qty,
									_shpfee,			_credit,			_expprodnm,	_messge,		_pkgclss,	_shipcls,	_sabordno,		_shopid,
									_ordnm,			_etcmsg,			_mallcd,		_ordamt,		_prodcd,		_optdesc,	_expbundnm,		_boxcnt,
									_expfile,		_expcd,			_flagset,	_prodcdm,	_expinvqty,	v_prodnm,	_specdes,		_qty2,
									_racknm,			_optprodcd,		_expinner,	_expcostnm,	_shopcd,		_shopseq,	_shop_userid,
									_order_orddt,	_order_ordseq,	_order_seq, _orddtl_clmdt, _orddtl_clmseq;
									
							IF	NOT	done	THEN
								SET	_delivery_type	=	"택배";
									
								# (2021.7.14) 무임건은 택배사를 '무임'으로 변경
								if	_order_ordseq	=	3	then
									set	sel_cnt	=	0;
									
									set	v_orddtl_clmdt		=	'';
									set	v_orddtl_clmseq		=	0;
									set	clmlist_expcd		=	'';
									set	clmlist_proctype	=	'';
									set	clmlist_suppamt	=	0;
									set	clmlist_boxcnt	   =	0;
									
									#select	max(clmdt),		max(clmseq)
									#into		orddtl_clmdt,	orddtl_clmseq
									#from		ORDDTL
									#where		compno	=	cut_compno
									#and		brandcd	=	cut_brandcd
									#and		orddt		=	_order_orddt
									#and		ordseq	=	_order_ordseq
									#and		seq		=	_order_seq;
									
									select	IFNULL(max(expcd), ''),		IFNULL(max(proctype), ''),	IFNULL(max(suppamt), 0), IFNULL(max(SUPPAKNM), 0)
									into		clmlist_expcd,					clmlist_proctype,				clmlist_suppamt, clmlist_boxcnt
									from		CLMLIST
									where		compno	=	cut_compno
									and		brandcd	=	cut_brandcd
									and		clmdt		=	_orddtl_clmdt
									and		clmseq	=	_orddtl_clmseq;
									
									# (20210901) 상담접수건인 경우 상담접수 시 등록한 택배비로 택배비 설정
									set		_shpfee	=	clmlist_suppamt;
									# (20220208) park 상담접수건인 경우 상담접수 시 등록한 box 설정
									set		_boxcnt	=	clmlist_boxcnt;
									
									# (202107.19) 상담접수/처리에 등록된 택배사로 할당될 수 있도록 처리 (시작)
									if			clmlist_expcd	=	'경동'	then
										set	_expfile	=	'경동택배';
									elseif	clmlist_expcd	=	'롯데'	then
										set	_expfile	=	'롯데택배';
									elseif	clmlist_expcd	=	'한진'	then
										set	_expfile	=	'한진택배';
									elseif	clmlist_expcd	=	'대신'	then
										set	_expfile	=	'합동택배';
										set	_expcd	=	'대신택배';
									elseif	clmlist_expcd	=	'천일'	then
										set	_expfile	=	'합동택배';
										set	_expcd	=	'천일택배';
									elseif	clmlist_expcd	=	''			then
										set	_expfile	=	'공용';
									end	if;
									# (202107.19) 상담접수/처리에 등록된 택배사로 할당될 수 있도록 처리 (끝)
									
									# DELFREEMST 테이블에 택배사와 처리유형이
									# 입력되어 있으면 무임으로 처리
									select	COUNT(*)
									into		sel_cnt
									FROM		DELFREEMST
									WHERE		compno		=	cut_compno
									AND		brandcd		=	cut_brandcd
									AND		expcd			=	clmlist_expcd
									AND		proctypecd	=	clmlist_proctype;
									
									if	sel_cnt	>=	1	then
										set	_expfile	=	'무임';
									end	if;
									
									# (2021.08.09) 배송방법이 '직송'인 경우 '택배사를 '공용'으로 변경
									SELECT	IFNULL(max(deliverytype), '택배')
									INTO		_delivery_type
									FROM		PROCTYPE
									WHERE		compno		=	cut_compno
									AND		brandcd		=	cut_brandcd
									AND		proctypecd	=	clmlist_proctype;
									
									if	_delivery_type	=	'직송'	then
										set	_expfile	=	'공용';
									end	if;
								end	if;
								# (2021.7.14) 무임건은 택배사를 '공용'으로 변경
							
								if	_delivery_type	=	"택배"	or	_delivery_type	=	"직송"	then
									SET	v_exists	=	v_exists	+	1;
									 
									if			_shopid	LIKE	"%롯데쇼핑%"	then	SET	v_credit	=	"선불";
									ELSEIF	left(_prodcd , 3)	=	"YYW"	then	SET	v_credit =	"선불"; 
									ELSEIF	_credit	=		""				then	SET	v_credit	=	"착불";
									ELSEIF	_credit	LIKE	"%유료%"		or
												_credit	LIKE	"%무료%"		or
												_credit	LIKE	"%선결제%"	then	SET	v_credit	=	"선불"; 
									ELSE	SET	v_credit	=	_credit;
									END	if;
									 
							   	if	_expcd	=	"천일택배"	OR 	_expcd = "대신택배" then
									   if v_credit = "선불" then
											set v_credit ="현불"; 
										end if;
									end if;
									
									if _expcostnm = '1' then 
										set v_credit ="현불";
									end if;
									
									#----------
									# 설명 필요
									#----------
									if	ifnull(_shopcd,'')	<>	''	AND	ifnull(_shop_userid,'')	<>	''	then 
									   #SELECT _shopcd,_shop_userid;
										SELECT	EXISTS(SELECT	1	
															FROM	SHOPDTL
															WHERE	compno		=	cut_compno
															AND	shopcd		=	_shopcd
															AND	shoppingid	=	_shop_userid)
										INTO		_shopseq;
															
										if	ifnull(_shopseq, 0)	<>	0	then 
											SET	v_existsexpsite	=	0;
											
											#SELECT _shopcd,_shop_userid;
											SELECT	MIN(shopseq)	
											INTO		_shopseq
											FROM		SHOPDTL
											WHERE		compno		=	cut_compno
											AND		shopcd		=	_shopcd
											AND		shoppingid	=	_shop_userid;
											
											SELECT	EXISTS(SELECT	1
																FROM	EXPSITE
																WHERE	COMPNO	=	cut_compno
																AND	BRANDCD	=	cut_brandcd
																AND	PRODCD	=	_prodcdm
																AND	shopcd	=	_shopcd
																AND	shopseq	=	_shopseq)
											INTO		v_existsexpsite; 
																
											if	v_existsexpsite	<>	0	then 
												SET	v_credit	=	"착불";
											END	if;
										END	if;
									END	if;
									
							   	SET	_instrcnt	=	'';
							   	
							   	# 택배정보(EXPPRODMST)의 주문수량별택배테이블적용여부(1, EXPOUTNM)
									SELECT	ifnull(EXPOUTNM, '')
									INTO		_instrcnt
									FROM		EXPPRODMST
									WHERE		COMPNO	=	cut_compno
									AND		BRANDCD	=	cut_brandcd
									AND		PRODCD	=	_prodcdm;
							   	
							   	# 상담접수건은 별도처리 2022-02-10 park
							   	if	_order_ordseq	=	3	then
										SET	v_qty	=	_boxcnt;
							   	ELSEif	ifnull(_instrcnt, '')	=	'1'	then
										SET	v_qty	=	_qty;
									ELSEIF	ifnull(_flagset,'')		=	'1'	then
										SET	_setcnt			=	0;
										SET	_levsetcnt		=	0;
										SET	v_expminbunqty	=	0;
										
										# 세트여부(PRODDTL.FLAGSET)(1)
										SELECT	COUNT(*)	
										INTO		_setcnt
										FROM		PRODDTL
										WHERE		COMPNO	=	cut_compno
										AND		BRANDCD	=	cut_brandcd
										AND		prodcd	=	_prodcdm
										AND		ifnull(flagset,'')	=	'1';
										
										# 세트레벨(PRODDTL.LEVSET)
										SELECT	count(*)
										INTO		_levsetcnt
										FROM		PRODDTL
										WHERE		COMPNO	=	cut_compno
										AND		BRANDCD	=	cut_brandcd
										AND		prodcd	=	_prodcdm
										AND		ifnull(levset,'')	=	'2';
										
										# 합동택배 이관 최소 수량(EXPPRODMST.EXPMINBUNQTY)
										SELECT	expminbunqty
										INTO		v_expminbunqty
										FROM		EXPPRODMST
										WHERE		COMPNO	=	cut_compno
										AND		BRANDCD	=	cut_brandcd
										AND		prodcd	=	_prodcdm;
										
										if			ifnull(v_expminbunqty, 0)	=	9	then
											SET	v_qty	=	_qty		*	_expinvqty;
										ELSEIF	ifnull(_levsetcnt, 0)		>	0	then
											SET	v_qty	=	_setcnt	-	1;
										else
											SET	v_qty	=	_setcnt;
										END	if;
									ELSE
										SET	v_qty	=	_qty	* _expinvqty;
									END	if;
							   	
									SET	v_exists2	=	0;
									
									SELECT	max(seq)	
									INTO		v_exists2
									FROM		TMP2
									WHERE		expbundnm	=	_expbundnm;
									
									IF	ifnull(v_exists2, 0)	>	0	THEN
										SELECT expprodnm,prodcdm,qty,ifnull(optprodcd,'None'),orddtl_clmdt,orddtl_clmseq
										  INTO v_expprodnm,v_prodcdm,v_qty2,v_optprodcd,v_orddtl_clmdt,v_orddtl_clmseq
										  FROM TMP2
										 WHERE seq = v_exists2;
										
										# 합배송대상이면서 상담번호가 같으면 
										# 2022-03-04 park 상담상품은 묶는과정에서 수량이 합해지는것 방지
										if _order_ordseq = 3 and _orddtl_clmdt=v_orddtl_clmdt and _orddtl_clmseq=v_orddtl_clmseq then
											set v_qty = 0;
										end if;
										#
										# 세트 상품이면서 
										# 송장수량변경 : 배송관리의 '합동택배이관최소수량' 이 '9' 이면 Rule 1. or Rule 2 로 변경 적용
										#                (즉 특정상품 이면 배송관리의 '주문수량별 택배테이블'의 '주문수량' 에 해당 하는 '테이블번호' 아니면 배송관리의 
										#                 송장수량' * 주문수량 )
										#
										#if	ifnull(_flagset, '')	=	'1'	AND	ifnull(v_expminbunqty, 0)	<>	9	then
										if _order_ordseq	<>	3 and ifnull(_flagset, '')	=	'1' AND ifnull(v_expminbunqty, 0) <> 9	then
											SET	_setcnt		=	0;
											SET	_levsetcnt	=	0;
											
											# 세트여부(PRODDTL.FLAGSET)
											SELECT	count(*)
											INTO		_setcnt
											FROM		PRODDTL
											WHERE		COMPNO	=	cut_compno
											AND		BRANDCD	=	cut_brandcd
											AND		prodcd	=	_prodcdm
											AND		ifnull(flagset, '')	=	'1';
											
											# 세트레벨(PRODDTL.LEVSET)
											SELECT	count(*)
											INTO		_levsetcnt
											FROM		PRODDTL
											WHERE		COMPNO	=	cut_compno
											AND		BRANDCD	=	cut_brandcd
											AND		prodcd	=	_prodcdm
											AND		ifnull(levset,'')	=	'2';
											
											#주문수량별 택배테이블 적용시  
											if	ifnull(_levsetcnt, 0)	>	0	then
												# 규격(PRODMST.SPECDES)
												SELECT	ifnull(specdes, '')
												INTO		v_specdes
												FROM		PRODMST
												WHERE		COMPNO	=	cut_compno
												AND		BRANDCD	=	cut_brandcd
												AND		prodcd	=	_prodcdm;
												
												SET	_expprodnm	=	concat(v_expprodnm, ",", ifnull(v_specdes,''));
											   SET	_setcnt		=	_setcnt	-	1;
											   
											   UPDATE	TMP2
												SET		qty2			=	qty2	+	_qty2,
															qty			=	qty	+	ifnull(_setcnt, 1),
															expprodnm	=	ifnull(_expprodnm, '')
												WHERE		seq			=	v_exists2;
											ELSE
												if	_prodcdm	=	v_prodcdm	then
													SET	_expprodnm	=	concat(v_expprodnm,",",_specdes,"-",_qty,"개");
												else	
													SET	_expprodnm	=	concat(v_expprodnm, ",", v_prodnm, "_", ifnull(_specdes,''), "-", _qty, "개");
												end if;
												
												UPDATE	TMP2
												SET		qty2			=	qty2	+	_qty2,
															qty			=	qty	+	ifnull(_setcnt, 1),
															expprodnm	=	_expprodnm
												WHERE		seq			=	v_exists2;
											end if;
										else	
											# 대표상품, 옵션상품코드 같은 경우 동일상품이므로 칼라/사이즈만 찍는다 
											if	_prodcdm	=	v_prodcdm	then
												SET	_expprodnm	=	concat(v_expprodnm, ",", _specdes, "-", _qty, "개");
											else
												SET	_expprodnm	=	concat(v_expprodnm, ",", v_prodnm, "_", ifnull(_specdes, ''), "-", _qty, "개");
											end if;
											
											UPDATE	TMP2
											SET		qty2			=	qty2	+	_qty2,
														qty			=	qty	+	v_qty,
														expprodnm	=	_expprodnm
											WHERE		seq			=	v_exists2;				
										end if;
										
										# (2021.9.17) 수정 (시작)
										set	imsi_seq	=	_seq	-	(SUBSTR(cut_orddt, 7, 8) * 10000000);
										# (2021.9.17) 수정 (끝)
										
										# (2021.9.17) (우측 코드를 아래로 수정) SELECT	FLOOR((_seq) / 1000000),	MOD(_seq, 1000000)
										SELECT	FLOOR((imsi_seq) / 1000000),	MOD(imsi_seq, 1000000)
										INTO		u_ordseq,							u_seq;
										
										UPDATE	ORDDTL
										SET		moveseq	=	v_exists2
										WHERE		compno	=	cut_compno
										AND		brandcd	=	cut_brandcd
										AND		orddt		=	cut_orddt
										AND		ordseq	=	u_ordseq
										AND		seq		=	u_seq;
									ELSE
										INSERT	INTO	TMP2
										VALUES	(_seq,			_rorddt,			_rcvnam,		_pstno,		_addr,		_clpno,		_telno,			v_qty,
										 			_shpfee,			v_credit,		_expprodnm,	_messge,		_pkgclss,	_shipcls,	_sabordno,		_shopid,
													_ordnm,			_etcmsg,			_mallcd,		_ordamt,		_prodcd,		_optdesc,	_expbundnm,		_boxcnt,
													_expfile,		_expcd,			_flagset,	_prodcdm,	_expinvqty,	v_prodnm,	_specdes,		_qty2,
													_racknm,			_optprodcd,		_expinner,	_expcostnm,	_shopcd,		_shopseq,	_shop_userid,
													_order_orddt,	_order_ordseq,	_order_seq, _orddtl_clmdt, _orddtl_clmseq);
									END	IF;
								END	if;
							END	IF;
						UNTIL	done	END	REPEAT;
						
					   CLOSE	c1;
				   END	Block1;
				   
				   #배송관리-> "주문수량테이블 적용여부"에 체크된 상품들의 경우 출고수량(box)를 테이블에 맞게 변경함 
					Block2: BEGIN
				   	DECLARE	done2	INT	DEFAULT	0;
				   		
				   	DECLARE	c2	CURSOR	FOR
							SELECT	*	
							FROM		TMP2;
							
					   DECLARE	CONTINUE	HANDLER	FOR	SQLSTATE	'02000'	SET	done2	=	1;
					   
					   OPEN	c2;
					   	
						REPEAT
							FETCH	NEXT	FROM	c2
							INTO	_seq,				_rorddt,			_rcvnam,		_pstno,		_addr,		_clpno,		_telno, 			_qty,
									_shpfee,			_credit,			_expprodnm,	_messge,		_pkgclss,	_shipcls,	_sabordno,		_shopid,
									_ordnm,			_etcmsg,			_mallcd,		_ordamt, 	_prodcd,		_optdesc,	_expbundnm, 	_boxcnt,	
									_expfile,		_expcd,			_flagset,	_prodcdm,	_expinvqty,	v_prodnm,	_specdes, 		_qty2,
									_racknm,			_optprodcd,		_expinner,	_expcostnm,	_shopcd,		_shopseq,	_shop_userid,
									_order_orddt,	_order_ordseq,	_order_seq, _orddtl_clmdt, _orddtl_clmseq;
										
							IF	NOT	done2	THEN
								SET	_instrcnt	=	'';
								
								# 택배정보(EXPPRODMST)의 주문수량별택배테이블적용여부(1)(EXPOUTNM)
								SELECT	ifnull(EXPOUTNM, '')	
								INTO		_instrcnt
								FROM		EXPPRODMST
								WHERE		COMPNO	=	cut_compno
								AND		BRANDCD	=	cut_brandcd
								AND		PRODCD	=	_prodcdm;
									
								# 택배정보(EXPPRODMST)의 랙제품 유무(FLAGRACK)
								#랙제품 준비 
								#이름+주소+랙제품 유무 통합 항목 만들기  
								SELECT	max(FLAGRACK)
								INTO		_flagrack
								FROM		EXPPRODMST
								WHERE		COMPNO	=	cut_compno
								AND		BRANDCD	=	cut_brandcd
								AND		PRODCD	=	_prodcdm;
									
								SET	_racknm	=	concat(_rcvnam, "|", _addr, "|", ifnull(_flagrack,'') );
									
								SET	v_qty	=	0;
								
								# 상담접수건은 별도처리 2022-02-10 park
							   if	_order_ordseq	=	3	then
									SET	v_qty	=	_qty;
								elseif	ifnull(_instrcnt, '')	=	'1'	then
									SET	v_qty	=	0;
									SELECT	sum(delivtabno)
									INTO		v_qty
									FROM		EXPORDQTYTAB
									WHERE		COMPNO	=	cut_compno
									AND		BRANDCD	=	cut_brandcd
									AND		prodcd	=	_prodcdm
									AND		ordqty	=	_qty;
								else
									SET	v_qty	=	_qty;
								END	if;
									
								INSERT	INTO	TMP3
								VALUES	(_seq,			_rorddt,			_rcvnam,		_pstno,		_addr,		_clpno,		_telno,			ifnull(v_qty,0),
							 				_shpfee,			_credit,			_expprodnm,	_messge,		_pkgclss,	_shipcls,	_sabordno,		_shopid, 
											_ordnm,			_etcmsg,			_mallcd,		_ordamt,		_prodcd,		_optdesc,	_expbundnm, 	_boxcnt, 
											_expfile,		_expcd,			_flagset,	_prodcdm,	_expinvqty,	v_prodnm,	_specdes,		_qty2,
											_racknm,			_optprodcd,		_expinner,	_expcostnm,	_shopcd,		_shopseq,	_shop_userid,
											_order_orddt,	_order_ordseq,	_order_seq, _orddtl_clmdt, _orddtl_clmseq);
							END	IF;
						UNTIL	done2	END	REPEAT;
						
						close c2;
					END	Block2;
					
					#랙 제품중 본품이 1개 인경우 포함배송(선반,기타) 처리함 
					Block3:	BEGIN
						DECLARE	done3	INT	DEFAULT	0;
				   	DECLARE	c3	CURSOR	FOR
							SELECT	*
							FROM		TMP3
							ORDER	BY	racknm;
											
					   DECLARE	CONTINUE	HANDLER	FOR	SQLSTATE	'02000'	SET	done3	=	1;
					   
					   OPEN	c3;
					   	
						REPEAT
						  	FETCH	NEXT	FROM	c3
							INTO	_seq,				_rorddt,			_rcvnam,		_pstno,		_addr,		_clpno,		_telno,			_qty,
									_shpfee,			_credit,			_expprodnm,	_messge,		_pkgclss,	_shipcls,	_sabordno,		_shopid,
									_ordnm,			_etcmsg,			_mallcd,		_ordamt,		_prodcd,		_optdesc,	_expbundnm, 	_boxcnt,
									_expfile,		_expcd,			_flagset,	_prodcdm,	_expinvqty,	v_prodnm,	_specdes,		_qty2, 
									_racknm,			_optprodcd,		_expinner,	_expcostnm,	_shopcd,		_shopseq,	_shop_userid,
									_order_orddt,	_order_ordseq,	_order_seq, _orddtl_clmdt, _orddtl_clmseq;
										
							IF	NOT	done3	THEN
								SET	_flagrack	=	'';
								SET	_rackconts	=	'';
									
								SELECT	max(FLAGRACK),	max(rackconts)
								INTO		_flagrack,		_rackconts
								FROM		EXPPRODMST
								WHERE		COMPNO	=	cut_compno
								AND		BRANDCD	=	cut_brandcd
								AND		PRODCD	=	_prodcdm;
								
								IF	ifnull(_flagrack,'')		=	'1'	AND	
									ifnull(_rackconts,'')	<>	'0'	and 
									_qty							=	1		then
									SET	v_exists3	=	0;
										
									SELECT	max(seq)
									INTO		v_exists3
									FROM		TMP4
									WHERE		racknm	=	_racknm;
										
									IF	ifnull(v_exists3,0)	>	0	THEN
										SET	v_qty2	=	0;
											
										SELECT	sum(qty),	max(expprodnm)
										INTO		v_qty2,		v_expprodnm
										FROM		TMP4
										WHERE		seq	=	v_exists3;
											
										if	ifnull(v_qty2, 0)	=	1	then 
											SET	_expprodnm	=	concat(v_expprodnm, ",", v_prodnm, "_", ifnull(_specdes,''), "-", _qty2, "개");
												
											UPDATE	TMP4
											SET		qty2			=	qty2	+	_qty2, 
														expprodnm	=	_expprodnm
											WHERE		seq			=	v_exists3;	
												
											# (2021.9.17) 수정 (시작)
											set	imsi_seq	=	_seq	-	(SUBSTR(cut_orddt, 7, 8) * 10000000);
											# (2021.9.17) 수정 (끝)
										
											# (2021.9.17) (우측 코드를 아래로 수정) SELECT	FLOOR((_seq) / 1000000),	MOD(_seq, 1000000)
											SELECT	FLOOR((imsi_seq) / 1000000),	MOD(imsi_seq, 1000000)
											INTO		u_ordseq,							u_seq;
												
											UPDATE	ORDDTL	
											SET		moveseq	=	v_exists3
											WHERE		compno	=	cut_compno
											AND		brandcd	=	cut_brandcd
											AND		orddt		=	cut_orddt
											AND		ordseq	=	u_ordseq
											AND		seq		=	u_seq;
										else 
											INSERT	INTO	TMP4
											VALUES	(_seq,			_rorddt,			_rcvnam,		_pstno,		_addr,		_clpno,		_telno,			_qty,
														_shpfee,			_credit,			_expprodnm,	_messge,		_pkgclss,	_shipcls,	_sabordno,		_shopid,
														_ordnm,			_etcmsg,			_mallcd,		_ordamt,		_prodcd,		_optdesc,	_expbundnm, 	_boxcnt,
														_expfile,		_expcd,			_flagset,	_prodcdm,	_expinvqty,	v_prodnm,	_specdes,		_qty2,
														_racknm,			_optprodcd,		_expinner,	_expcostnm,	_shopcd,		_shopseq,	_shop_userid,
														_order_orddt,	_order_ordseq,	_order_seq, _orddtl_clmdt, _orddtl_clmseq);
										END	if;
									else 
										INSERT	INTO	TMP4
										VALUES	(_seq,			_rorddt,			_rcvnam,		_pstno,		_addr,		_clpno,		_telno,			_qty,
													_shpfee,			_credit,			_expprodnm,	_messge,		_pkgclss,	_shipcls,	_sabordno,		_shopid,
													_ordnm,			_etcmsg,			_mallcd,		_ordamt,		_prodcd,		_optdesc,	_expbundnm, 	_boxcnt,
													_expfile,		_expcd,			_flagset,	_prodcdm,	_expinvqty,	v_prodnm,	_specdes,		_qty2,
													_racknm,			_optprodcd,		_expinner,	_expcostnm,	_shopcd,		_shopseq,	_shop_userid,
													_order_orddt,	_order_ordseq,	_order_seq, _orddtl_clmdt, _orddtl_clmseq);
									END	if;
								else  
									INSERT	INTO	TMP4
									VALUES	(_seq,			_rorddt,			_rcvnam,		_pstno,		_addr,		_clpno,		_telno,			_qty,
												_shpfee,			_credit,			_expprodnm,	_messge,		_pkgclss,	_shipcls,	_sabordno,		_shopid, 
												_ordnm,			_etcmsg,			_mallcd,		_ordamt,		_prodcd,		_optdesc,	_expbundnm, 	_boxcnt, 
												_expfile,		_expcd,			_flagset,	_prodcdm,	_expinvqty,	v_prodnm,	_specdes,		_qty2,
												_racknm,			_optprodcd,		_expinner,	_expcostnm,	_shopcd,		_shopseq,	_shop_userid,
												_order_orddt,	_order_ordseq,	_order_seq, _orddtl_clmdt, _orddtl_clmseq);
								END	if;
							END	IF;
						UNTIL	done3	END	REPEAT;
							
						close	c3;
					END	Block3;
				   
					#동명 2인 의 이름에 유일성 부여
					Block4: BEGIN
						DECLARE	done4	INT	DEFAULT	0;
				   		
				   	DECLARE	c4	CURSOR	FOR
							SELECT	*
							FROM		TMP4
										ORDER	BY	rcvnam, addr;
											
					   DECLARE	CONTINUE	HANDLER	FOR	SQLSTATE	'02000'	SET	done4	=	1;
					   	
						SET	v_rcvnam	=	'';
						SET	v_addr	=	'';
						SET	v_cnt		=	0;
							
						OPEN	c4;
							
						REPEAT
							FETCH	NEXT	FROM	c4
							INTO	_seq,				_rorddt,			_rcvnam,		_pstno,		_addr,		_clpno,		_telno,			_qty,
									_shpfee,			_credit,			_expprodnm,	_messge,		_pkgclss,	_shipcls,	_sabordno,		_shopid,
									_ordnm,			_etcmsg,			_mallcd,		_ordamt,		_prodcd,		_optdesc,	_expbundnm,		_boxcnt,
									_expfile,		_expcd,			_flagset,	_prodcdm,	_expinvqty,	v_prodnm,	_specdes,		_qty2, 
									_racknm,			_optprodcd,		_expinner,	_expcostnm,	_shopcd,		_shopseq,	_shop_userid,
									_order_orddt,	_order_ordseq,	_order_seq, _orddtl_clmdt, _orddtl_clmseq;
										
							IF	NOT	done4	THEN
								if	_rcvnam	=	v_rcvnam	then 
									if	_addr	<>	v_addr	then
										SET	v_cnt		=	v_cnt	+	1;
										SET	c_rcvnam	=	concat( _rcvnam, "_", cast(v_cnt as char(2)) );
										SET	v_addr	=	_addr;
									END	if;
								else 
									SET	v_rcvnam	=	_rcvnam;
									SET	v_addr	=	_addr;
									SET	v_cnt		=	0;
									SET	c_rcvnam	=	_rcvnam;
								END	if;
									
								INSERT	INTO	TMP5
								VALUES	(_seq,			_rorddt,			c_rcvnam,	_pstno,		_addr,		_clpno,		_telno,			_qty,
											_shpfee,			_credit,			_expprodnm,	_messge,		_pkgclss,	_shipcls,	_sabordno,		_shopid, 
											_ordnm,			_etcmsg,			_mallcd,		_ordamt,		_prodcd,		_optdesc,	_expbundnm,		_boxcnt, 
											_expfile,		_expcd,			_flagset,	_prodcdm,	_expinvqty,	v_prodnm,	_specdes,		_qty2,
											_racknm,			_optprodcd,		_expinner,	_expcostnm,	_shopcd,		_shopseq,	_shop_userid,
											_order_orddt,	_order_ordseq,	_order_seq, _orddtl_clmdt, _orddtl_clmseq);
							END	IF;
						UNTIL	done4	END	REPEAT;
						
						close	c4;
					END	Block4;
					
					# 타제품과 포함배송 여부가 있는 상품 처리
					Block5:	BEGIN
						DECLARE	done5	INT	DEFAULT	0;
						
						DECLARE	c5	CURSOR	FOR
							SELECT	*	
							FROM		TMP5
										ORDER	BY	rcvnam,	addr,	expinner;
										
						DECLARE	CONTINUE	HANDLER	FOR	SQLSTATE	'02000'	SET	done5	=	1;
						
					   OPEN	c5;
					   
						REPEAT
							FETCH	NEXT	FROM	c5
							INTO	_seq,				_rorddt,			_rcvnam,		_pstno,		_addr,		_clpno,		_telno,			_qty,
									_shpfee,			_credit,			_expprodnm,	_messge,		_pkgclss,	_shipcls,	_sabordno,		_shopid,
									_ordnm,			_etcmsg,			_mallcd,		_ordamt,		_prodcd,		_optdesc,	_expbundnm,		_boxcnt,
									_expfile,		_expcd,			_flagset,	_prodcdm,	_expinvqty,	v_prodnm,	_specdes,		_qty2, 
									_racknm,			_optprodcd,		_expinner,	_expcostnm,	_shopcd,		_shopseq,	_shop_userid,
									_order_orddt,	_order_ordseq,	_order_seq, _orddtl_clmdt, _orddtl_clmseq;
									
							IF	NOT	done5	THEN
								if	_expinner	=	'1'	then
									SET	v_exists5	=	0;
										
									SELECT	min(seq),	min(expprodnm)
									INTO		v_exists5,	v_expprodnm
									FROM		TMP6
									WHERE		rcvnam	=	_rcvnam
									AND		addr		=	_addr;
										
									#select concat("step1=",v_exists5);
									IF	ifnull(v_exists5, 0)	>	0	THEN
										SET	_expprodnm	=	concat(v_expprodnm, ",", v_prodnm, "_", ifnull(_specdes,''), "-", _qty, "개");
											
										UPDATE	TMP6
										SET		qty2			=	qty2	+	_qty2,
													expprodnm	=	_expprodnm
										WHERE		seq			=	v_exists5;
											
										# (2021.9.17) 수정 (시작)
										set	imsi_seq	=	_seq	-	(SUBSTR(cut_orddt, 7, 8) * 10000000);
										# (2021.9.17) 수정 (끝)
										
										# (2021.9.17) (우측 코드를 아래로 수정) SELECT	FLOOR((_seq) / 1000000),	MOD(_seq, 1000000)
										SELECT	FLOOR((imsi_seq) / 1000000),	MOD(imsi_seq, 1000000)
										INTO		u_ordseq,							u_seq;
											
										UPDATE	ORDDTL	
										SET		moveseq	=	v_exists5
										WHERE		compno	=	cut_compno
										AND		brandcd	=	cut_brandcd
										AND		orddt		=	cut_orddt
										AND		ordseq	=	u_ordseq
										AND		seq		=	u_seq;
									else
										INSERT	INTO	TMP6
										VALUES	(_seq,			_rorddt,			_rcvnam,		_pstno,		_addr,		_clpno,		_telno,			_qty,
													_shpfee,			_credit,			_expprodnm,	_messge,		_pkgclss,	_shipcls,	_sabordno,		_shopid,
													_ordnm,			_etcmsg,			_mallcd,		_ordamt,		_prodcd,		_optdesc,	_expbundnm,		_boxcnt,
													_expfile,		_expcd,			_flagset,	_prodcdm,	_expinvqty,	v_prodnm,	_specdes,		_qty2,
													_racknm,			_optprodcd,		_expinner,	_expcostnm,	_shopcd,		_shopseq,	_shop_userid,
													_order_orddt,	_order_ordseq,	_order_seq, _orddtl_clmdt, _orddtl_clmseq);
									END	if;
								else
									INSERT	INTO	TMP6
									VALUES	(_seq,			_rorddt,			_rcvnam,		_pstno,		_addr,		_clpno,		_telno,			_qty,
												_shpfee,			_credit,			_expprodnm,	_messge,		_pkgclss,	_shipcls,	_sabordno,		_shopid, 
												_ordnm,			_etcmsg,			_mallcd,		_ordamt,		_prodcd,		_optdesc,	_expbundnm,		_boxcnt, 
												_expfile,		_expcd,			_flagset,	_prodcdm,	_expinvqty,	v_prodnm,	_specdes,		_qty2,
												_racknm,			_optprodcd,		_expinner,	_expcostnm,	_shopcd,		_shopseq,	_shop_userid,
												_order_orddt,	_order_ordseq,	_order_seq, _orddtl_clmdt, _orddtl_clmseq);
								END	if;
							END	IF;
						UNTIL	done5	END	REPEAT;
							
						close	c5;
					END	Block5;
					
					# 주문수량으로 택배가격 결정 하는 상품 별도처리 
					Block6:	BEGIN
						DECLARE	v_delivamt	INT	DEFAULT 0;
						DECLARE	done6			INT	DEFAULT	0;
						
				   	DECLARE	c6	CURSOR	FOR
							SELECT	a.seq,	a.prodcdm,	a.qty2
							FROM		TMP6	a,	EXPPRODMST	b 
							WHERE		a.prodcdm	=	b.prodcd 
							AND      a.order_ordseq <> 3
							AND		IFNULL(b.EXPTABLEFLAG,'')	=	'2'
							AND		b.COMPNO		=	cut_compno
							AND		b.BRANDCD	=	cut_brandcd;
							
					   DECLARE	CONTINUE	HANDLER	FOR	SQLSTATE	'02000'	SET	done6	=	1;
					   	
					   OPEN	c6;
					   	
					   REPEAT
						  	FETCH	NEXT	FROM	c6
							INTO	_seq,	_prodcdm,	_qty2;
								
							IF	NOT	done6	THEN
								SET	v_delivamt	=	0;
									
								SELECT	ifnull(delivamt,0)
								INTO		v_delivamt
								FROM		EXPCOST
								WHERE		COMPNO	=	cut_compno
								AND		BRANDCD	=	cut_brandcd
								AND		prodcd	=	_prodcdm
								AND		TABNO		=	_qty2;
									
								UPDATE	TMP6
								SET		shpfee	=	v_delivamt
								WHERE		seq		=	_seq;
							END	IF;
						UNTIL	done6	END	REPEAT;
							
						close	c6;
					END	Block6;		
				END;
			END	YWM_EXCEL_EXPPICUP_Block;
			#=====================================
			# 기존 SP(YWM_EXCEL_EXPPICUP) 영역 끝
			#====================================
			
			# 무한루프 방지
			SET	safe_count	=	safe_count	-	1;
			
			if		safe_count	<=	0	then
				SET	start_pos	=	1;
				SET	end_pos		=	0;
			END	if;
		END	if;
	END	while;
	
	SELECT a.seq,a.rorddt,a.rcvnam,a.pstno,a.addr,a.clpno,a.telno,a.qty,a.shpfee,a.credit,a.expprodnm,a.messge,a.pkgclss,a.shipcls,a.sabordno,a.shopid,a.ordnm,a.etcmsg,a.mallcd,a.ordamt,a.prodcd,a.optdesc,
	       a.expbundnm,a.boxcnt,a.expfile,
		    a.expcd,a.flagset,a.prodcdm,a.expinvqty,a.prodnm,a.specdes,a.qty2,a.racknm,a.optprodcd,a.expinner,a.expcostnm,a.shopcd,a.shopseq,a.shop_userid,a.order_orddt,a.order_ordseq,a.order_seq,	
		    ifnull(b.neccd1,''),ifnull(b.neccd2,''),ifnull(b.neccd3,'')
	FROM		TMP6	a	LEFT	JOIN	NECPRODMST	b	
							ON		b.compno		=	cut_compno
							AND	b.BRANDCD	=	cut_brandcd
							AND	a.prodcdm	=	b.prodcd;
END;;

CREATE DEFINER=`ydwms`@`%` PROCEDURE `YWM_RACKPICUP_LIST_VER2`(
	IN `in_order_asign` VARCHAR(10),
	IN `in_gubun` VARCHAR(10),
	IN `in_para` VARCHAR(200)
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
	DECLARE	start_pos		INT	DEFAULT	0;
	DECLARE	end_pos			INT	DEFAULT	1;
	DECLARE	cut_pos			INT	DEFAULT	0;
	DECLARE	cut_para			vARCHAR(200);
	
	DECLARE	safe_count		INT	DEFAULT	20; /* 20회 이상 반복 구간이 실행되는 것을 방지 */
	
	DECLARE	cut_compno		INT;
	DECLARE	cut_brandcd		INT;
	DECLARE	cut_orddt		VARCHAR(10);
	DECLARE	cut_ordseq		INT;
	DECLARE	cut_seq_from	INT;
	DECLARE	cut_seq_to		INT;
	
	DECLARE	first_proc		BOOLEAN	DEFAULT	true;

	SET	start_pos	=	1;
	SET	end_pos		=	LENGTH(in_para);

	while	start_pos	<	end_pos	do
		SET	cut_pos	=	LOCATE(",", in_para, start_pos);
			
		if	cut_pos	=	0	then
			SET	cut_para		=	SUBSTRING(in_para, start_pos, end_pos);
			SET	start_pos	=	end_pos;
		else
			SET	cut_para		=	SUBSTRING(in_para, start_pos, cut_pos - start_pos);
			SET	start_pos	=	cut_pos	+	1;
		END	if;
		
		# 주문 차수에 대한 유효성 체크 
		if	LENGTH(cut_para)	=	30	then
			SET	cut_compno		=	cast(substring(cut_para, 1, 5) AS unsigned);
			SET	cut_brandcd		=	CAST(SUBSTRING(cut_para, 6, 5) AS UNSIGNED);
			SET	cut_orddt		=	SUBSTRING(cut_para, 11, 8);
			SET	cut_ordseq		=	CAST(SUBSTRING(cut_para, 19, 2) AS UNSIGNED);
			SET	cut_seq_from	=	CAST(SUBSTRING(cut_para, 21, 5) AS UNSIGNED);
			SET	cut_seq_to		=	CAST(SUBSTRING(cut_para, 26, 5) AS UNSIGNED);
			
			# TO 순번이 0이면 9999를 강제로 넣어줘 순번 전체가 조회되게 처리
			if	cut_seq_to	=	0	then
				SET	cut_seq_to	=	99999;
			END	if;
				
			# ==========================================
			# 기존 SP(YWM_+RACKPICUP_LIST) 붙여넣은 영역
			# ==========================================
			YWM_RACKPIUP_LIST_Block:	begin
				DECLARE	_orddt				VARCHAR(8);
				DECLARE	_ordseq				INT;
				DECLARE	_seq					INT;
				DECLARE	_asigndt				VARCHAR(8);
				DECLARE	v_delivery_type	VARCHAR(10);
				
				DECLARE	print_yn		BOOLEAN;

				DECLARE	_prodcd		VARCHAR(200);
				DECLARE	_prodnm		VARCHAR(200);
				DECLARE	_qty			INT;
				DECLARE	_qty2			INT;
				DECLARE	_prodcdm		VARCHAR(200);
				DECLARE	_flagset		CHAR(1);
				DECLARE	_levset		CHAR(1);
				DECLARE	_proddtcd	VARCHAR(200);
				
				DECLARE	done			INT	DEFAULT	0; 
				
				# (2021.7.19) 변수 추가
				DECLARE	_clmdt		VARCHAR(8);
				DECLARE	_clmseq		INT;
				DECLARE	sel_cnt				int;
				DECLARE	chkFreeCharge		boolean	default	false;
				DECLARE	clmlist_expcd		varchar(50);
				DECLARE	clmlist_proctype	varchar(50);
				# (2021.7.19) 변수 추가
					
				DECLARE	c2	CURSOR	FOR
					SELECT	proddtcd,	levset,	qtyset
					FROM		PRODDTL 
				 	WHERE		compno	=	cut_compno
			 		AND		brandcd 	=	cut_brandcd
					AND		prodcd	=	_prodcdm 
					AND		ifnull(flagset, '')	=	'1';
						
				DECLARE	c3	CURSOR	FOR
					SELECT	proddtcd,	levset,	qtyset 
					FROM		PRODDTL 
					WHERE		compno	=	cut_compno
					AND		brandcd	=	cut_brandcd
					AND		prodcd	=	_proddtcd
					AND		ifnull(flagset, '')	=	'1';										 	   
						
				DECLARE	c1	CURSOR	FOR
					SELECT	*
					FROM		tmp;
						
				DECLARE	CONTINUE	HANDLER	FOR	SQLSTATE	'02000'	SET	done	=	1;
						
				# 택배/직송 및 할당 여부 추가(2021.6.8)						
				# 주문품목 중 부품은 제외, 부품은 상담접수의 엑셀 저장을 통해 택배사로 수동 전송
				DROP		TEMPORARY	TABLE	if	EXISTS	tmp;
				CREATE	TEMPORARY	TABLE	IF	NOT	EXISTS	tmp	ENGINE=MEMORY	AS
					(SELECT	a.orddt,		a.ordseq,	a.seq,		ifnull(a.asigndt, ''),
								a.prodcd,	b.prodcdm,	a.qty,		b.flagset,
								a.clmdt,		a.clmseq
					FROM		ORDDTL a,	V_PRODUCTS b 
					WHERE		a.compno		=	cut_compno 
					AND		a.brandcd	=	cut_brandcd
					AND		a.orddt		=	cut_orddt
					AND		a.ordseq		=	cut_ordseq
					AND		a.seq			>=	cut_seq_from
					AND		a.seq			<=	cut_seq_to
					AND		iFNULL(ORDAMT, 0)	<>	0
					AND		a.compno		=	b.compno
					AND		a.brandcd	=	b.brandcd
					AND		a.prodcd		=	b.prodcd
					AND		ifnull(b.flagpart, '0')	<>	'1');	
			
				if	first_proc	then
					DROP		TEMPORARY	TABLE	if	EXISTS	RACKPICUP_TMP;
					CREATE	TEMPORARY	TABLE	IF	NOT	EXISTS	RACKPICUP_TMP
					(
						prodcd	varchar(20),
						prodcdm	varchar(20),
						qty		INT,
						flagset	char(1)
					)	ENGINE=MEMORY;
					DELETE	FROM	RACKPICUP_TMP;
					
					SET	FIRST_proc	=	FALSE;
				END	if;
					
				OPEN	c1;
					
				REPEAT
					FETCH	NEXT	FROM	c1
					INTO	_orddt,	_ordseq,		_seq,	_asigndt,
							_prodcd,	_prodcdm,	_qty,	_flagset,
							_clmdt,	_clmseq;
					
					IF	NOT	done	THEN
						# 주문기준 또는 할당구분 추가(2021.6.8)
						if	in_order_asign	=	"주문기준"	then
							SET	print_yn	=	TRUE;
						else
							if	LENGTH(_asigndt)	>	0	then
								SET	print_yn	=	TRUE;
							else
								SET	print_yn	=	FALSE;
							END	if;
						END	if;
						
						# 택배 또는 직송 추구 (2021.6.8)
						DELIVERY_SELECT_Block:	begin
							DECLARE	sel_done	INT	DEFAULT	0;
							DECLARE	continue	handler	FOR	SQLSTATE	'02000'	SET	sel_done	=	1;
						
							SET	v_delivery_type	=	'택배';
							
							SELECT	ifnull(b.deliverytype, '택배'),
										ifnull(a.expcd, ''),
										ifnull(a.proctype, '')
							INTO		v_delivery_type,
										clmlist_expcd,
										clmlist_proctype
							FROM		CLMLIST	AS	a	LEFT	JOIN	PROCTYPE	AS	b
																	ON		a.compno		=	b.compno
																	AND	a.brandcd	=	b.brandcd
																	AND	a.proctype	=	b.proctypecd
							WHERE		a.compno			=	cut_compno
							AND		a.brandcd		=	cut_brandcd
							AND		a.clmdt			=	_clmdt
							AND		a.clmseq			=	_clmseq;
						END	DELIVERY_SELECT_Block;
						
						# (2021.7.19) DELFREEMST 테이블에 택배사와 처리유형이
						# 입력되어 있으면 무임건으로 픽업 리스트에서 제외
						select	COUNT(*)
						into		sel_cnt
						FROM		DELFREEMST
						WHERE		compno		=	cut_compno
						AND		brandcd		=	cut_brandcd
						AND		expcd			=	clmlist_expcd
						AND		proctypecd	=	clmlist_proctype;
									
						if	sel_cnt	>=	1	then
							set	chkFreeCharge	=	true;
						else
							set	chkFreeCharge	=	false;
						end	if;
						# (2021.7.19) DELFREEMST (종료)
						
						# 배송유형(택배/직송)과 주문/할당 기준 체크
						if	v_delivery_type	=	in_gubun	AND	print_yn	and	chkFreeCharge	=	false	then
							IF	_prodcd	<>	_prodcdm	then
								SELECT	flagset
								INTO	_flagset
								FROM	PRODMST
								WHERE	compno	=	cut_compno
								AND	brandcd	=	cut_brandcd
								AND	prodcd	=	_prodcdm;
							END	if;
								
							IF	ifnull(_flagset, '')	=	'1'	then
								Block2: BEGIN
									DECLARE	done2	INT	DEFAULT	0;
									DECLARE	CONTINUE	HANDLER	FOR	SQLSTATE	'02000'	SET	done2	=	1;
									
									OPEN	c2;
										
									REPEAT
										FETCH	NEXT	FROM	c2
										INTO	_proddtcd,	_levset,	_qty2;
											
										IF	NOT	done2	THEN 
											IF	IFNULL(_levset, '')	=	'2'	THEN
												Block3: BEGIN
													DECLARE	done3	INT	DEFAULT	0;
													DECLARE	CONTINUE	HANDLER	FOR	SQLSTATE	'02000'	SET	done3	=	1;
														
													OPEN	c3;
														
													REPEAT
														FETCH	NEXT	FROM	c3
														INTO	_proddtcd,	_levset,	_qty2;
															
														IF	NOT	done3	THEN 
															INSERT	INTO	RACKPICUP_TMP
															VALUES	(_prodcd,	_proddtcd,	_qty*_qty2,	_flagset);
														END	IF;
													UNTIL	done3	END	REPEAT;
													
													close	c3;
												END Block3;
											ELSE
												INSERT	INTO	RACKPICUP_TMP
												VALUES	(_prodcd,	_proddtcd,	_qty*_qty2,	_flagset);
											END	IF;
										END	IF;
									UNTIL	done2	END	REPEAT;
									
									close	c2;
								END	Block2;
							ELSE
								INSERT	INTO	RACKPICUP_TMP
								VALUES	(_prodcd,	_prodcdm,	_qty,	_flagset);
							END	IF;
						END	if;
					END	IF;
				UNTIL	done	END	REPEAT;
					
				close	c1;
			END YWM_RACKPIUP_LIST_Block;	# 기존 SP(YWM_+RACKPICUP_LIST) 붙여넣은 영역 끝
		END	if;
			
		/* 무한루프 방지 */
		SET	safe_count	=	safe_count	-	1;
		if	safe_count	<=	0	then
			SET	start_pos	=	1;
			SET	end_pos		=	0;
		END	if;
	END	while;	

	SELECT	a.NecCd1,	a.NecCd2,	a.NecCd3, 
				case	ifnull(a.SPECDES,'')	
				when	''	then	CONCAT(ifnull(a.ProdNm,''),'\n')
							ELSE	CONCAT(a.prodnm ,'_',ifnull(a.SPECDES,''),'\n')
							END, 
			   concat(SUM(b.qty),'\n')  
	FROM		V_PRODUCTS a,	RACKPICUP_TMP b 
	WHERE		a.compno		=	cut_compno
	AND		a.brandcd	=	cut_brandcd
	AND		a.prodcd		=	b.prodcdm 
	AND		IFNULL(a.NecCd1, '')	<>	'' 
	AND		IFNULL(a.ProdCd, '')	<>	'' 
	AND		IFNULL(a.NecCd2, 0)	BETWEEN	1	AND	5 
	AND		IFNULL(a.NecCd3, 0)	<>	0 
				GROUP	BY	a.NecCd1,	a.NecCd2,	a.NecCd3,	a.SPECDES,	a.ProdNm 
				ORDER	BY	a.NecCd1,	a.NecCd2	DESC,	a.NecCd3,	a.ProdNm;
END;;

CREATE DEFINER=`root`@`%` FUNCTION `GET_PRODCD`(
	`p_seq_name` VARCHAR(4)
)
RETURNS varchar(12) CHARSET utf8mb4
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
	DECLARE RTN_VAL VARCHAR(12);
	
    INSERT INTO productcd_sequence (seq_name, seq_no)
         values (p_seq_name, LAST_INSERT_ID(1))
    ON DUPLICATE KEY UPDATE seq_no=LAST_INSERT_ID(seq_no+1);
	
    set @ret = row_count();
	
    if @ret = 0 then 
        set RTN_VAL = '0'; 
    else
        SET RTN_VAL = (SELECT CONCAT(p_seq_name,  LPAD(LAST_INSERT_ID(),8,'0'))); 
    end if;
	
	RETURN RTN_VAL;
END;;
DROP FUNCTION IF EXISTS `GET_CATEGORY_JSON_IDX`;;
CREATE DEFINER=`root`@`%` FUNCTION `GET_CATEGORY_JSON_IDX`(
	`V_SHOPCATE` JSON,
	`V_SHOPCD` VARCHAR(50)
)
RETURNS varchar(50) CHARSET utf8mb4
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
	DECLARE RETURN_VALUE INT;
	BEGIN
		SELECT idx INTO RETURN_VALUE
		  FROM (SELECT  0 AS idx UNION
				  SELECT  1 AS idx UNION
				  SELECT  2 AS idx UNION
				  SELECT  3 AS idx UNION
				  SELECT  4 AS idx UNION
				  SELECT  5 AS idx UNION
				  SELECT  6 AS idx UNION
				  SELECT  7 AS idx UNION
				  SELECT  8 AS idx UNION
				  SELECT  9 AS idx UNION
				  SELECT  10 AS idx UNION
				  SELECT  11 AS idx UNION
				  SELECT  12 AS idx UNION
				  SELECT  13 AS idx UNION
				  SELECT  14 AS idx UNION
				  SELECT  15 AS idx UNION
				  SELECT  16 AS idx UNION
				  SELECT  17 AS idx UNION
				  SELECT  18 AS idx UNION
				  SELECT  19 AS idx UNION
				  SELECT  20 AS idx UNION
				  SELECT  21 AS idx UNION
				  SELECT  22 AS idx UNION
				  SELECT  23
				  ) AS indexes
		 WHERE JSON_EXTRACT(V_SHOPCATE, CONCAT('$[', idx, '].shopcd')) = V_SHOPCD;
	END;
	SET RETURN_VALUE = IFNULL(RETURN_VALUE, 9999);
   RETURN (RETURN_VALUE);
END;;
DROP FUNCTION IF EXISTS `GET_ALL_ESCODE`;;
CREATE DEFINER=`root`@`%` FUNCTION `GET_ALL_ESCODE`(
	`V_SHOPCD` VARCHAR(50),
	`V_CODE` VARCHAR(50),
	`V_SHOPCATE` JSON
)
RETURNS text CHARSET utf8mb4
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
	DECLARE RETURN_VALUE TEXT;
	DECLARE v_idx2       INT;
	
	BEGIN
		SELECT GET_CATEGORY_JSON_IDX(V_SHOPCATE,V_SHOPCD) INTO v_idx2;		   
		SELECT concat(ID,'|', SHOPCATE) into RETURN_VALUE 
		 FROM CATEGORY
			WHERE 
		 JSON_EXTRACT(SHOPCATE, CONCAT('$[',v_idx2,'].cid')) = V_CODE limit 1;
			   
	END;
 	SET RETURN_VALUE = IFNULL(RETURN_VALUE, '');
   RETURN (RETURN_VALUE);
END;;
CREATE TABLE `cat_imsi_tot` (
	`ID` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_0900_ai_ci',
	`SHOP` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_0900_ai_ci',
	`CODE` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_0900_ai_ci'
)
COLLATE='utf8mb4_0900_ai_ci'
ENGINE=InnoDB;;

CREATE DEFINER=`root`@`%` PROCEDURE `DELETE_EXCELMASTER`(
	IN `inSeq` INT
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
    DECLARE exit handler for sqlexception
    BEGIN
        -- Error occurred, rollback changes or perform necessary actions
        ROLLBACK;
    END;

    START TRANSACTION; -- Start a transaction to ensure data consistency

    -- Attempt the following block of code, handle any exceptions if they occur
    BEGIN
        -- Delete records from excelformdetail
        DELETE FROM excelformdetail WHERE MasterSeq = inSeq;

        -- Delete records from excelformmaster
        DELETE FROM excelformmaster WHERE Seq = inSeq;

        -- Commit the transaction if no exceptions occur
        COMMIT;
    END;
END;;
CREATE DEFINER=`root`@`%` PROCEDURE `INSERT_COPY_EXCELMASTER`(
	IN `inSeq` INT
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
	 DECLARE newSeq INT ;
    DECLARE exit handler for sqlexception
    BEGIN
        -- Error occurred, rollback changes or perform necessary actions
        ROLLBACK;
        SELECT -1 AS NewSequence; -- Return -1 to indicate an error
    END;

    START TRANSACTION; -- Start a transaction to ensure data consistency

    -- Attempt the following block of code, handle any exceptions if they occur
    BEGIN
        SET newSeq = IFNULL((SELECT MAX(Seq) FROM excelformmaster), 0) + 1;

        -- Inserting a copy of the record and adjusting fields in excelformmaster
        INSERT INTO excelformmaster (Seq, ExcelFormName, USEYN, Registrant, RegistrationDt)
        SELECT newSeq, CONCAT(ExcelFormName, '_COPY'), USEYN, Registrant, CURDATE()
        FROM excelformmaster
        WHERE Seq = inSeq;

        -- Inserting details into excelformdetail
        INSERT INTO excelformdetail (Seq, MasterSeq, ExcelColumnName, ExcelColumnIndex, ExcelColumnValue, ExcelColumnMemo)
        SELECT Seq, newSeq, ExcelColumnName, ExcelColumnIndex, ExcelColumnValue, ExcelColumnMemo
        FROM excelformdetail
        WHERE MasterSeq = inSeq;

        -- Commit the transaction if no exceptions occur
        COMMIT;
        -- Return the newly generated sequence number
        SELECT newSeq AS NewSequence;
    END;
END;;
CREATE TABLE `excelformmaster` (
	`Seq` INT(10) NOT NULL AUTO_INCREMENT,
	`ExcelFormName` VARCHAR(255) NULL DEFAULT NULL COMMENT '엑셀양식이름' COLLATE 'utf8mb4_0900_ai_ci',
	`UseYN` CHAR(1) NULL DEFAULT NULL COMMENT '사용여부' COLLATE 'utf8mb4_0900_ai_ci',
	`Registrant` VARCHAR(255) NULL DEFAULT NULL COMMENT '등록자' COLLATE 'utf8mb4_0900_ai_ci',
	`RegistrationDt` DATE NULL DEFAULT NULL COMMENT '등록일',
	`Modifier` VARCHAR(255) NULL DEFAULT NULL COMMENT '수정자' COLLATE 'utf8mb4_0900_ai_ci',
	`ModificationDt` DATE NULL DEFAULT NULL COMMENT '수정일',
	PRIMARY KEY (`Seq`) USING BTREE,
	UNIQUE INDEX `uk_no` (`ExcelFormName`) USING BTREE
)
COLLATE='utf8mb4_0900_ai_ci'
ENGINE=InnoDB
AUTO_INCREMENT=11
;;
CREATE TABLE `excelformdetail` (
	`Seq` INT(10) NOT NULL,
	`MasterSeq` INT(10) NOT NULL,
	`ExcelColumnName` VARCHAR(255) NULL DEFAULT NULL COMMENT '엑셀 컬럼 이름' COLLATE 'utf8mb4_0900_ai_ci',
	`ExcelColumnIndex` INT(10) NULL DEFAULT NULL COMMENT '엑셀 컬럼 인덱스',
	`ExcelColumnValue` VARCHAR(255) NULL DEFAULT NULL COMMENT '컬럼 값' COLLATE 'utf8mb4_0900_ai_ci',
	`ExcelColumnMemo` TEXT NULL DEFAULT NULL COMMENT '컬럼 메모' COLLATE 'utf8mb4_0900_ai_ci',
	PRIMARY KEY (`Seq`, `MasterSeq`) USING BTREE,
	INDEX `MasterSeq` (`MasterSeq`) USING BTREE,
	CONSTRAINT `excelformdetail_ibfk_1` FOREIGN KEY (`MasterSeq`) REFERENCES `excelformmaster` (`Seq`) ON UPDATE NO ACTION ON DELETE NO ACTION
)
COLLATE='utf8mb4_0900_ai_ci'
ENGINE=InnoDB
;;

