drop PROCEDURE if exists Ap_ygfqqs;
DELIMITER $$  
CREATE DEFINER=`baison`@`%` PROCEDURE `Ap_ygfqqs`(
IN vphy  varchar(30),
IN vplb varchar(30),
IN zklx varchar(30)     
)
BEGIN

select gk_id,vpdm,vpmc,b.lbmc,b.lqzk from ipos_vip a,ipos_czklb b  WHERE 

(vpdm LIKE vphy or  sj LIKE vphy) AND  lbdm LIKE vplb AND b.lbdm LIKE zklx

GROUP BY  gk_id,vpdm,vpmc,b.lbmc,b.lqzk;
end$$



---根据小票送券
drop PROCEDURE if exists pro_wxjftz;
DELIMITER $$  
CREATE DEFINER=`root`@`%` PROCEDURE `pro_xfzsq`(
IN qdm varchar(60),
IN qje int,
IN yxrq VARCHAR(20),
IN sysm varchar(20),
IN sddm varchar(20),
IN xph varchar(20),
IN dhlx varchar(20)

)
BEGIN 
insert INTO ipos_czk (czkdm,lb_id,org_id,zd_id,syfw,sysp,`status`,qy,qyrq,yxrq,ty,dqje,cz_yxrq,czje,czrq,zcje,jdr,jdrq,xgr,spzk,lqzk,lqlx,zk,push_state,ksrq,bds,bz)
SELECT   qdm ,43,1,id,0,0,1,1,UNIX_TIMESTAMP(NOW()),UNIX_TIMESTAMP(yxrq),0,qje, UNIX_TIMESTAMP(yxrq),
         qje,UNIX_TIMESTAMP(NOW()),qje,sysm,UNIX_TIMESTAMP(NOW()),dhlx,0,1,1,1,0,0,0,xph
from com_base_kehu 
where khdm=sddm ;


insert into ipos_czklsz (pid,czk_id,czkdm,czdm,czmc,czsj,org_id,zd_id,zj,je,qmje,lxdj,bz)
select id,id,czkdm,0,dhlx,qyrq,1,zd_id,1,czje,zcje,bz,sysm
from  ipos_czk
where czkdm= qdm;


end $$

---根据导购业绩
drop PROCEDURE if exists APP_PROC_dgyj;
DELIMITER $$  
CREATE DEFINER=`baison`@`%` PROCEDURE `APP_PROC_dgyj`(auth varchar(20) , 
params TEXT )
begin

declare Rq_S varchar(10) DEFAULT '';   -- 起始日期
declare Rq_e varchar(10) DEFAULT ''; -- 终止日期
declare sddm varchar(100)DEFAULT '';   -- 商店           必选条件

set rq_s=getjson (params,'beginDate');
set rq_e=getjson (params,'endDate');
set sddm=getjson (params,'sddm');


call app_proc_getqx(auth,2) ;
  select * from com_base_kehu, khqx  where com_base_kehu.khdm=khqx.dm;

select  A.DGY_ID code,DGY_MC name,sum(A.sl) num,sum(A.je)amount ,COUNT(*)ordernum,sum(B.je)standardAmount ,'0' grossProfit
from ipos_qtlsdyj A left join  ipos_qtlsd  B ON B.ID=A.PID
WHERE DATE_FORMAT(FROM_UNIXTIME(B.RQ),'%Y-%m-%d') BETWEEN rq_s AND rq_e  AND ZDDM=sddm
GROUP BY  A.DGY_ID,DGY_MC ;


end$$


---根据七匹狼充值汇总余额店铺明细
drop PROCEDURE if exists P_qplczhzyedpmx;
DELIMITER $$  
CREATE DEFINER=`baison`@`%` PROCEDURE `P_qplczhzyedpmx`(st_st datetime ,
st_ed datetime ,
vipk varchar(20),
sd varchar(20)     
)
BEGIN
drop table if exists t_vipout;
create TEMPORARY  table  t_vipout
(
sddm varchar(10) null,
sdmc varchar(60) null,
vpdm varchar(20) null,
vipmc varchar(30) null,
czje decimal(18,0)null,
flje decimal(18,0)null,
xfje decimal(18,0)null,
sdczje decimal(18,0)null,
sdflje decimal(18,0)null,
sdxfje decimal(18,0)null
);

 insert into  t_vipout(vpdm,sddm,czje,flje,sdczje,sdflje,sdxfje,xfje)
  (select a.vpdm,a.khdm,sum(a.je*a.zj),sum(a.hkje*a.zj),0,0,0,0
    from vw_iposvpczklsz a,ipos_vip b
 where  a.vpdm=b.vpdm and b.lbdm='16' 
 and  a.khdm like sd  and b.vpdm like vipk and a.rq<st_st      
 group by a.vpdm,a.khdm )
 union all
(select a.vpdm,a.khdm,0,0,sum(a.fkje*a.zj),sum(a.hkje*a.zj),0,0
    from vw_iposvpczklsz a,ipos_vip b
 where  a.vpdm=b.vpdm and b.lbdm='16'  and a.czdm='1'
       and  a.khdm like sd  and b.vpdm like vipk and a.rq<=st_ed and a.rq>=st_st
 group by a.vpdm,a.khdm)
union all
 (select a.vpdm,a.khdm,0,0,0,0,0,sum(a.je*a.zj) 
 from vw_iposvpczklsz a,ipos_vip b
 where  a.vpdm=b.vpdm and b.lbdm='16'  and a.czdm='2'
		 and  a.khdm like sd  and b.vpdm like vipk  and a.rq<st_st      
 group by vpdm,a.khdm)
 
 union all
( select a.vpdm,a.khdm,0,0,0,0,sum(a.je*a.zj),0 
 from vw_iposvpczklsz a,ipos_vip b
 where  a.vpdm=b.vpdm and b.lbdm='16'  and a.czdm='2'
  and  a.khdm like sd  and b.vpdm like vipk  and a.rq<=st_ed and a.rq>=st_st
 group by a.vpdm,a.khdm)
;
 update t_vipout   INNER JOIN  com_base_kehu on t_vipout.sddm=com_base_kehu.khdm 
 set t_vipout.sdmc =com_base_kehu.KHMC  ;
 update t_vipout INNER JOIN  ipos_vip on t_vipout.vpdm=ipos_vip.vpdm set t_vipout.vipmc =ipos_vip.vpmc  ;
 select 
	 ipos_vip.zddm ,com_base_kehu.khmc,sddm,sdmc,t_vipout.vpdm,vipmc,sum(czje)as czje ,sum(flje) as flje ,sum(t_vipout.xfje) as xfje,sum(sdczje)as sdczje ,
	 sum(sdflje) as sdflje ,sum(t_vipout.sdxfje) as sdxfje
	 from t_vipout ,ipos_vip,com_base_kehu
 where  
   t_vipout.vpdm=ipos_vip.vpdm
 and ipos_vip.zddm=com_base_kehu.khdm
 group by ipos_vip.zddm,com_base_kehu.khmc,sddm,sdmc,t_vipout.vpdm,vipmc
 order by VIPk,sum(czje)  desc;

end$$

