GO/****** Object:  StoredProcedure [dbo].[Cust_dzd]    Script Date: 11/28/2019 15:43:17 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO ALTER proc [dbo].[Cust_dzd](@shopid varchar(max), @bdate varchar(10),@edate varchar(10))   ASBEGIN SET NOCOUNT ON   --linjc 20191114 8244蓝萌新增自定义报表【对账单】  if exists (select * from tempdb.dbo.sysobjects where name = '#temptjr')     drop table #temptjr     create  TABLE #temptjr (     nid int IDENTITY(1,1),   iodate varchar(20),   c_xilmc varchar(50),   code varchar(50),   colorid varchar(50),   djlx varchar(50),   scripno varchar(50),   colorname varchar(50),   amount int,   price  decimal(18,2),   sale   decimal(18,2),   notes varchar(500)   )   declare @Index intdeclare @tmp intdeclare @fNO varchar(50)declare @sizefields varchar(1000)declare @upsizefields1 varchar(5000)declare @upsizefields2 varchar(5000)declare @upsizefields3 varchar(max)declare @fieldname varchar(50)declare @lSql varchar(5000)set @index=0  SET @tmp=0set @sizefields =''set @upsizefields1 =''set @upsizefields2 =''set @upsizefields3 =''set @lSql='' --取表最长长度SELECT top 1 @index=COUNT(groupno) FROM r_size  group BY groupno ORDER BY count(groupno) descSELECT @index=ISNULL(@Index,20)while (@index>@tmp) begin  set @fNO= convert(varchar(50),@tmp)  set @sizefields = @sizefields+' ,xsize'+@fNO  set @fieldname='xsize'+@fNO  set @lSql = @lSql + ' alter table #temptjr add '+@fieldname+' int'  set @upsizefields1 = @upsizefields1 + ' a.xsize'+@fNO+'=b.xsize'+@fNO+','  set @upsizefields2 = @upsizefields2 + ' ,sum(case when b.xsize='+@fNO+' then amount else 0 end) as xsize'+@fNO   set @upsizefields3 = @upsizefields3 + ' xsize'+@fNO+' =case when isnull(xsize'+@fNO +',0)=0 then null else xsize'+@fNO+' end,'set  @tmp=@tmp+1endset @upsizefields1 = @upsizefields1 + 'amount=b.amount'set @upsizefields2 = @upsizefields2 + ',sum(amount) as amount'
exec(@lSql) 
    DECLARE @aSql varchar(max)declare @pwhere varchar(max)declare @pwhere1 varchar(max)declare @pwhere2 varchar(max)declare @vpwhere varchar(max)declare @qmwhere varchar(max)declare @qcwhere varchar(max)set @pwhere = ''set @pwhere1 = ''set @pwhere2 = ''set @vpwhere = ''set @qmwhere = ''set @qcwhere = ''set @aSql = '' if @shopid <> '' and @shopid <> ''''''begin   set @pwhere = ' and a.gto  in ( '+@shopid+' )';   set @pwhere1 = @pwhere1 +  ' and a.gfrom  in ( '+@shopid+' )';   set @pwhere2 = @pwhere2 +  ' and a.shopid  in ( '+@shopid+' )';   set @qmwhere = @qmwhere +  ' and a.shopid  in ( '+@shopid+' )';   set @qcwhere = @qcwhere +  ' and a.shopid  in ( '+@shopid+' )';   set @vpwhere = @vpwhere +  ' and a.shopid  in ( '+@shopid+' )';end if @bdate <> '' and @bdate <> ''''''begin   set @pwhere = @pwhere +  ' and convert(varchar(100),a.d_cwshenh,112)>='+@bdate+'';   set @pwhere1 = @pwhere1 +  ' and convert(varchar(100),a.d_cwshenh,112)>='+@bdate+'';   set @pwhere2 = @pwhere2 +  ' and convert(varchar(100),a.d_shenh,112)>='+@bdate+'';   set @qcwhere = @qcwhere +  ' and a.iodate<'+@bdate+'';end  if @edate <> '' and @edate <> ''''''begin   set @pwhere = @pwhere + ' and convert(varchar(100),a.d_cwshenh,112)<='+@edate+'';   set @pwhere1 = @pwhere1 + ' and convert(varchar(100),a.d_cwshenh,112)<='+@edate+'';   set @pwhere2 = @pwhere2 + ' and convert(varchar(100),a.d_shenh,112)<='+@edate+'';   set @qmwhere = @qmwhere + ' and iodate<='+@edate+''end set @aSql = ' insert into #temptjr(djlx,code,sale)               select '''',''前期欠款'',sum(isnull(m_yings,0)-isnull(m_yingf,0)-isnull(m_fuk_jy,0)) from a_ysyf a --前期欠款                 where  ( 1=1 '+@qcwhere+' ) or (1=1 '+@vpwhere+' and isnull(c_zaiy,'''')=''期初设置'')  '                                            set @aSql = @aSql + ' insert into #temptjr(djlx,scripno,iodate,c_xilmc,code,colorid,colorname,price,amount,sale)                      select * from (                      select ''销售出库'' as djlx,a.scripno,convert(varchar(100),a.d_cwshenh,23) as iodate,p.c_xilmc,b.code,b.colorid,p.colorname,b.price,0 as amount,0 as sale                         from g_billm_ck a left join g_billd_ck b on a.scripno=b.scripno                         left join v_g_spdm p on b.code=p.code and b.colorid=p.colorid                        where ioflag=2 and iotype=1 and isnull(hdlflag,'''')=''JZ'' and isnull(a.c_zuangtm,''NA'') not in (''BC'',''CD'') '+@pwhere+'                        group by a.scripno,a.d_cwshenh,p.c_xilmc,b.code,b.colorid,p.colorname,b.price                      union all                       select ''销售退货'' as djlx,a.scripno,convert(varchar(100),a.d_cwshenh,23) as iodate,p.c_xilmc,b.code,b.colorid,p.colorname,b.price,0,0                         from g_billm_rk a left join g_billd_rk b on a.scripno=b.scripno                         left join v_g_spdm p on b.code=p.code and b.colorid=p.colorid                        where ioflag=1 and iotype=2 and isnull(hdlflag,'''')=''JZ'' and isnull(a.c_zuangtm,''NA'') not in (''BC'',''CD'') '+@pwhere1+'                        group by a.scripno,a.d_cwshenh,p.c_xilmc,b.code,b.colorid,p.colorname,b.price                      union all                       select ''应收款'' as djlx,a.scripno,convert(varchar(100),a.d_cwshenh,23) as iodate,'''',a.c_fkfs,'''','''',null,-sum(a.m_fuk),-sum(a.m_fuk)                        from a_billm_pay a                         where ioflag=1 and n_cwshenh=1  '+@pwhere1+' group by a.scripno,a.d_cwshenh,a.c_fkfs                      union all                       select ''销货费用单'' as djlx,a.scripno,convert(varchar(100),a.d_cwshenh,23) as iodate,'''',a.c_fklx,'''','''',null,-sum(a.totalmoney),-sum(a.totalmoney)                        from g_billm_SellingExpenses a                         where c_fklx=''对方代付'' and ioflag=1 and n_cwshenh=1  '+@pwhere1+' group by a.scripno,a.d_cwshenh,a.c_fklx                      union all                       select ''销货费用单'' as djlx,a.scripno,convert(varchar(100),a.d_cwshenh,23) as iodate,'''',a.c_fklx,'''','''',null,sum(a.totalmoney),sum(a.totalmoney)                        from g_billm_SellingExpenses a                         where c_fklx=''我方代付'' and ioflag=1 and n_cwshenh=1  '+@pwhere1+' group by a.scripno,a.d_cwshenh,a.c_fklx                         union all                       select ''应收调账单'' as djlx,a.c_pingzbh,convert(varchar(100),a.d_shenh,23) as iodate,'''',a.c_zaiy,'''','''',null,sum(isnull(a.m_yings,0)-isnull(a.m_yingf,0)),sum(isnull(a.m_yings,0)-isnull(a.m_yingf,0))                        from a_bill_ysyf a                         where   n_shenh=1  '+@pwhere2+' group by a.c_pingzbh,a.d_shenh,a.c_zaiy                                          ) a order by iodate'                                              set @aSql = @aSql +  ' insert into #temptjr(djlx,code,sale)               select '''',''期末欠款'',sum(isnull(m_yings,0)-isnull(m_yingf,0)-isnull(m_fuk_jy,0)) from a_ysyf a                  where 1=1  '+@qmwhere+' '                                                                                         set @aSql = @aSql +  ' update a set '+@upsizefields1+' from #temptjr a,                       (select b.scripno,b.code,b.colorid,b.price'+@upsizefields2+'                                                          from (select a.scripno,b.code,b.colorid,b.price,b.xsize,b.amount                             from g_billm_ck a left join g_billd_ck b on a.scripno=b.scripno                              where ioflag=2 and iotype=1 and isnull(hdlflag,'''')=''JZ''                              and isnull(a.c_zuangtm,''NA'') not in (''BC'',''CD'') '+@pwhere+'                          ) b group by b.code,b.colorid,b.price,b.scripno) b where a.scripno=b.scripno and a.code=b.code and a.colorid=b.colorid and                             a.price=b.price and a.djlx=''销售出库'''                          set @aSql = @aSql +  ' update a set '+@upsizefields1+' from #temptjr a,                       (select b.scripno,b.code,b.colorid,b.price'+@upsizefields2+' from (                              select a.scripno,b.code,b.colorid,b.price,b.xsize,-b.amount as amount                             from g_billm_rk a left join g_billd_rk b on a.scripno=b.scripno                              where ioflag=1 and iotype=2 and isnull(hdlflag,'''')=''JZ''                              and isnull(a.c_zuangtm,''NA'') not in (''BC'',''CD'') '+@pwhere1+'                          ) b group by b.code,b.colorid,b.price,b.scripno) b where a.scripno=b.scripno and a.code=b.code and a.colorid=b.colorid and                             a.price=b.price and a.djlx=''销售退货'''                                                                                  set @aSql = @aSql +  ' update #temptjr set sale=isnull(amount,0)*isnull(price,0) where djlx  in (''销售出库'',''销售退货'')'                      set @aSql = @aSql +  'update #temptjr set '+@upsizefields3+'                                          amount=case when isnull(amount,0)=0 then null else amount end,                                          price=case when isnull(price,0)=0 then null else price end,                                          sale=case when isnull(sale,0)=0 then null else sale end '                                                                               set @asql = @asql + ' select nid,iodate,c_xilmc,code,colorname,djlx,scripno '+@sizefields+',amount,price,sale,notes from #temptjr  order by nid'print(@asql) exec(@asql)
     END  RETURN