lipinka_admin/docs/databases/将卡中数据导入到账户中20240123.sql

-- 个人卡
CREATE TABLE tmp_cust_goods(
	SELECT 
		r.wxMpOpenid,
		d.customerSid,
		d.bindDate,
		d.giftbagSid,
		d.serialNumber,
		d.code,
		s.goodsSid,
		s.goodsNumber,
		e.NAME AS goodsName,
		'个人卡' cardtype
	FROM lpk_giftcard d 
		LEFT JOIN lpk_customer r ON r.sid = d.customerSid	
		LEFT JOIN lpk_giftbag_goods s ON s.giftbagSid=d.giftbagSid 	
		LEFT JOIN lpk_goods e ON e.sid = s.goodsSid  	
	WHERE d.customerSid IS NOT NULL AND d.customerSid <> ''
	ORDER BY r.wxMpOpenid
);

select count(1) from tmp_cust_goods; -- 5080 

-- 企业卡
INSERT INTO tmp_cust_goods 
	SELECT 
		r.wxMpOpenid,
		d.customerSid,
		d.bindDate,
		d.giftbagSid,
		d.serialNumber,
		d.code,
		s.goodsSid,
		s.goodsNumber,
		e.NAME AS goodsName,
		'企业卡' cardtype
	FROM emp_card d 
		LEFT JOIN lpk_customer r ON r.sid = d.customerSid	
		LEFT JOIN lpk_giftbag_goods s ON s.giftbagSid=d.giftbagSid 	
		LEFT JOIN lpk_goods e ON e.sid = s.goodsSid  	
	WHERE d.customerSid IS NOT NULL AND d.customerSid <> '' 
	ORDER BY r.wxMpOpenid;
	
select count(1) from tmp_cust_goods; -- 5256 

-- 购买卡
INSERT INTO tmp_cust_goods 
	SELECT 
		r.wxMpOpenid,
		d.customerSid,
		d.bindDate,
		d.sid,
		d.serialNumber,
		d.code,
		s.goodsSid,
		s.goodsNumber,
		e.NAME AS goodsName,
		'购买卡' cardtype
	FROM emp_card_gift d 
		LEFT JOIN lpk_customer r ON r.sid = d.customerSid	
		LEFT JOIN emp_card_gift_goods s ON s.empCardGiftSid=d.sid 	
		LEFT JOIN lpk_goods e ON e.sid = s.goodsSid  	
	WHERE d.customerSid IS NOT NULL AND d.customerSid <> '' AND d.isSenior = '1' 
	ORDER BY r.wxMpOpenid;
	
select count(1) from tmp_cust_goods; -- 5325 

-- 转赠卡
INSERT INTO tmp_cust_goods 
	SELECT 
		r.wxMpOpenid,
		d.customerSid,
		d.bindDate,
		d.sid,
		d.serialNumber,
		d.code,
		s.goodsSid,
		s.goodsNumber,
		e.NAME AS goodsName,
		'转赠卡' cardtype
	FROM emp_card_gift d 
		LEFT JOIN lpk_customer r ON r.sid = d.customerSid	
		LEFT JOIN emp_card_gift_goods s ON s.empCardGiftSid=d.sid 	
		LEFT JOIN lpk_goods e ON e.sid = s.goodsSid  	
	WHERE d.customerSid IS NOT NULL AND d.customerSid <> '' AND d.isSenior = '2' 
	ORDER BY r.wxMpOpenid;
	
select count(1) from tmp_cust_goods; -- 5652 
	
	
	-- 预约提货，数量为负值
	INSERT INTO tmp_cust_goods 
		SELECT 
			r.wxMpOpenid,
			d.customerSid,
			d.reserveDate,
			d.sid,
			d.storeSid,
			d.cardCode,
			s.goodsSid,
			-s.goodsNumber AS goodsNumber,
			e.NAME AS goodsName,
			d.cardType cardtype
		FROM lpk_reserve_order d 
			LEFT JOIN lpk_customer r ON r.sid = d.customerSid	
			LEFT JOIN lpk_reserve_order_goods s ON s.orderSid=d.sid 	
			LEFT JOIN lpk_goods e ON e.sid = s.goodsSid  	
		WHERE 1=1
		ORDER BY r.wxMpOpenid;
	
select count(1) from tmp_cust_goods; -- 10917 
	
	
	-- 赠出的卡，数量为负值
	INSERT INTO tmp_cust_goods 
	SELECT 
		r.wxMpOpenid,
		d.empCardCustomerSid,
		d.grantDate,
		d.sid,
		d.serialNumber,
		d.code,
		s.goodsSid,
		-s.goodsNumber AS goodsNumber,
		e.NAME AS goodsName,
		'赠出' cardtype
	FROM emp_card_gift d 
		LEFT JOIN lpk_customer r ON r.sid = d.empCardCustomerSid	
		LEFT JOIN emp_card_gift_goods s ON s.empCardGiftSid=d.sid 	
		LEFT JOIN lpk_goods e ON e.sid = s.goodsSid  	
	WHERE d.empCardCustomerSid IS NOT NULL AND d.empCardCustomerSid <> '' AND d.isSenior = '2' 
	ORDER BY r.wxMpOpenid;
	
select count(1) from tmp_cust_goods; -- 11292 

select * from tmp_cust_goods;

delete from vegetable_cellar where 1=1;

-- 统计
	INSERT INTO vegetable_cellar(sid,customerSid,goodsSid,goodsNumber)
	SELECT 
	   UUID(),
		customerSid,
		goodsSid,
		SUM(goodsNumber) AS goodsNumber
	FROM tmp_cust_goods
	GROUP BY customerSid,goodsSid
	HAVING goodsNumber>0 
	


	update vegetable_cellar vc set affiliation=(select brandId from lpk_goods lg where vc.goodsSid=lg.sid) where 1=1