---根据七匹狼充值汇总余额店铺明细
drop PROCEDURE if exists P_qplczhzyedpmxipos;
DELIMITER $$  
CREATE DEFINER=`baison`@`%` PROCEDURE `P_qplczhzyedpmxipos`(st_st datetime ,
st_ed datetime ,
vipk varchar(20),
sd varchar(20)     
)
BEGIN
drop table if exists t_vipout;
create TEMPORARY  table  t_vipout
(
sddm varchar(10) null,
sdmc varchar(60) null,
vpdm varchar(20) null,
vipmc varchar(30) null,
czje decimal(18,0)null,
flje decimal(18,0)null,
xfje decimal(18,0)null,
sdczje decimal(18,0)null,
sdflje decimal(18,0)null,
sdxfje decimal(18,0)null
);

 insert into  t_vipout(vpdm,sddm,czje,flje,sdczje,sdflje,sdxfje,xfje)
  (select a.vpdm,a.khdm,sum(a.je*a.zj),sum(a.hkje*a.zj),0,0,0,0
    from vw_iposvpczklsz a,ipos_vip b
 where  a.vpdm=b.vpdm and b.lbdm='16' 
 and  a.khdm like sd  and b.vpdm like vipk and a.rq<st_st      
 group by a.vpdm,a.khdm )
 union all
(select a.vpdm,a.khdm,0,0,sum(a.fkje*a.zj),sum(a.hkje*a.zj),0,0
    from vw_iposvpczklsz a,ipos_vip b
 where  a.vpdm=b.vpdm and b.lbdm='16'  and a.czdm='1'
       and  a.khdm like sd  and b.vpdm like vipk and a.rq<=st_ed and a.rq>=st_st
 group by a.vpdm,a.khdm)
union all
 (select a.vpdm,a.khdm,0,0,0,0,0,sum(a.je*a.zj) 
 from vw_iposvpczklsz a,ipos_vip b
 where  a.vpdm=b.vpdm and b.lbdm='16'  and a.czdm='2'
		 and  a.khdm like sd  and b.vpdm like vipk  and a.rq<st_st      
 group by vpdm,a.khdm)
 
 union all
( select a.vpdm,a.khdm,0,0,0,0,sum(a.je*a.zj),0 
 from vw_iposvpczklsz a,ipos_vip b
 where  a.vpdm=b.vpdm and b.lbdm='16'  and a.czdm='2'
  and  a.khdm like sd  and b.vpdm like vipk  and a.rq<=st_ed and a.rq>=st_st
 group by a.vpdm,a.khdm)
;
 update t_vipout   INNER JOIN  com_base_kehu on t_vipout.sddm=com_base_kehu.khdm 
 set t_vipout.sdmc =com_base_kehu.KHMC  ;
 update t_vipout INNER JOIN  ipos_vip on t_vipout.vpdm=ipos_vip.vpdm set t_vipout.vipmc =ipos_vip.vpmc  ;
 select 
	 ipos_vip.zddm ,com_base_kehu.khmc,sddm,sdmc,t_vipout.vpdm,vipmc,sum(czje)as czje ,sum(flje) as flje ,sum(t_vipout.xfje) as xfje,sum(sdczje)as sdczje ,
	 sum(sdflje) as sdflje ,sum(t_vipout.sdxfje) as sdxfje
	 from t_vipout ,ipos_vip,com_base_kehu
 where  
   t_vipout.vpdm=ipos_vip.vpdm
 and ipos_vip.zddm=com_base_kehu.khdm
 group by ipos_vip.zddm,com_base_kehu.khmc,sddm,sdmc,t_vipout.vpdm,vipmc
 order by VIPk,sum(czje)  desc;

end$$


drop PROCEDURE if exists P_qplczhzyedpmxipos;
DELIMITER $$  
CREATE DEFINER=`baison`@`%` PROCEDURE `P_qplczhzyedpmxiposn`()
BEGIN
drop table if exists t_vipout;
create TEMPORARY  table  t_vipout
(
sddm varchar(10) null,
sdmc varchar(60) null,
vpdm varchar(20) null,
vipmc varchar(30) null,
czje decimal(18,0)null,
flje decimal(18,0)null,
xfje decimal(18,0)null,
sdczje decimal(18,0)null,
sdflje decimal(18,0)null,
sdxfje decimal(18,0)null
);

 insert into  t_vipout(vpdm,sddm,czje,flje,sdczje,sdflje,sdxfje,xfje)
  (select a.vpdm,a.khdm,sum(a.je*a.zj),sum(a.hkje*a.zj),0,0,0,0
    from vw_iposvpczklsz a,ipos_vip b,sszxcs c
 where  a.vpdm=b.vpdm and b.lbdm='16' and  c.lb='1'
 and  a.khdm like c.sd  and b.vpdm like c.vipk and a.rq<c.st_st      
 group by a.vpdm,a.khdm )
 union all
(select a.vpdm,a.khdm,0,0,sum(a.fkje*a.zj),sum(a.hkje*a.zj),0,0
    from vw_iposvpczklsz a,ipos_vip b,sszxcs c
 where  a.vpdm=b.vpdm and b.lbdm='16'  and a.czdm='1' and c.lb='1'
       and  a.khdm like c.sd  and b.vpdm like c.vipk and a.rq<=c.st_ed and a.rq>=c.st_st
 group by a.vpdm,a.khdm)
union all
 (select a.vpdm,a.khdm,0,0,0,0,0,sum(a.je*a.zj) 
 from vw_iposvpczklsz a,ipos_vip b,sszxcs c
 where  a.vpdm=b.vpdm and b.lbdm='16'  and a.czdm='2' and  c.lb='1'
		 and  a.khdm like c.sd  and b.vpdm like c.vipk  and a.rq<c.st_st      
 group by vpdm,a.khdm)
 
 union all
( select a.vpdm,a.khdm,0,0,0,0,sum(a.je*a.zj),0 
 from vw_iposvpczklsz a,ipos_vip b,sszxcs c
 where  a.vpdm=b.vpdm and b.lbdm='16'  and a.czdm='2' and  c.lb='1'
  and  a.khdm like c.sd  and b.vpdm like c.vipk  and a.rq<=c.st_ed and a.rq>=c.st_st
 group by a.vpdm,a.khdm)
;
 update t_vipout   INNER JOIN  com_base_kehu on t_vipout.sddm=com_base_kehu.khdm 
 set t_vipout.sdmc =com_base_kehu.KHMC  ;
 update t_vipout INNER JOIN  ipos_vip on t_vipout.vpdm=ipos_vip.vpdm set t_vipout.vipmc =ipos_vip.vpmc  ;
 select 
	 ipos_vip.zddm ,com_base_kehu.khmc,sddm,sdmc,t_vipout.vpdm,vipmc,sum(czje)as czje ,sum(flje) as flje ,sum(t_vipout.xfje) as xfje,sum(sdczje)as sdczje ,
	 sum(sdflje) as sdflje ,sum(t_vipout.sdxfje) as sdxfje
	 from t_vipout ,ipos_vip,com_base_kehu
 where  
   t_vipout.vpdm=ipos_vip.vpdm
 and ipos_vip.zddm=com_base_kehu.khdm
 group by ipos_vip.zddm,com_base_kehu.khmc,sddm,sdmc,t_vipout.vpdm,vipmc
 order by t_vipout.vpdm,sum(czje)  desc;
DELETE FROM sszxcs  where lb='1';
commit;
end$$

drop PROCEDURE if exists P_qplczhzyedpmxipos;
DELIMITER $$  
CREATE DEFINER=`baison`@`%` PROCEDURE `P_qplczyedpmx`()
BEGIN

