清理销售

1. 订单已经完成单未上传的数据

SELECT *  FROM x_orders  WHERE ( x_orders.done = 1 ) And ( x_orders.flag = '0' );

2. 清空数据

truncate table X_fkd;
truncate table x_fktz;
truncate table X_xsd;
truncate table x_xstz;
truncate table x_orders;
truncate table x_order;
truncate table x_order_details;
truncate table x_zfb_jytz;
truncate table x_bmpcoupon;
truncate table x_skpcoupon;

3. 每次更新时不存在的商品清除

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER  PROCEDURE [dbo].[Pro_updatepludata]
 AS
declare @spbh varchar(100),@txm varchar(100),@spmc varchar(50),@jldw varchar(20),@ggxh varchar(30),@flh varchar(100),@xszt varchar(2),@str4 varchar(50),@num1 numeric(20,0),@jjmode integer,@zjf varchar(100)
declare @lsj decimal(20,2),@hyj decimal(20,2),@pljs decimal(20,4),@plyhj decimal(20,2),@jmj decimal(20,2),@pfj decimal(20,2),@jj decimal(20,2),@xsl decimal(20,4),@xjj decimal(20,2)
declare @xqflag integer,@bzq integer,@num2 integer,@zkf integer,@hyzk integer,@pff integer,@thf integer ,@jff integer,@kcf integer,@clsj integer,@flag char(1)
declare @jdr varchar(20),@sell_pr varchar(20),@weight_flag char(1),@dept int
declare @jd_rq datetime,@modi_time datetime ,@moditime_local datetime

if exists(select 1 from j_dzc_ai)
begin
 --update j_spzl set xszt='0' where num1 not in (select plu_code from j_dzc_ai)
 delete from j_spzl where num1 not in (select plu_code from j_dzc_ai);
 delete from sys_lbxx where lx=1;
 insert into sys_lbxx(lx,id,name,id_ss,flag) select 1,cast(dept as varchar(100)),deptname,cast(dept as varchar(100)),'1' from j_dzc_ai group by dept,deptname order by dept;

end


    Declare csr_yg cursor for select plu_code,descr,sell_pr,weight_flag,dept from j_dzc_ai
              open csr_yg
              fetch next from csr_yg into @num1,@spmc,@sell_pr,@weight_flag,@dept
              while(@@fetch_status<> -1)
              begin
        set @spbh = cast(@num1 as varchar(100))
        set @lsj = cast(@sell_pr as decimal(18,2))
        set @jjmode=cast(@weight_flag as int)
        set @flh = cast(@dept as varchar(100))
        set @zjf = dbo.GetPYFirstLetter(@spmc)
                      if exists(select 1 from j_spzl where spbh=@spbh)
                      begin
                                select @moditime_local=modi_date from j_spzl where spbh=@spbh
                                update j_spzl set txm=@spbh,spmc=@spmc,ggxh='',jldw=case when @jjmode=0 then 'kg' else '件' end,zjf=@zjf,num1=@num1,flh=@flh,xsl=1,xjj=0,jj=0,lsj=@lsj,hyj=0,pljs=0,@plyhj=0,jmj=0,zkf=0,hyzk=0,pff=0,thf=0,jff=0,kcf=0,clsj=0,xqflag=0,bzq=1,num2=0,jjmode=@jjmode,str4=@spmc,xszt='1',flag='1',modi_date=getdate(),jd_rq=getdate() where spbh=@spbh
                      end
                      else
                      begin
                              insert into j_spzl(spbh,txm,spmc,num1,flh,jldw,zjf,ggxh,xsl,xjj,jj,lsj,hyj,jmj,pfj,pljs,plyhj,zkf,hyzk,pff,thf,jff,kcf,clsj,xqflag,bzq,num2,jjmode,str4,xszt,flag,modi_date,jd_rq) values(@spbh,@spbh,@spmc,@num1,@flh,case when @jjmode=0 then 'kg' else '件' end,@zjf,'',1,0,0,@lsj,0,0,0,0,0,0,0,0,1,0,0,0,0,1,0,@jjmode,@spmc,'1','1',getdate(),getdate());
                      end
                       fetch next from csr_yg into @num1,@spmc,@sell_pr,@weight_flag,@dept
              end
    close csr_yg
    deallocate csr_yg