/*      developed history      created by geton jew on 2014-6-23            popurse: for credit report  rpt_woer_statement            this sp invoked another store_procedure CAL_WOER_STATEMENT to calculate the opening data         modified by geton on 2014-6-25  decription: due to users want to show the open bal enevn no data during these period     use left join instead of inner join,      modified by geton on 2014-6-27  decription: added @soffice parameter as a filter  */        ALTER   PROCEDURE [RPT_WOER_STATEMENT]          @cus1 char(4)=‘Z21N‘,   @cus2 char(4)=‘Z23N‘,       @begdate char(10)=‘2014/06/1‘,       -- 我们的ERP主要日期是不记录时间的.   @enddate char(10)=‘2014/06/26‘,  @soffice varchar(10) = ‘ALL‘          AS          --               CREATE TABLE #RESULT      (       CUS CHAR(4),       TYPE VARCHAR(15),       BEGDATE CHAR(10),       ENDDATE CHAR(10),       AMT FLOAT,       OSAMT FLOAT,       ORIAMT FLOAT,       ORIOSAMT FLOAT      )          declare @where varchar(10)  if @soffice=‘ALL‘   set @where = ‘%‘  else   set @where = @soffice    --select @where  -- @BEGDATE 传进去要少一天,期初不包括当天,如输入的时间是2014.6.1--2014.6.27, 则期初的余额只到5.31.       DECLARE @cutday VARCHAR(10)    SELECT  @cutday=CONVERT(VARCHAR(10),DATEADD(DAY,-1,CONVERT(SMALLDATETIME,@begdate)),121)     INSERT INTO #RESULT      EXEC CAL_WOER_STATEMENT @cus1,@cus2,@cutday,@enddate,@where     -- 调用计算SP         SELECT A.*,B.TYPE AS RECTYPE,B.BEGDATE AS RECBEGDATE,B.ENDDATE AS RECENDDATE,     -isnull(B.AMT,0) AS RECAMT,     -isnull(B.OSAMT,0) AS RECOSAMT,     -isnull(B.ORIAMT,0) AS RECORIAMT,     -isnull(B.ORIOSAMT,0) AS RECORIOSAMT,      CUSNAME=SPACE(40),          CUSFAX=SPACE(20),          CUSTEL=SPACE(20),          CONTACT=SPACE(40)     INTO #FINANAL FROM #RESULT A LEFT JOIN #RESULT B    ON A.CUS=B.CUS AND B.TYPE=‘*Received*‘    -- 为方便期间发生额没有数据也能调出基本数据,于是先把期初余额和期间收款保存到时表后left join.WHERE A.TYPE=‘*Openning*‘         --select * from #RESULT        UPDATE   #FINANAL  SET  CUSNAME=CUS.NAME,CUSFAX=CUS.FAX,CUSTEL=CUS.TEL1,CONTACT=CUS.CONTACT       FROM   CUS WITH (NOLOCK)      WHERE  #FINANAL.CUS=CUS.CUS                 SELECT           SINV=SINV.SINV,          INVNO=(          CASE                  WHEN ISNULL(SINV.INVNUM,‘A‘)=‘A‘  THEN SINV.DN                 ELSE  SINV.INVNUM      END),                  PONO=SO.YOURREF,         SONUM=SO.SONUM,          MODEL=SODTL.PRONUM,          CUSMODEL=SODTL2.COMMENT,          OURPRO=SINVDTL.MODEL,        PRODESC = SPACE(250),          QTY=SINVDTL.QTY,           PRICE,      SUMQTY=PRICE*SINVDTL.QTY,      TAXQTY=(SINVDTL.QTY*CASE                 WHEN SO.TAXINC=‘TRUE‘ THEN SODTL.UPRICE/(1+SO.TAX)                               END)*0.17,          SIDATE=SINV.IDATE,          CUSNUM=SINV.CUS,          CPERIOD=SPACE(16),          TAX=SINV.TAX,          COMPANY=SPACE(40),          COMPANY1=SPACE(40),          TEL=SPACE(20),          FAX=SPACE(20),          ADDR1=SPACE(40),             CURR=SO.CURRENCY,          SUMALL=CONVERT(FLOAT,0),          FUOM=SINVDTL.SUOM,SO.F2,          INVDATE=CASE WHEN  ISNULL(SINV.INVDATE,‘1900/01/01‘)=‘1900/01/01‘THEN SINV.IDATE                                                                             ELSE SINV.INVDATE END    INTO           #TMP        FROM           SINV WITH (NOLOCK),SINVDTL WITH (NOLOCK),SODTL WITH (NOLOCK),SO WITH (NOLOCK),SODTL2 WITH (NOLOCK),CUS WITH(NOLOCK)       WHERE           SINV.SINV=SINVDTL.SINV AND          SINVDTL.SO=SODTL.SONUM AND          SINVDTL.MODEL=SODTL.OURPRO AND          SODTL.SONUM=SO.SONUM AND      SINV.CUS = CUS.CUS AND  CUS.OFFNUM LIKE @where and         CUS.CUS BETWEEN @cus1 AND  @cus2 AND        SINV.IDATE BETWEEN @begdate AND @enddate AND           SODTL2.SONUM=SODTL.SONUM AND           SODTL2.PRONUM=SODTL.PRONUM AND           SODTL2.LOT=SINVDTL.LOT             UPDATE #TMP      SET PRODESC=P.PRODESC FROM PRODUCT P WITH (NOLOCK)      WHERE P.PRONUM=#TMP.OURPRO                      UPDATE   #TMP        SET  COMPANY=COMPANYINFO.CONAME,ADDR1=COMPANYINFO.ADDR1,COMPANY1=COMPANYINFO.CONAME2,TEL=COMPANYINFO.TEL1,FAX=COMPANYINFO.FAX        FROM COMPANYINFO WITH (NOLOCK)               UPDATE   #TMP  SET  INVNO=JOU.XREF      FROM   JOU,#TMP        WHERE  #TMP.SINV=JOU.JOU AND #TMP.INVNO=‘‘               SELECT * FROM #FINANAL left join #TMP on #FINANAL.CUS=#TMP.CUSNUM                     DROP TABLE #TMP, #RESULT,#FINANAL 

  /*  Developed History  Created by Geton Jew on 2014-6-23   Popurse: For Credit Report  RPT_WOER_STATEMENT    this was invoked by another store_procedure: RPT_WOER_STATEMENT to show  the final data     -- [CAL_WOER_STATEMENT] ‘Z21N‘,‘Z21N‘,‘2014/6/1‘,‘2014/6/28‘,‘%‘    */     -- select * from SINV WHERE CUSNUM=         CREATE   procedure [dbo].[CAL_WOER_STATEMENT]        @cus1 char(4)=‘Z21N‘,   @cus2 char(4)=‘Z21N‘,     @begdate char(10)=‘2014/06/25‘,        @enddate char(10)=‘2014/06/26‘,  @where varchar(10) = ‘%‘         as        --        --grant execute on CAL_WOER_STATEMENT to public    CREATE TABLE #result    (     CUS char(4),     TYPE varchar(15),     BEGDATE char(10),     ENDDATE char(10),     AMT FLOAT,     OSAMT FLOAT,     ORIAMT FLOAT,     ORIOSAMT FLOAT    )              declare @delclose int        set @delclose=1              -- 送货单 , 包括DN单也在这, 这是程序设计的精妙之处. 一般的DN单放在另一个表格.select  SI.CUS,          J.JOUTYPE,          J.JOU,          J.IDATE,          AMT=JD.DR,          REF=convert(varchar(30),J.JOU),             OSAMT=JD.DR,       ORIAMT=JD.ORIAMT,         ORIOSAMT=JD.ORIAMT,         CURR=SI.CURR             into #T1 from  SINV SI with (nolock),JOU J with (nolock), JOUDTL JD with (nolock), CUS  with (nolock)      where         SI.JOUTYPE=J.JOUTYPE and          SI.SINV=J.JOU and         J.JOUTYPE=JD.JOUTYPE and          J.JOU=JD.JOU and          JD.ACC=SI.RECACC and           SI.CUS  between @cus1 and @cus2 and        SI.CUS = CUS.CUS and CUS.OFFNUM LIKE @where and    SI.IDATE <= @enddate and         (JD.R = ‘false‘  or JD.RR=0) and isnull(SI.DN,‘‘)<>‘NIL‘         --select * from #T1        -- 收款  截止为参数2日期   -- select * from CUS WHERE CUS=‘Z21N‘  insert #T1         select AR.CUS,          ‘RV‘,           AR.JOU,             AR.IDATE,          AMT=-AR.AMT,  -- AMT为收款金额，这里为负数         AR.CHK,            OSAMT=0,    -- OSAMT栏位为0         ORIAMT=-ORIAMT,   -- 收的是原幣金額       OSORIAMT=0,         CURR=AR.CURR      from AREC AR  with (nolock), CUS  with (nolock)      where   AR.CUS = CUS.CUS  and  --CUS.OFFNUM LIKE @where and      AR.CUS  between @cus1 and @cus2 and      AR.IDATE <= @enddate                       select  R2.JOU,R.CUS,MISCHRG=sum(R2.DR+R2.CR)    -- 手续费等    into #ta1        from ARECDTL2 R2  with (nolock), AREC R  with (nolock),CUS  with (nolock)    where          R.CUS=CUS.CUS and R.JOU=R2.JOU and R2.ACC<>R.AR and  -- 科目不等于该客户应收科目       R.CUS  between @cus1 and @cus2 and  CUS.OFFNUM LIKE @where and       R.IDATE <= @begdate      group by         R2.JOU,R.CUS           select  R2.JOU, R.CUS,DEPOSIT=sum(R2.DR-R2.CR),ODEPOSIT=sum(R2.ORIAMT)        into #tb1        from  ARECDTL2 R2  with (nolock),AREC R  with (nolock),CUS  with (nolock)      where  R.CUS = CUS.CUS and R.JOU=R2.JOU and  R2.ACC=R.AR and    -- 科目等于该客户应收科目，即应收减少，相当于有余钱在我们公司    R.CUS  between @cus1 and @cus2 and  CUS.OFFNUM LIKE @where and        R.IDATE <= @begdate      group by R2.JOU,R.CUS                update #T1 set AMT=#T1.AMT-#ta1.MISCHRG    -- AMT收款增加，即等于对冲票据总额    from #ta1       where #T1.JOUTYPE=‘RV‘ and #T1.JOU=#ta1.JOU AND #ta1.CUS = #T1.CUS               update #T1 set OSAMT=#tb1.DEPOSIT, ORIOSAMT=#tb1.ODEPOSIT  -- 这里更新为客户余的钱在我们公司，分别为本位币和原币       from #tb1         where #T1.JOUTYPE=‘RV‘ and #T1.JOU=#tb1.JOU  AND #tb1.CUS = #T1.CUS              /***        退货, 同理CN单也在此处   ***/          --print 88      insert #T1        select  SR.CUSNUM,         JD.JOUTYPE,          SR.SRNUM,          SR.DATE,              AMT=-JD.CR,  -- 负数，退钱        isnull(SR.XREF,‘‘),             OSAMT=-JD.CR,  -- 负数，退钱         ORIAMT=JD.ORIAMT,   -- 同样，这里会负数      ORIOSAMT=JD.ORIAMT, -- 同样，这里会负数        CURR=SR.CURR          from  SR,JOUDTL JD,CUS      where SR.RECACC=JD.ACC and SR.JOUTYPE=JD.JOUTYPE and SR.SRNUM=JD.JOU and         SR.CUSNUM  between @cus1 and @cus2 and  CUS.OFFNUM LIKE @where and          SR.DATE <= @enddate and         (JD.R = ‘false‘  or JD.RR=0) and isnull(SR.XREF,‘‘)<>‘NIL‘ and SR.CUSNUM=CUS.CUS            /***        update OSAMT        ***/        select  SINV,#T1.CUS, AMTPAID=sum(AMTPAID),ORIAMTPAID=sum(ARECDTL.ORIAMT)       --  AMTPAID 應收金額，ORIAMT原幣金額        into  #T3         from AREC, ARECDTL,  #T1        where          ARECDTL.SINV=#T1.JOU and AREC.JOU=ARECDTL.JOU and #T1.CUS=AREC.CUS and        AREC.IDATE <= @begdate     -- 期初,在上一个SP传值过来就已被减了一天  group by         SINV,#T1.CUS            update #T1 set OSAMT=OSAMT-AMTPAID, ORIOSAMT=ORIOSAMT-ORIAMTPAID         from #T3 where        #T3.SINV=#T1.JOU  AND #T3.CUS=#T1.CUS          -- select * from #T1 WHERE JOU=‘NSB4001333‘    -- 这段时间收款分开计算        select  R2.JOU,R.CUS,MISCHRG=sum(R2.DR+R2.CR)    -- 手续费等    into #ta2        from ARECDTL2 R2  with (nolock), AREC R  with (nolock),CUS  with (nolock)    where          R.CUS=CUS.CUS and R.JOU=R2.JOU and R2.ACC<>R.AR and  -- 科目不等于该客户应收科目       R.CUS  between @cus1 and @cus2 and  CUS.OFFNUM LIKE @where and       R.IDATE >@begdate  and  R.IDATE <= @enddate    group by         R2.JOU,R.CUS           select  R2.JOU, R.CUS,DEPOSIT=sum(R2.DR-R2.CR),ODEPOSIT=sum(R2.ORIAMT)        into #tb2        from  ARECDTL2 R2  with (nolock),AREC R  with (nolock),CUS  with (nolock)      where  R.CUS = CUS.CUS and R.JOU=R2.JOU and  R2.ACC=R.AR and    -- 科目等于该客户应收科目，即应收减少，相当于有余钱在我们公司    R.CUS  between @cus1 and @cus2 and  CUS.OFFNUM LIKE @where and         R.IDATE >@begdate  and  R.IDATE <= @enddate    group by R2.JOU,R.CUS          update #T1 set AMT=#T1.AMT-#ta2.MISCHRG    -- AMT收款增加，即等于对冲票据总额    from #ta2       where #T1.JOUTYPE=‘RV‘ and #T1.JOU=#ta2.JOU AND #ta2.CUS = #T1.CUS               update #T1 set OSAMT=#tb2.DEPOSIT, ORIOSAMT=#tb2.ODEPOSIT  -- 这里更新为客户余的钱在我们公司，分别为本位币和原币       from #tb2      where #T1.JOUTYPE=‘RV‘ and #T1.JOU=#tb2.JOU  AND #tb2.CUS = #T1.CUS            select  SINV,#T1.CUS,          AMTPAID=sum(AMTPAID),          ORIAMTPAID=sum(ARECDTL.ORIAMT)             into  #T4         from          AREC,          ARECDTL,          #T1        where          ARECDTL.SINV=#T1.JOU and   AREC.CHKACC IN(‘36198‘,‘DDD‘) and   --如果算收款的是特定科目，则这里应该加设科目，否则一些调帐的收票或其它收票将会当做收款。   AREC.JOU=ARECDTL.JOU and   #T1.CUS=AREC.CUS and          AREC.IDATE>@begdate  and AREC.IDATE <= @enddate     group by          SINV ,#T1.CUS         select CUS, AMTPAID=sum(AMTPAID),ORIAMTPAID=sum(#T4.ORIAMTPAID)             into  #T5       FROM #T4 GROUP BY CUS      --  select ‘a‘,* from #T4        /***        group all entry before sid1 to form one entries and delete        the individual ones        ***/        if @delclose=1        delete #T1 where OSAMT=0              --select * from #T1 where IDATE < @begdate order by IDATE ASC          select  CUS,          AMT=sum(AMT),          OSAMT=sum(OSAMT),          ORIAMT=sum(ORIAMT),          ORIOSAMT=sum(ORIOSAMT)         into #T2 from  #T1      where         IDATE <= @begdate         group by          CUS            --delete #T1 where IDATE < @begdate        insert #result         select  CUS,     ‘*Openning*‘,     convert(varchar(10),convert(smalldatetime,‘1900/01/01‘),121),         convert(varchar(10),convert(smalldatetime,@begdate),121),              AMT,           OSAMT,           ORIAMT,          ORIOSAMT    from #T2        --WHERE ORIOSAMT>0                /***        以上算完了期初，所以收票还要加上这个期间的数    ***/        insert #result         select  CUS,              ‘*Received*‘,          convert(varchar(10),dateadd(day,1,convert(smalldatetime,@begdate)),121),     convert(varchar(10),convert(smalldatetime,@enddate),121),            -AMTPAID,                 -ORIAMTPAID,       -AMTPAID,          -ORIAMTPAID    from #T5            select * from #result     drop table #ta1,#tb1, #ta2,#tb2,#T1,#T2,#T3,#T4,#T5,#result