drop table if exists t_vipout;
create TEMPORARY table t_vipout
(
rq   datetime,
sddm varchar(10) null,
sdmc varchar(60) null,
VIPk varchar(20) null,
czy varchar(20) null,
dymc varchar(20) null,
vipmc varchar(30) null,
djbh varchar(30) null,
czje decimal(18,0)null,
flje decimal(18,0)null,
xfje decimal(18,0)null,
sdczje decimal(18,0)null,
sdflje decimal(18,0)null,
sdxfje decimal(18,0)null
);

 insert into t_vipout(rq,VIPk,sddm,czy,dymc,djbh,czje,flje,sdczje,sdflje,xfje,sdxfje)
(select date_format(from_unixtime(a.RQ),'%Y-%m-%d') rq,d.vpdm,c.khdm dm1,a.zdr,a.dgy,a.DJBH,a.fkje,a.hkje,0,0,0,0
    from ipos_vpczd a,ipos_vip d ,com_base_kehu c,sszxcs e
 where  d.id=a.vp_id and d.lbdm='16'  and sp='1'  and c.id=a.zd_id and e.lb='2'
      and d.vpdm like e.vipk and date_format(from_unixtime(a.RQ),'%Y-%m-%d')<e.st_st)     
union all
(select date_format(from_unixtime(a.RQ),'%Y-%m-%d') rq,d.vpdm,c.khdm dm1,a.zdr,a.dgy,a.DJBH,a.fkje,a.hkje,0,0,0,0
    from ipos_vpczd a,ipos_vip d ,com_base_kehu c,sszxcs e
 where  d.id=a.vp_id and d.lbdm='16'  and sp='1'  and c.id=a.zd_id and e.lb='2'
       and d.vpdm like e.vipk and date_format(from_unixtime(a.RQ),'%Y-%m-%d')<=e.st_ed and
			 date_format(from_unixtime(a.RQ),'%Y-%m-%d')>=e.st_st)
union all
(select date_format(from_unixtime(a.rq),'%Y-%m-%d') rq ,
 b.dm,c.khdm,a.zdr,a.dgy_list_mc,a.djbh,0,0,0,0,b.je,0  
 from ipos_qtlsd a ,ipos_qtlsdjs b,ipos_vip,com_base_kehu c,sszxcs e
   where a.id=b.pid  
         and  b.js_id=4  and c.id=a.zd_id and  ipos_vip.lbdm='16'
		     and ipos_vip.vpdm=b.dm and e.lb='2'
		 and b.dm like e.vipk and date_format(from_unixtime(a.RQ),'%Y-%m-%d')<e.st_st 			 	 
group by  b.dm,c.khdm,a.zdr,a.dgy_list_mc,a.djbh,b.je)
 union all
 (select date_format(from_unixtime(a.rq),'%Y-%m-%d') rq,
 b.dm,c.khdm,a.zdr,a.dgy_list_mc,a.djbh,0,0,0,0,b.je,0  
 from ipos_qtlsd a ,ipos_qtlsdjs b,ipos_vip,com_base_kehu c,sszxcs e
   where a.id=b.pid  and  b.js_id=4  and c.id=a.zd_id and  ipos_vip.lbdm='16'
		     and ipos_vip.vpdm=b.dm and e.lb='2'
		 and b.dm like e.vipk and date_format(from_unixtime(a.RQ),'%Y-%m-%d')>=e.st_st 
			 and date_format(from_unixtime(a.RQ),'%Y-%m-%d')<=e.st_ed
   group by  b.dm,c.khdm,a.zdr,a.dgy_list_mc,a.djbh,b.je)
;
 update t_vipout   INNER JOIN  com_base_kehu on t_vipout.sddm=com_base_kehu.khdm 
 set t_vipout.sdmc =com_base_kehu.KHMC  ;
 update t_vipout INNER JOIN  ipos_vip on t_vipout.VIPk=ipos_vip.vpdm set t_vipout.vipmc =ipos_vip.vpmc  ;
 select 
	rq,sddm,sdmc,vipk,vipmc,czy,dymc,djbh,
	czje ,flje ,
	czje+flje as qccz,
	sdczje as sdczje,sdflje as sdflje,
	sdczje+sdflje as sdcz,
	t_vipout.xfje as xfje ,t_vipout.sdxfje as sdxfje
	 from t_vipout ,ipos_vip
 where  t_vipout.vipk=ipos_vip.vpdm
 order by rq,vipk;
 DELETE  FROM sszxcs  where lb='2';
 commit;
end$$

drop PROCEDURE if exists pro_dzqwxts;
DELIMITER $$  
CREATE DEFINER=`baison`@`%` PROCEDURE `pro_dzqwxts`(
 IN djbh varchar(20),
 IN kdm varchar(30),
 IN sddm varchar(40),
 IN vdm varchar(20),
 IN sj varchar(20),
 IN sd varchar(20),
 IN openid varchar(100),
 IN stjf  varchar(20),
 IN edjf varchar(20)
 )
BEGIN

select a.djbh,a.czkdm,a.khmc,a.id,a.czje,b.vpdm,b.openid from 
(select ipos_czkzcd.djbh,ipos_czk.czkdm,com_base_kehu.khmc,ipos_czk.id,ipos_czk.czje,@scoreNum :=@scoreNum+1 as scoreNum from
ipos_czk LEFT JOIN ipos_czkzcdmx on ipos_czk.czkdm=ipos_czkzcdmx.czkdm
LEFT JOIN ipos_czkzcd on ipos_czkzcd.id=ipos_czkzcdmx.pid 
LEFT JOIN com_base_kehu on ipos_czk.zd_id=com_base_kehu.id,
(select @scoreNum :=0) init 
 where  ipos_czkzcd.djbh LIKE djbh 
        and ipos_czk.czkdm LIKE kdm
				and com_base_kehu.khdm LIKE sddm AND ipos_czk.push_state='0'
ORDER BY ipos_czk.czkdm  DESC) a,
(select ipos_vip.vpdm,ipos_vip.openid,@scoreNum1 :=@scoreNum1+1 as scoreNum1 from ipos_vip,(select @scoreNum1 :=0) init 
 where vpdm LIKE vdm and sj like sj and zddm like sd and openid like openid
       and dqjf>=stjf and dqjf<=edjf  and ipos_vip.openid<>''
ORDER BY ipos_vip.vpdm  DESC) b 
where a.scoreNum=b.scoreNum1  ;
end$$
--积分兑换
drop PROCEDURE if exists pro_hxjfdh;
DELIMITER $$  
CREATE DEFINER=`baison`@`%` PROCEDURE `pro_hxjfdh`( dhdjbh  VARCHAR(40),vipcard VARCHAR(20) ,dhr VARCHAR(20),dhjf INT,
 rq VARCHAR(40),sd VARCHAR(40),OUT returnq INT
 )
BEGIN
insert into ipos_jftzd (djbh,rq,tzlx,tzjf,jf,zdr,zdrq,zd_id,tn_id,org_id)
 select dhdjbh,UNIX_TIMESTAMP(NOW()) ,'1',dhjf,-dhjf,dhr,UNIX_TIMESTAMP(NOW()),com_base_kehu.id,0,1
from ipos_vip , com_base_kehu 
where vpdm=vipcard  and com_base_kehu.khdm=sd;
insert into ipos_jftzdmx(pid,vp_id,dqjf,tzjf,ljjf,bz,vpdm)
 select a.Id ,b.Id ,b.dqjf,-dhjf,b.dqjf-dhjf,'积分兑换',b.vpdm from ipos_jftzd a ,ipos_vip b
 where  vpdm=vipcard and djbh=dhdjbh;
SET returnq=0; 
end$$
---删除积分调整单
drop PROCEDURE if exists pro_hxjfdhdel;
DELIMITER $$  
CREATE DEFINER=`baison`@`%` PROCEDURE `pro_hxjfdhdel`( dhdjbh  VARCHAR(40),OUT returnq INT)
BEGIN
DELETE ipos_jftzdmx FROM  ipos_jftzdmx WHERE pid= (SELECT id FROM ipos_jftzd where djbh=dhdjbh) ;
DELETE ipos_jftzd from ipos_jftzd WHERE ipos_jftzd.id = dhdjbh;
SET returnq=0; 
end$$


---删除积分调审批
drop PROCEDURE if exists pro_hxjfdhsp;
DELIMITER $$  
CREATE DEFINER=`baison`@`%` PROCEDURE `pro_hxjfdhsp`( dhdjbh  VARCHAR(40),vipcard VARCHAR(20) ,dhr VARCHAR(20),dhjf INT,
 rq VARCHAR(40),OUT returnq INT
 )
BEGIN

insert into ipos_vplsz(vp_id,vpdm,vpmc,jfcz,czmc,czsj,zd_id,zddm,zdmc,qcjf,qcsl,qcje,tzfx,zj,jf,sl,je,qmjf,qmje,lxdj,yxjf,lylx,dy_id,lb_id)
select                 a.id,a.vpdm,a.vpmc,5,'积分兑换',a.sj,a.zd_id,a.zddm,b.khmc,a.dqjf,0,0,0,dhjf,1,0,0,a.dqjf-dhjf,1,
d.djbh,1,0,0,1
				
from ipos_vip a ,com_base_kehu b ,ipos_jftzdmx  c,ipos_jftzd d
where a.zddm=b.khdm  and a.vpdm=vipcard and c.pid=d.Id and c.vpdm=a.vpdm;

update ipos_vip set dqjf=(dqjf-dhjf) where vpdm=vipcard;
UPDATE ipos_jftzd as a SET sp='1' ,spr=dhr,sprq=UNIX_TIMESTAMP(NOW())   
WHERE djbh=dhdjbh;
SET returnq=0;
end$$

drop PROCEDURE if exists pro_hxstvipzc;
DELIMITER $$  
CREATE DEFINER=`baison`@`%` PROCEDURE `pro_hxstvipzc`(vipcard varchar(20),
vipname varchar(40),
vipsj varchar(20),
vipsex varchar(10),
vipsr datetime,
vipopenid varchar(100),
dqrq datetime ,
out returnq INT
)
BEGIN
if NOT EXISTS (select vpdm from ipos_vip where vpdm=vipcard) THEN
 set  returnq= 1; 
ELSEIF vipname='' THEN
set  returnq= 2; 
ELSEIF vipsj=''  THEN
set  returnq= 3; 
ELSEIF vipsex='' THEN
set  returnq= 4; 
ELSEIF DAY(vipsr)= DAY(CURRENT_DATE ())   THEN
set  returnq= 5; 
ELSE
 update ipos_vip set vpmc=vipname,openid=vipopenid,dqrq=UNIX_TIMESTAMP(dqrq),zcrq=UNIX_TIMESTAMP(NOW()),lbdm='316',lb_id=7,xldm='06',xl_id=7,sj=vipsj,sr1=UNIX_TIMESTAMP(vipsr) ,
 ipos_vip.birth=
				CONCAT_WS('-',SUBSTR(date_format( from_unixtime(vipsr),'%Y-%m-%d'),6,2),SUBSTR(date_format(from_unixtime(vipsr),'%Y-%m-%d'),9,10)) 
 where vpdm=vipcard;
 update ipos_customer set gkmc=vipname,xb=vipsex , sj=vipsj,sr1=UNIX_TIMESTAMP(vipsr) ,
 ipos_customer.birth=
				CONCAT_WS('-',SUBSTR(date_format( from_unixtime(vipsr),'%Y-%m-%d'),6,2),SUBSTR(date_format(from_unixtime(vipsr),'%Y-%m-%d'),9,10)) 
  where  gkdm in (select gkdm from ipos_vip where vpdm=vipcard); 
 set  returnq= 0;          
end if;
end$$

drop PROCEDURE if exists pro_hxvipzc;
DELIMITER $$  
CREATE DEFINER=`baison`@`%` PROCEDURE `pro_hxvipzc`(vipcard varchar(20),
vipname varchar(40),
vipsj varchar(20),
vipsex varchar(10),
vipsr datetime,
vipopenid varchar(100),
 OUT returnq  int
)
BEGIN
if NOT EXISTS (select * from ipos_vip where vpdm=vipcard) THEN
 SET  returnq=1 ;
ELSEIF vipname='' THEN
SET    returnq=2 ;  
ELSEIF vipsj=''  THEN
SET returnq=3  ;
ELSEIF vipsex='' THEN
SET returnq=4;
ELSEIF DAY(vipsr)= DAY(CURRENT_DATE ())   THEN
SET returnq=5;
ELSE
 update ipos_vip set vpmc=vipname, openid= vipopenid,sj=vipsj,sr1=UNIX_TIMESTAMP(vipsr),
        ipos_vip.birth=
				CONCAT_WS('-',SUBSTR(vipsr,6,2),SUBSTR(vipsr,9,10)) 
 where vpdm=vipcard;
 update ipos_customer set gkmc=vipname,xb=vipsex , sj=vipsj,sr1=UNIX_TIMESTAMP(vipsr),
         ipos_customer.birth=
				CONCAT_WS('-',SUBSTR(vipsr,6,2),SUBSTR(vipsr,9,10)) 
  where  gkdm in (select gkdm from ipos_vip where vpdm=vipcard);
  
  SET returnq=0;
    
end if;

end$$

drop PROCEDURE if exists pro_intodzqt;
DELIMITER $$  
CREATE DEFINER=`baison`@`%` PROCEDURE `pro_intodzqt`(
 IN djbh varchar(20),
 IN kdm varchar(30),
 IN kid varchar(40),
 IN kje varchar(20),
 IN vpm varchar(20),
 IN sdmc varchar(100), 
 IN opid varchar(100),
 IN qsm varchar(1000),
 out retrun int 
 )
BEGIN

insert into hxwxdzqts (djbh,czkdm,czkdid,kje,vpdm,openid,sdmc,qsm) 
SELECT djbh,kdm,kid,kje,vpm,opid,sdmc,qsm from ipos_vip where vpdm=vpm;
UPDATE ipos_czk  set push_state=1 where id=kid;
UPDATE hxwxdzqts  set zt=1 where czkdid=kid;
SET retrun=0;

end$$

drop PROCEDURE if exists pro_xfzsq;
DELIMITER $$  
CREATE DEFINER=`root`@`%` PROCEDURE `pro_xfzsq`(
IN qdm varchar(60),
IN qje int,
IN yxrq VARCHAR(20),
IN sysm varchar(20),
IN sddm varchar(20),
IN xph varchar(20),
IN dhlx varchar(20)

)
BEGIN 
insert INTO ipos_czk (czkdm,lb_id,org_id,zd_id,syfw,sysp,`status`,qy,qyrq,yxrq,ty,dqje,cz_yxrq,czje,czrq,zcje,jdr,jdrq,xgr,spzk,lqzk,lqlx,zk,push_state,ksrq,bds,bz)
SELECT   qdm ,43,1,id,0,0,1,1,UNIX_TIMESTAMP(NOW()),UNIX_TIMESTAMP(yxrq),0,qje, UNIX_TIMESTAMP(yxrq),
         qje,UNIX_TIMESTAMP(NOW()),qje,sysm,UNIX_TIMESTAMP(NOW()),dhlx,0,1,43,0,0,0,0,xph
from com_base_kehu 
where khdm=sddm ;


insert into ipos_czklsz (pid,czk_id,czkdm,czdm,czmc,czsj,org_id,zd_id,zj,je,qmje,lxdj,bz)
select id,id,czkdm,0,dhlx,qyrq,1,zd_id,1,czje,zcje,bz,sysm
from  ipos_czk
where czkdm= qdm;
end$$

drop PROCEDURE if exists pro_oldvipwxzc;
DELIMITER $$  
CREATE DEFINER=`baison`@`%` PROCEDURE `pro_oldvipwxzc`(
vipcardid varchar(20),
vipcard varchar(20),
vipname varchar(40),
vipsj varchar(20),
vipsex varchar(10),
vipsr datetime,
vipopenid varchar(100),
dqrq datetime ,
shop varchar(100),
dz varchar(100),
jsr varchar(100),
out returnq INT
)
BEGIN
if NOT EXISTS (select vpdm from ipos_vip where vpdm=vipcard) THEN
 set  returnq= 1; 
ELSEIF vipname='' THEN
set  returnq= 2; 
ELSEIF vipsj=''  THEN
set  returnq= 3; 
ELSEIF vipsex='' THEN
set  returnq= 4; 
ELSEIF DAY(vipsr)= DAY(CURRENT_DATE ())   THEN
set  returnq= 5; 
ELSE
 update ipos_vip set vpmc=vipname,openid=vipopenid,dqrq=UNIX_TIMESTAMP(dqrq),zcrq=UNIX_TIMESTAMP(NOW()),lbdm='316',lb_id=7,xldm='06',xl_id=7,sj=vipsj,sr1=UNIX_TIMESTAMP(vipsr),zk=0.69 where vpdm=vipcard;
 update ipos_customer set gkmc=vipname,xb=vipsex , sj=vipsj,sr1=UNIX_TIMESTAMP(vipsr) ,dz=dz,jdr='微信',
                          zjh=vipcardid,jddy=jsr 
  where  gkdm in (select gkdm from ipos_vip where vpdm=vipcard); 
 set  returnq= 0;          
end if;
end$$
drop PROCEDURE if exists pro_vipczyq;
DELIMITER $$  
CREATE DEFINER=`baison`@`%` PROCEDURE `pro_vipczyq`(
 
 card varchar(20)  ,
 yqrq datetime,
 out returnq int
 )
BEGIN
 
 if card='' THEN
 set returnq= 1;
ELSEIF  not EXISTS  (select 1 from ipos_vip where vpdm=card) THEN
 set returnq= 3;
ELSE
UPDATE ipos_vip SET dqrq=UNIX_TIMESTAMP(yqrq) WHERE vpdm=card;
set returnq= 0;
end if;
end $$

drop PROCEDURE if exists pro_vipmodpass;
DELIMITER $$  
CREATE DEFINER=`baison`@`%` PROCEDURE `pro_vipmodpass`(
 vpcode varchar(20)  ,
 passwordnew varchar(100)  ,
 passwordold varchar(100)  , 
 out returnq   int 
 )
BEGIN
 

 IF not EXISTS  (select * from ipos_vip where vpdm=vpcode) THEN
  set  returnq=3;
	
	ELSEIF  EXISTS  (select * from ipos_vip where password<>passwordold and vpdm=vpcode) THEN
  set  returnq=2;	
ELSE

UPDATE ipos_vip set password=passwordnew where vpdm=vpcode;
 set  returnq=0;
end if;
end $$


drop PROCEDURE if exists pro_vipwxzc;
DELIMITER $$  
CREATE DEFINER=`baison`@`%` PROCEDURE `pro_vipwxzc`(
 name varchar(20)  ,
 cardid varchar(30)  ,
  sj varchar(11)  ,
 sr datetime ,
 sex varchar(4)  ,
 shop varchar(20)  ,
 dqrq datetime,
 dz varchar(60) ,
 openid varchar(20)  ,
 jsr  varchar(20) ,
 out returnq   int  
 )
BEGIN
 
 if sj='' THEN
 
 set  returnq= 1;
 ELSEIF name='' THEN
 set  returnq= 2;
 ELSEIF  EXISTS  (select * from ipos_vip where vpdm=sj) THEN
 set  returnq= 3;
ELSE
insert into ipos_customer (gkdm,gkmc,qy_id,org_id,zd_id,dy_id,vip,xb,sr1,maritalstatus,sj,zjh,qy,ty,jdr,jdrq,qy_dm,                           org_dm,zd_dm,lylx,dj_zd,jddy,dz)
select  sj,name,28,1,id,0,2,sex,UNIX_TIMESTAMP(sr),0,sj,cardid,0,0,'微信',UNIX_TIMESTAMP(NOW()),000,000,
        shop,6,1022,jsr,dz
from com_base_kehu  where khdm=shop;

insert into ipos_vip(vpdm,vpmc,xl_id,lb_id,gk_id,org_id,zd_id,syfw,status,qy,qyrq,yxrq,ty,jdr,jdrq,lbdm,xldm,orgdm,zddm,gkdm,lylx,fklx_id,jfsyfw,sj,sr1,zk,zdr,czfw,zcrq,dqrq,openid)
							select  sj,name,2,7,a.id,1,b.id,0,1,1,UNIX_TIMESTAMP(NOW()),UNIX_TIMESTAMP('2029-12-30'),0,'微信',UNIX_TIMESTAMP(NOW()),'316','06','000',shop,a.gkdm,6,1,0,sj,UNIX_TIMESTAMP(sr),0.69,'微信',0,UNIX_TIMESTAMP(NOW()),
											UNIX_TIMESTAMP(dqrq),openid
from ipos_customer  a ,com_base_kehu b where  b.khdm=a.zd_dm and a.gkdm=sj;
 set  returnq=0;
end if;
end $$


drop PROCEDURE if exists pro_vipmodpass;
DELIMITER $$  
CREATE DEFINER=`baison`@`%` PROCEDURE `pro_vipmodpass`(
 vpcode varchar(20)  ,
 passwordnew varchar(100)  ,
 passwordold varchar(100)  , 
 out returnq   int 
 )
BEGIN
 

 IF not EXISTS  (select * from ipos_vip where vpdm=vpcode) THEN
  set  returnq=3;
	
	ELSEIF  EXISTS  (select * from ipos_vip where password<>passwordold and vpdm=vpcode) THEN
  set  returnq=2;	
ELSE

UPDATE ipos_vip set password=passwordnew where vpdm=vpcode;
 set  returnq=0;
end if;
end $$


drop PROCEDURE if exists pro_vipxkls;
DELIMITER $$  
CREATE DEFINER=`baison`@`%` PROCEDURE `pro_vipxkls`(
 card varchar(20)  ,
 yqrq datetime,
 xkrq datetime,
 je int ,
out returnq int
 )
BEGIN
 
 if card='' THEN
 
 set returnq= 1;
 ELSEIF je=0 THEN
 set returnq= 2;
 ELSEIF  not EXISTS  (select * from ipos_vip where vpdm=card) THEN
  set returnq= 3;
ELSE
insert into vipxkls(card,xkrq,yqrq,zfrq,xfje) values (card,UNIX_TIMESTAMP(xkrq),UNIX_TIMESTAMP(yqrq),UNIX_TIMESTAMP(NOW()),je);
set   returnq= 0;
end if;
end $$

drop PROCEDURE if exists pro_vpczdpfx;
DELIMITER $$  

CREATE DEFINER=`baison`@`%` PROCEDURE `pro_vpczdpfx`(
 IN strq  VARCHAR(20) ,
 IN	edrq VARCHAR(20),
 IN sd VARCHAR(20)
 )
BEGIN
drop table if exists tmp_czouthx;
 CREATE TEMPORARY TABLE  tmp_czouthx
 (
  id INT auto_increment primary key, 
	sddm VARCHAR(20),
	khmc VARCHAR(40),
	qcje decimal(15,1),
	kfje decimal(15,1),
	hkje decimal(15,1),
	czye decimal(15,1),
	tkje decimal(15,1),
	tzje decimal(15,1),
	xfje decimal(15,1),
	qmje decimal(15,1)
 );
 insert into tmp_czouthx (sddm,khmc,qcje,kfje,hkje,czye,tkje,tzje,xfje,qmje)
select zd_id,khmc,sum(qcje),sum(czje),sum(zsje),sum(czje)+sum(zsje),sum(tkje),sum(tzje),sum(xfje),sum(qmje) from 
(
select a.zd_id,b.khmc,sum(a.je*a.zj) qcje,0 as czje,0 as zsje, 0 as tkje, 0 as tzje , 0 as xfje ,0 as qmje from ipos_vpczklsz a,	com_base_kehu b where date_format(a.lastchanged,'%Y-%m-%d')  <strq  and a.zd_id=b.id and b.khdm like sd 
group by a.zd_id
UNION
select a.zd_id,b.khmc,0 as qcje,sum(a.fkje*a.zj) czje,sum(a.hkje*a.zj) zsje,0 as tkje, 0 as tzje , 0 as xfje ,0 as qmje from 		  ipos_vpczklsz a,com_base_kehu b
 where date_format(a.lastchanged,'%Y-%m-%d')>=strq  and date_format(a.lastchanged,'%Y-%m-%d')<=edrq and a.czmc='充值'
 and a.zd_id=b.id and b.khdm like sd 
group by a.zd_id
UNION
select a.zd_id,b.khmc,0 as qcje,0 as czje,0 as zsje, sum(a.fkje*a.zj) tkje,0 as tzje , 0 as xfje ,0 as qmje from ipos_vpczklsz a
,com_base_kehu b where date_format(a.lastchanged,'%Y-%m-%d')>=strq  and date_format(a.lastchanged,'%Y-%m-%d')<=edrq 
and a.czmc='充值退款' and a.zd_id=b.id and b.khdm like sd 
group by a.zd_id
UNION
select a.zd_id,b.khmc,0 as qcje,0 as czje,0 as zsje,0 as tkje,sum(a.fkje*a.zj) tzje, 0 as xfje ,0 as qmje from ipos_vpczklsz a 
,com_base_kehu b where date_format(a.lastchanged,'%Y-%m-%d')>=strq  and date_format(a.lastchanged,'%Y-%m-%d')<=edrq
and a.czmc='充值调整'  and a.zd_id=b.id and b.khdm like sd 
group by a.zd_id
UNION
select a.zd_id,b.khmc,0 as qcje,0 as czje,0 as zsje,0 as tkje,0 as tzje ,sum(a.je*a.zj) xfje,0 as qmje from ipos_vpczklsz a ,com_base_kehu b  where date_format(a.lastchanged,'%Y-%m-%d')>=strq  and date_format(a.lastchanged,'%Y-%m-%d')<=edrq     and a.czmc='消费'  and a.zd_id=b.id and b.khdm like sd 
group by a.zd_id
UNION
select zd_id,b.khmc,0 as qcje,0 as czje,0 as zsje,0 as tkje,0 as tzje ,0 as xfje ,sum(je*zj) qmje from ipos_vpczklsz a ,com_base_kehu b where  date_format(a.lastchanged,'%Y-%m-%d')<=edrq and a.zd_id=b.id and b.khdm like sd 
group by a.zd_id
) a 
group by zd_id;
select khmc,qcje,kfje,hkje,czye,tkje,tzje,xfje,qmje from  tmp_czouthx;
 end $$

drop PROCEDURE if exists pro_vpfqxf;
DELIMITER $$  

CREATE DEFINER=`baison`@`%` PROCEDURE `pro_vpfqxf`(
	 IN strq  VARCHAR(20),
	 IN	edrq  VARCHAR(20),
	 IN jgdm  VARCHAR(20),
	 IN cxdm  VARCHAR(20),
	 IN qlbdm VARCHAR(20)
 )
BEGIN
	drop table if exists tmp_czouthx;
 CREATE TEMPORARY TABLE  tmp_czouthx
 (
  id INT auto_increment primary key, 
  
	sddm VARCHAR(20),
	khmc VARCHAR(80),
	lbdm  VARCHAR(80),
	lbmc  VARCHAR(80),
	hddm  VARCHAR(80),
	hdmc VARCHAR(80),
	qmz VARCHAR(40),	
	fqzs int(11)  ,
	fqje decimal(15,1) ,
	yqzs int(11) ,
	yqxsje decimal(15,1) ,
	yqxsyj decimal(15,1) 
 );
 insert into tmp_czouthx (sddm,khmc,lbdm,lbmc,hddm,hdmc,fqzs,qmz,fqje)
										select khdm,khmc,lbdm,lbmc,a.ydjh,a.bz,IFNULL(count(czkdm),0) fqzs,IFNULL(je,0),
										IFNULL(je*count(czkdm),0) fqje from (
  select a.djbh,a.zdr,a.ydjh,a.bz, e.lbmc,e.lbdm, date_format(from_unixtime(a.rq), '%Y-%m-%d') as rq,date_format(from_unixtime(a.yxrq), '%Y-%m-%d') as yxrq, a.je, b.czkdm,d.khdm,d.khmc
	from ipos_czkzcd a, ipos_czkzcdmx b, ipos_czkzcdfw c,com_base_kehu d,ipos_czklb e
	 where a.id = b.pid and b.pid = c.pid and c.zd_id = d.id and  e.id=a.lb_id and (a.lqlx =1 or e.dck=1)
	  UNION
	select a.djbh,a.zdr,a.ydjh,a.bz, e.lbmc,e.lbdm,date_format(from_unixtime(a.rq), '%Y-%m-%d') as rq,date_format(		from_unixtime(	a.yxrq), '%Y-%m-%d') as yxrq, a.je, b.czkdm,  '000','公司通用'
		 from  ipos_czkzcd a, ipos_czkzcdmx b, ipos_czklb e
		  where a.id = b.pid and  e.id=a.lb_id and syfw=0 and (a.lqlx =1 or e.dck=1) ) a	
			GROUP BY khdm,khmc,lbmc,lbdm,je,a.ydjh,a.bz;
			
	UPDATE tmp_czouthx  INNER JOIN  
	(select khdm,khmc,lbdm,lbmc,ydjh,bz,qje,IFNULL(count(dm),'0') syzs,IFNULL( sum(je) ,'0') je,IFNULL( sum(yj) ,'0')  yj  from
			(select date_format(from_unixtime(a.rq), '%Y-%m-%d') as rq,f.ydjh,f.bz,b.dm,a.je as je,e.khdm,e.khmc,d.lbmc,f.je as 							qje,g.yj as yj,d.lbdm
						from ipos_qtlsd a, ipos_qtlsdjs b,com_base_kehu e,ipos_czkzcdmx c,ipos_czklb d ,ipos_czkzcd f,
					(select pid,sum(je) yj from ipos_qtlsdjs where js_id in (1,2,3,4,1007,1008,1009,1010,1011,1012) GROUP BY pid) g
					 where a.id=b.pid and  b.dm =c.czkdm and e.id=a.zd_id and c.czkdm=b.dm and c.pid=f.id and d.id=f.lb_id  
											and (f.lqlx=1 or d.dck=1)  and g.pid=a.id and 
						date_format(from_unixtime(a.rq), '%Y-%m-%d')>=strq and date_format(from_unixtime(a.rq), '%Y-%m-%d')<=edrq and 
            e.khdm like jgdm and f.ydjh like cxdm and d.lbdm like qlbdm) a											
				GROUP BY khdm,khmc,lbdm,lbmc, qje,ydjh,bz  ) a
	ON tmp_czouthx.sddm =a.khdm and  tmp_czouthx.lbdm=a.lbdm and tmp_czouthx.hddm=a.ydjh
	SET  tmp_czouthx.yqzs=IFNULL(a.syzs,'0') ,tmp_czouthx.yqxsje=IFNULL(a.je,'0'),tmp_czouthx.yqxsyj=IFNULL(a.yj,'0') ;
	select sddm,khmc,lbdm,lbmc,hddm,hdmc,qmz,fqzs,fqje,yqzs,yqxsje,yqxsyj from 
 (select sddm,khmc,lbdm,lbmc,hddm,hdmc,qmz,IFNULL(fqzs,0)fqzs,IFNULL(fqje,0)fqje,IFNULL(yqzs,0)yqzs,IFNULL(yqxsje,0) yqxsje,IFNULL(yqxsyj,0) yqxsyj from  tmp_czouthx  order by sddm,hddm,lbmc) a
   ;
  end $$

drop PROCEDURE if exists pro_vpryzctj;
DELIMITER $$  
CREATE DEFINER=`baison`@`%` PROCEDURE `pro_vpryzctj`(
 IN strq  VARCHAR(20) ,
 IN	edrq VARCHAR(20),
	IN sd VARCHAR(20),
	IN zdr VARCHAR(20)
 )
BEGIN
drop table if exists tmp_czouthx;
 CREATE TEMPORARY TABLE  tmp_czouthx
 (
  id INT auto_increment primary key, 
  czrq int,
	sddm VARCHAR(20),
	khmc VARCHAR(40),
	czzdr VARCHAR(40),
	vp_id int(11),
	kfje decimal(15,1),
	hkje decimal(15,1),
	tkje decimal(15,1),
	tkhk decimal(15,1),
	tzje decimal(15,1)	
 ) ;
 
 drop table if exists tmp_hxczout;
 CREATE TEMPORARY TABLE  tmp_hxczout
 (  
  czrq VARCHAR(40),
	khmc VARCHAR(40),
	czzdr VARCHAR(40),
	kfje decimal(15,1),
	hkje decimal(15,1),
	tkje decimal(15,1),
	tzje decimal(15,1),
	czje decimal(15,1)
 ) ;


  insert into tmp_czouthx (czrq,sddm,khmc,czzdr,vp_id,kfje,hkje)
  select a.rq,b.id,b.khmc,a.zdr,a.vp_id,sum(a.fkje)fkje,sum(a.hkje) hkje from  ipos_vpczd a,com_base_kehu  b where a.zd_id=b.id
		and a.rq >= UNIX_TIMESTAMP(strq) and a.rq<= UNIX_TIMESTAMP(edrq) and b.khdm like sd and a.zdr like zdr
		GROUP BY a.rq,b.id,b.khmc,a.zdr,a.vp_id;
 
	UPDATE tmp_czouthx INNER JOIN ipos_refundorder on tmp_czouthx.vp_id =ipos_refundorder.vp_id
	and tmp_czouthx.czrq =ipos_refundorder.rq 
	and tmp_czouthx.sddm=ipos_refundorder.zd_id 
	AND tmp_czouthx.czzdr=ipos_refundorder.zdr
	SET tmp_czouthx.tkje=ipos_refundorder.tkje;

	UPDATE tmp_czouthx  INNER JOIN  (select b.rq,a.vp_id,a.tzje from ipos_vpcztzdmx  a ,ipos_vpcztzd b where a.pid=b.id) a
	ON tmp_czouthx.vp_id =a.vp_id and  tmp_czouthx.czrq=a.rq
	SET  tmp_czouthx.tzje=a.tzje ;
 insert into tmp_hxczout (czrq,khmc,czzdr,kfje,hkje,tkje,tzje,czje)
	select  date_format( from_unixtime(czrq),'%Y-%m-%d') rq,khmc,czzdr,SUM(kfje) kfje,SUM(hkje) hkje,IFNULL(SUM(tkje),'0') tkje ,IFNULL(SUM(tzje),'0') tzje,IFNULL(SUM(kfje)-IFNULL(SUM(tkje),0)+  IFNULL(SUM(tzje),0),0) czje 
				 from  tmp_czouthx GROUP BY czrq,khmc,czzdr;
	select czrq,khmc,czzdr,kfje,hkje,tkje,tzje,czje from tmp_hxczout  order by czrq,khmc;
  end $$


drop PROCEDURE if exists pro_wxjftz;
DELIMITER $$  
CREATE DEFINER=`baison`@`%` PROCEDURE `pro_wxjftz`( vipcard VARCHAR(20) ,
   OUT returnq INT
 )
BEGIN
DECLARE varcet int;
DECLARE  dh VARCHAR(100) ;
set dh=CONCAT('WX',DATE_FORMAT(now(), '%Y%m%d%H%i%s'));
set varcet=(select  DISTINCT 1 from ipos_jftzdmx a,ipos_vip b where b.vpdm=vipcard AND a.vp_id=b.Id  and   a.pid 
           in (select id from ipos_jftzd where date_format( from_unixtime(rq),'%Y-%m-%d')=date_format( NOW(),'%Y-%m-%d')));

if varcet=1 THEN
SET returnq=3;
else
insert into ipos_jftzd (djbh,rq,tzlx,tzjf,zdr,zdrq,zd_id)
 select dh,UNIX_TIMESTAMP(NOW()),'0','1','微信',UNIX_TIMESTAMP(NOW()),zd_id
from ipos_vip
where vpdm=vipcard ;

insert into ipos_jftzdmx(pid,vp_id,dqjf,tzjf,bz,vpdm)
 select a.Id ,b.Id ,b.dqjf,1,'微信签到' ,b.vpdm from ipos_jftzd a ,ipos_vip b
 where  vpdm=vipcard and djbh=dh;

insert into ipos_vplsz(vp_id,vpdm,vpmc,jfcz,czmc,czsj,zd_id,zddm,zdmc,qcjf,qcsl,qcje,tzfx,zj,jf,sl,je,qmjf,qmje,lxdj,yxjf,lylx,dy_id,lb_id)
select                 a.id,a.vpdm,a.vpmc,5,'微信签到',a.sj,a.zd_id,a.zddm,b.khmc,a.dqjf,0,0,0,1,1,0,0,(a.dqjf+1),1,d.djbh,1,0,0,1
				
from ipos_vip a ,com_base_kehu b ,ipos_jftzdmx  c,ipos_jftzd d
where a.zddm=b.khdm  and a.vpdm=vipcard and c.pid=d.Id and c.vpdm=a.vpdm and d.djbh=dh;

update ipos_vip set dqjf=(dqjf+1) where vpdm=vipcard;
UPDATE ipos_jftzd as a SET sp='1' ,spr='微信',sprq=UNIX_TIMESTAMP(NOW())   
WHERE a.djbh =dh;
SET returnq=0;
end if;
end $$


drop PROCEDURE if exists pro_wxjftz;
DELIMITER $$  
CREATE DEFINER=`baison`@`%` PROCEDURE `pro_ysjvipzc`(vipcard varchar(20),
vipname varchar(40),
vipsj varchar(20),
vipsex varchar(10),
vipsr datetime,
vipopenid varchar(100),
 OUT returnq  int
)
BEGIN
if NOT EXISTS (select 1 from ipos_vip where vpdm=vipcard AND lb_id=10) THEN
 SET  returnq=1 ;
ELSEIF vipname='' THEN
SET    returnq=2 ;  
ELSEIF vipsj=''  THEN
SET returnq=3  ;
ELSEIF vipsex='' THEN
SET returnq=4;
ELSEIF DAY(vipsr)= DAY(CURRENT_DATE ())   THEN
SET returnq=5;
ELSE
 update ipos_vip set vpmc=vipname, openid= vipopenid,sj=vipsj,sr1=UNIX_TIMESTAMP(vipsr),
        ipos_vip.birth=
				CONCAT_WS('-',SUBSTR(vipsr,6,2),SUBSTR(vipsr,9,10)) 
 where vpdm=vipcard;
 update ipos_customer set gkmc=vipname,xb=vipsex , sj=vipsj,sr1=UNIX_TIMESTAMP(vipsr),
         ipos_customer.birth=
				CONCAT_WS('-',SUBSTR(vipsr,6,2),SUBSTR(vipsr,9,10)) 
  where  gkdm in (select gkdm from ipos_vip where vpdm=vipcard);
  
  SET returnq=0;
    
end if;

end $$

drop PROCEDURE if exists pro_vipwxzcss;
DELIMITER $$  
CREATE DEFINER=`ipos`@`%` PROCEDURE `pro_vipwxzcss`(
 code varchar(20)  ,
 name varchar(20)  ,
 sj varchar(11)  ,
 sr datetime ,
 sex varchar(4)  ,
 shop varchar(20)  ,
 dz varchar(60) ,
 openid varchar(20)  ,
 out returnq   int 
 )
BEGIN
 
 if sj='' THEN
 
 set  returnq=1;
 ELSEIF name='' THEN
 set  returnq=2;

 ELSEIF  EXISTS  (select * from ipos_vip where vpdm=sj) THEN
  set  returnq=3;
ELSE

insert into ipos_customer (gkdm,gkmc,qy_id,org_id,zd_id,dy_id,vip,xb,sr1,maritalstatus,sj,zjh,qy,ty,jdr,jdrq,qy_dm,org_dm,zd_dm,lylx,dj_zd,country_id,education_id,job_id,sx1_list,sx2_list,sx3_list,sx4_list,sx5_list,sx6_list,job_dm,country_dm,education_dm,vpsx1_dm,vpsx2_dm,vpsx3_dm,vpsx4_dm,vpsx5_dm,vpsx6_dm,dj_org)
									select  code,name,35,1,id,0,2,sex,UNIX_TIMESTAMP(sr),0,sj,code,0,0,'微信',UNIX_TIMESTAMP(NOW()),'1','000',shop,6,id,1,1,1,1,1,1,1,1,1,'000','000','000','000','000','000'	,'000','000','000','1'
										from com_base_kehu  where khdm=shop;

insert into ipos_vip(pid,vpdm,vpmc,xl_id,lb_id,gk_id,org_id,zd_id,syfw,status,qy,qyrq,yxrq,ty,jdr,jdrq,lbdm,xldm,sszd_id,orgdm,zddm,gkdm,lylx,fklx_id,jfsyfw,sj,sr1,zk,zdr,czfw,openid)
							select  a.Id,code,name,6,6,a.id,1,b.id,0,1,1,UNIX_TIMESTAMP(NOW()),UNIX_TIMESTAMP('2029-12-30'),0,'微信',UNIX_TIMESTAMP(NOW()),'16','05',b.id,'000',shop,a.gkdm,6,1,0,sj,UNIX_TIMESTAMP(sr),1,'微信',0,openid
										
from ipos_customer  a ,com_base_kehu b where  b.khdm=shop and a.gkdm=sj;
 set  returnq=0;
end if;
end $$



///衣世界系统自动送券
drop PROCEDURE if exists pro_xtsqnew;
DELIMITER $$
CREATE DEFINER=`ipos`@`%` PROCEDURE `pro_xtsqnew`(
IN qdm varchar(60), /* 券代码*/
IN qje int, /* 券金额*/
IN yxrq VARCHAR(20), /* 有效金额*/
IN sysm varchar(20), /* 使用说明*/
IN sddm varchar(20), /* 商店代码*/
IN bz varchar(20), /* 备注说明*/
IN dhlx varchar(20), 
IN lbid varchar(20), /* 券类别代码*/
IN fw  varchar(20), /* 使用范围标识1代表插入ipos_czkfw表控制店铺使用范围*/
IN sp  varchar(20), /* 商品范围标识1代表插入ipos_czksp表控制商品使用范围*/
IN alqdm  varchar(20), /* 案例代金券*/
OUT result INT(1)
)

BEGIN 
 /* 使用事务的时候定义变量必须在最前面，然后才是执行成功标识后面才是事务*/
DECLARE t_error INTEGER DEFAULT 0;
DECLARE alqid INT ;
DECLARE qdmid INT;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1;
SET alqid=(select id FROM ipos_czk  where czkdm= alqdm);
START TRANSACTION; 
insert INTO ipos_czk (czkdm,lb_id,org_id,zd_id,syfw,sysp,`status`,qy,qyrq,yxrq,ty,dqje,cz_yxrq,czje,czrq,zcje,jdr,jdrq,xgr,spzk,lqzk,lqlx,zk,push_state,ksrq,bds,bz)
                SELECT   qdm ,lbid,1,id,0,0,1,1,UNIX_TIMESTAMP(NOW()),UNIX_TIMESTAMP(yxrq),0,qje, UNIX_TIMESTAMP(yxrq),
         qje,UNIX_TIMESTAMP(NOW()),qje,sysm,UNIX_TIMESTAMP(NOW()),dhlx,0,1,1,1,0,0,0,bz
from com_base_kehu 
where khdm=sddm ;


insert into ipos_czklsz (pid,czk_id,czkdm,czdm,czmc,czsj,org_id,zd_id,zj,je,qmje,lxdj,bz)
select id,id,czkdm,0,dhlx,qyrq,1,zd_id,1,czje,zcje,bz,sysm
from  ipos_czk
where czkdm= qdm;

IF fw = 1 THEN /* 判断使用范围*/
INSERT into ipos_czkfw (pid,tn_id,org_id,zd_id)
select id,0,0,zd_id from  ipos_czk  where czkdm= qdm;
END IF;

IF sp = 1 THEN /* 判断商品有案例商品时候，案例商品很复杂*/
  SET qdmid=(select id FROM ipos_czk  where czkdm= qdm);
	INSERT into ipos_czksp (pid,tn_id,xh,spjs,jtsp,sptj,lwsp)
	select qdmid,tn_id,xh,spjs,jtsp,sptj,lwsp FROM ipos_czksp WHERE pid=alqid;
END IF;

IF t_error = 1 THEN  
 ROLLBACK;
 ELSE 
    COMMIT;
END IF; 
SET result = t_error;
end $$