SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO



ALTER    PROCEDURE [dbo].[cw_zz_GetPtypeBalance]
    (
      @PTypeID VARCHAR(50) ,
      @BTypeID VARCHAR(50) ,
      @ETypeID VARCHAR(50) ,
      @StartDate VARCHAR(10) ,
      @EndDate VARCHAR(10) ,
      @VchType INT ,
      @OperatorID VARCHAR(50),
      @BuyBTypeID VARCHAR(50)   --交易单位 记录销售类单据的购买单位

    )
AS
    SET NOCOUNT ON

    DECLARE @SQL VARCHAR(8000)  --动态SQL字符串
    DECLARE @SQL1 VARCHAR(8000) --动态SQL字符串
    DECLARE @SQL2 VARCHAR(8000) --动态SQL字符串
    DECLARE @BTypeCon VARCHAR(1000) ,@BuyBTypeCon VARCHAR(1000) --BType条件字符串
    DECLARE @ETypeCon VARCHAR(100)       --EType条件字符串
    DECLARE @PTypeCon VARCHAR(100)       --PType条件字符串
    DECLARE @VchCon VARCHAR(100)    --VchType条件字符串
    DECLARE @StartDateCon VARCHAR(100)  --开始日期条件字符串
    DECLARE @EndDateCon VARCHAR(100)    --结束日期条件字符串
    DECLARE @AllCon VARCHAR(1000)   --所有条件字符串
    DECLARE @AllCon2 VARCHAR(1000)
    DECLARE @YHAType VARCHAR(100)       --优惠的科目ID
    DECLARE @viewSql VARCHAR(8000)
    DECLARE @viewSql2 VARCHAR(8000)
    DECLARE @RPArype VARCHAR(3000)     --处理期初应收应付
    DECLARE @IniArAp VARCHAR(8000)     --处理期初应收应付
    DECLARE @sz_GatheringDetail VARCHAR(8000),@sz_GatheringDetail_1 VARCHAR(8000) --换掉临时表

    -- -2 代替期初应收的虚拟单据编号
    -- -1 代替期初应付的虚拟单据编号
    DECLARE @szMinYearName VARCHAR(50)
    DECLARE @BTableStr VARCHAR(500)
    DECLARE @TableStr VARCHAR(500)
    DECLARE @CondRetail VARCHAR(400)

    DECLARE @B1TypeTableStr VARCHAR(200),@B1TypeColumStr VARCHAR(200)  --判断BS连接表
    DECLARE @B2TypeTableStr VARCHAR(200),@B2TypeColumStr VARCHAR(200)  --判断BS连接表
    DECLARE @B3TypeTableStr VARCHAR(200),@B3TypeColumStr VARCHAR(200)  --判断BS连接表

    EXEC p_hh_GetRightINNERStr 'B', 'ds', @OperatorID, 'S', @BTableStr OUT
    EXEC p_hh_GetRightINNERStr 'B', 'b', @OperatorID, 'L', @TableStr OUT
    SELECT  @YHAType = '000040000390000'
    SELECT  @BTypeID = LTRIM(RTRIM(@BTypeID))
    SELECT  @StartDate = LTRIM(RTRIM(@StartDate))
    SELECT  @EndDate = LTRIM(RTRIM(@EndDate))
    SELECT  @BuyBTypeID = LTRIM(RTRIM(@BuyBTypeID))

    SELECT  @VchCon = ''
    SELECT  @StartDateCon = ''
    SELECT  @EndDateCon = ''
    SET @szMinYearName = ''
    SELECT @CondRetail = ' ((ds.BillBalanceMode in (-1,2)) or (ds.VchType IN (11, 45) AND ds.lsRetail NOT IN (0, 10) and ds.BillBalanceMode = 0))'
    SELECT TOP 1
            @szMinYearName = YearName
    FROM    YearInfo
    ORDER BY YearPeriod

    EXEC p_hh_GetBaseTypeStr 'b', 'b1', ' x.btypeid',
        ',b1.bfullname,b1.busercode', @B1TypeTableStr OUT,
        @B1TypeColumStr OUT;
    EXEC p_hh_GetBaseTypeStr 'b', 'b2', ' f.btypeid',
        ',b2.bfullname,b2.busercode', @B2TypeTableStr OUT,
        @B2TypeColumStr OUT;

   SELECT @B3TypeTableStr = ' LEFT JOIN dbo.Btype b3 on  x.buyBtypeid = b3.Btypeid ',
           @B3TypeColumStr = ' ,b3.bfullname buybfullname,b3.busercode buybusercode '

    SELECT  @BuyBTypeCon = dbo.fn_getsonstr(@BuyBTypeID, 'GB', 'ds')

    SELECT  @BTypeCon = dbo.fn_getsonstr(@BTypeID, 'B', 'ds')

    SELECT  @ETypeCon = dbo.fn_getsonstr(@ETypeID, 'E', '')

    SELECT  @PTypeCon = dbo.fn_getsonstr(@PTypeID, 'P', 'a')

    IF @BTypeID = '00000'
        SET @BTypeID = ''
    IF @BuyBTypeID = '00000'
    SET @BuyBTypeID = ''
    /**************************新增单据8.14********************/
    SELECT  @VchCon = CASE @VchType
                        WHEN 4
                        THEN ' and VchType IN (11,161,189,45,89,26,142,115,116,85,93,180,185,188) ' -- 收款单
                        WHEN 66
                        THEN ' and VchType IN (6,34,73,28,143,128,129,84,36,146,181,186,187,192,197) '  -- 付款单
                        WHEN 0 THEN ''                      -- 所有单据
                        ELSE 'VchType Wrong'
                      END
    IF @VchCon = 'VchType Wrong'
        RETURN -1

    IF @StartDate <> ''
        SELECT  @StartDateCon = 'Date >= ' + CHAR(39) + @StartDate + CHAR(39)
    IF @EndDate <> ''
        SELECT  @EndDateCon = 'Date <= ' + CHAR(39) + @EndDate + CHAR(39)

    SELECT  @AllCon = @BTypeCon + @ETypeCon
    SELECT @AllCon2 = dbo.fn_getsonstr(@BTypeID, 'B', 'd') + dbo.fn_getsonstr(@ETypeID, 'E', 'd')
    IF @StartDateCon <> ''
    BEGIN
        SELECT  @AllCon = @AllCon + ' AND ' + @StartDateCon
        SELECT  @AllCon2 = @AllCon2 + ' AND d.Date >= ' + CHAR(39) + @StartDate + CHAR(39)
    END
    IF @EndDateCon <> ''
    BEGIN
        SELECT  @AllCon = @AllCon + ' AND ' + @EndDateCon
        SELECT  @AllCon2 = @AllCon2 + ' AND d.Date <= ' + CHAR(39) + @EndDate + CHAR(39)
    END

    DECLARE @IsUsingFc INT
    SELECT  @IsUsingFc = ISNULL([STATS],0) FROM dbo.syscon WHERE [ORDER]=125

    --处理期初应收应付 --期初结算单位等于交易单位
    SELECT  @RPArype = CASE @VchType
                         WHEN 4
                         THEN '
                select b.btypeid,'''' pusercode,''期初应收'' pfullname,'' '' summary,1 VchType,0 BillType, '' '' etypeid,-2 Vchcode, '' '' Date, '''
                              + @szMinYearName
                              + '期初应收'' Number,ARTotal00Year Total,0 lsFee,ARTotal00Year fcTotal,''00001'' fctypeid,1 fcrate
                from  dbo.fn_Btypeall(''L'') b ' + @TableStr + '
                WHERE  b.Btypeid like ''' + @BTypeID + '%''  and  b.gatherBtypeid like ''' + @BuyBTypeID + '%'''
                              + ' and deleted = 0 and ARTotal00Year <> 0'
                -- 收款单
                         WHEN 66
                         THEN '
                select b.btypeid,'''' pusercode,''期初应付'' pfullname,'' '' summary,2 VchType,0 BillType, '' '' etypeid,-1 Vchcode, '' '' Date, '''
                              + @szMinYearName
                              + '期初应付'' Number,APTotal00Year Total,0 lsFee,APTotal00Year fcTotal,''00001'' fctypeid,1 fcrate
                from dbo.fn_Btypeall(''L'') b ' + @TableStr + '
                WHERE   b.Btypeid like ''' + @BTypeID + '%'' and  b.gatherBtypeid like ''' + @BuyBTypeID + '%'''
                              + ' and deleted = 0 and APTotal00Year <> 0'
            -- 付款单
                         WHEN 0
                         THEN '
                select b.btypeid,'''' pusercode,''期初应收'' pfullname,'' '' summary,1 VchType,0 BillType, '' '' etypeid, -2 Vchcode, '' '' Date, '''
                              + @szMinYearName
                              + '期初应收'' Number,ARTotal00Year Total,0 lsFee ,ARTotal00Year fcTotal,''00001'' fctypeid,1 fcrate
                from dbo.fn_Btypeall(''L'') b ' + @TableStr + '
                WHERE  b.BTYPEID like ''' + @BTypeID + '%'' and  b.gatherBtypeid like ''' + @BuyBTypeID + '%'''
                              + ' and deleted = 0 and ARTotal00Year<>0
                union all
                select b.btypeid,'''' pusercode,''期初应付'' pfullname,'' '' summary, 2 VchType,0 BillType, '' '' etypeid, -1 Vchcode,'' '' Date, '''
                              + @szMinYearName
                              + '期初应付'' Number,APTotal00Year Total,0 lsFee ,APTotal00Year fcTotal,''00001'' fctypeid,1 fcrate
                from dbo.fn_Btypeall(''L'') b ' + @TableStr + '
                WHERE   b.Btypeid like ''' + @BTypeID + '%'' and  b.gatherBtypeid like ''' + @BuyBTypeID + '%'''
                              + ' and deleted = 0 and APTotal00Year<>0'
                       END

    SELECT  @IniArAp = '
        SELECT f.*,
            CASE WHEN BalanceTotal = 0 THEN 1  WHEN  BalanceTotal = VchTotal THEN 0 ElSE 2 END IsFinished,
            ISNULL(k.SubCount,0) AS SubCount,0 AS IgnoreTotal ,0 AS fcIgnoreTotal '+@B2TypeColumStr+','''' buybfullname ,'''' buybusercode
        from
        (SELECT case when e.vchcode = -1 then ''期初应付'' else ''期初应收'' end ptypeid,e.pusercode,e.pfullname,e.btypeid,'' '' buybtypeid,e.vchname,dbo.fn_GetBtypeName(e.btypeid) + e.Number as summary,e.VchType,e.BillType,
                e.Vchcode,e.Date,gd.GatheringDate,e.Number,e.fctypeid,e.fcrate,
                e.Total fcvchtotal,
                (CASE WHEN (e.VchType=2) THEN -1.0 ELSE 1.0 END * SUM(ISNULL(gd.GatheringTotal, 0))) AS fcGatheringTotal ,
                e.Total - (CASE WHEN (e.VchType=2) THEN -1.0 ELSE 1.0 END*SUM(ISNULL(gd.GatheringTotal, 0))) AS fcBalanceTotal,
                e.Total VchTotal,
                (CASE WHEN (e.VchType=2) THEN -1.0 ELSE 1.0 END * SUM(ISNULL(gd.GatheringTotal, 0)) ) AS GatheringTotal,0 gatheringqty,
                e.Total - (CASE WHEN (e.VchType=2) THEN -1.0 ELSE 1.0 END*SUM(ISNULL(gd.GatheringTotal, 0))) AS BalanceTotal,0 balanceqty,
                e.etypeid,e.lsFee,-1 feeFlag,-1 feetype,0 vchqty,0 DlyOrder,'''' unit,0 isoverpay,'''' kwtypeid
            FROM
            (select t.btypeid,t.pusercode,t.pfullname,t.summary,t.VchType,t.BillType,t.etypeid,
            t.Vchcode,t.Date,t.Number,t.Total,ISNULL(v.vName, '''') VchName,t.lsFee,t.fcTotal,t.fctypeid,t.fcrate
            from (' + @RPArype + ') t
            LEFT JOIN
            VchType v ON t.VchType=v.VchType
            ) e
            LEFT OUTER JOIN
            (select ds.*
            from  dbo.view_zz_GatheringPtypeDetail ds ' + @BTableStr
            + '
            where vchcode in (-2,-1)
            ) gd ON e.btypeid=gd.btypeid
            GROUP BY e.btypeid,e.summary,e.VchType,e.BillType,gd.GatheringDate,
            e.Vchcode,e.Date,e.Number,e.Total,e.etypeid,e.VchName,e.lsFee,e.pusercode,e.pfullname,e.fcTotal,e.fctypeid,e.fcrate
        ) f
        left join
        (SELECT Count(1) SubCount, ds.btypeid
            FROM  dbo.view_zz_GatheringPtypeDetail ds ' + @BTableStr + '
            where vchcode in (-2,-1) and ds.btypeid like '''
            + @BTypeID + '%''' + '
            group by ds.btypeid
        ) k on f.btypeid=k.btypeid ' +@B2TypeTableStr

    --处理期初应收应付end
    SELECT  @viewSql = '
SELECT  d.ptypeid,d.pusercode,d.pfullname,d.btypeid,d.buybtypeid,d.VchName,d.summary,d.VchType,d.BillType,
    d.Vchcode,d.Date,d.GatheringDate, d.Number,
    d.fctypeid,d.fcrate,
    CASE WHEN d.VchType IN (45,6,115,129,186,188,189) THEN -1.0*d.fcTotal ELSE d.fcTotal END AS fcVchTotal,
    CASE WHEN (d.VchType IN (34,6,28,73,128,129,143,84,36,146,181,186,187,192,197)) or (feeFlag = 0  or feeFlag = 2) THEN - 1.0 ELSE 1.0 END * SUM(ISNULL(g.fcGatheringTotal, 0)) AS fcGatheringTotal,
    CASE WHEN d.VchType IN (45, 6,115,129,186,188,189) THEN -1.0 * d.fcTotal ELSE d.fcTotal END - CASE WHEN (d.VchType IN (34,6,28,73,128,129,143,84,36,146,181,186,187,192,197)) or (feeFlag = 0  or feeFlag = 2) THEN - 1.0 ELSE 1.0 END * SUM(ISNULL(g.fcGatheringTotal, 0)) AS fcBalanceTotal,
    CASE WHEN d.VchType IN (45,6,115,129,186,188,189) THEN -1.0*d.Total ELSE d.Total END AS VchTotal,
    CASE WHEN (d.VchType IN (34,6,28,73,128,129,143,84,36,146,181,186,187,192,197)) or (feeFlag = 0  or feeFlag = 2) THEN - 1.0 ELSE 1.0 END * SUM(ISNULL(g.GatheringTotal, 0)) AS GatheringTotal,
    CASE WHEN  (d.VchType IN (34,6,28,73,128,129,143,84,36,146,181,186,187,192,197)) or (feeFlag = 0  or feeFlag = 2) THEN - 1.0 ELSE 1.0 END * SUM(ISNULL(g.gatheringqty,0)) AS gatheringqty,
    CASE WHEN d.VchType IN (45, 6,115,129,186,188,189) THEN -1.0 * d.Total ELSE d.Total END - CASE WHEN (d.VchType IN (34,6,28,73,128,129,143,84,36,146,181,186,187,192,197)) or (feeFlag = 0  or feeFlag = 2) THEN - 1.0 ELSE 1.0 END * SUM(ISNULL(g.GatheringTotal, 0)) AS BalanceTotal,
    d.etypeid,d.lsFee,feeFlag,feetype,CASE WHEN d.VchType IN (45, 6,115,129,186,188,189) THEN -1.0*d.qty ELSE d.qty END - CASE WHEN  (d.VchType IN (34,6,28,73,128,129,143,84,36,146,181,186,187,192,197)) or (feeFlag = 0  or feeFlag = 2) THEN - 1.0 ELSE 1.0 END * SUM(ISNULL(g.gatheringqty,0)) AS balanceqty,
    CASE WHEN d.VchType IN (6,45,115,129,186,188,189) THEN -1.0*d.qty ELSE d.qty END AS vchqty,d.DlyOrder,d.unit,d.isoverpay,d.kwtypeid,ndx.lsRetail
FROM #Tmp'

SET @sz_GatheringDetail = 'select t.ptypeid,t.pusercode,t.pfullname,t.btypeid,t.buybtypeid, t.summary, t.VchType, t.BillType,t.etypeid,
    t.Vchcode,t.Date,t.GatheringDate,t.Number,t.Total,ISNULL(v.vName, '''') VchName,t.lsFee ,t.feeFlag, feetype,qty,DlyOrder,unit,isoverpay,kwtypeid,t.fctypeid,t.fcrate,t.fctotal INTO #Tmp
    from (select ptypeid,pusercode,pfullname,btypeid,buybtypeid,summary,ds.VchType,BillType,etypeid,ds.Vchcode,Date,GatheringDate,Number,Total,lsFee ,-1 feeFlag,-1 feetype,qty,DlyOrder,unit, isoverpay,kwtypeid,fctypeid,fcrate,fctotal from
    (select ds.ptypeid,ds.pusercode,ds.pfullname,ds.btypeid,ds.buybtypeid,summary,ds.VchType,BillType,etypeid,ds.Vchcode,Date,GatheringDate,Number,ds.Total total,0 lsFee ,-1 feeFlag,qty,DlyOrder,unit,isoverpay,kwtypeid,ds.fctypeid,ds.fcrate,
     case when isnull(ds.fctypeid,'''') = ''00001'' then ds.total else ds.fctotal end fctotal
    from (select buy.PtypeId,p.pusercode,p.pfullname,ds.Vchcode,ds.bTypeId,ds.bTypeId buybtypeid,
    ds.summary,ds.VchType,ds.BillType,ds.etypeid,ds.Date,ds.GatheringDate,ds.Number,
    CASE WHEN ds.VchType = 143 AND buy.tax_total < 0 THEN buy.tax_total else abs(buy.tax_total) end total,
    CASE WHEN ds.VchType = 143 AND buy.tax_total < 0 THEN -buy.AssQty ELSE abs(buy.AssQty) end qty,buy.DlyOrder,buy.unit, 0 isoverpay,buy.kwtypeid
    ,ds.fctypeid,ds.fcrate,CASE WHEN ds.VchType = 143 AND buy.tax_total < 0 THEN sign(buy.tax_total)*(fc.tax_total) else abs(fc.tax_total) end fctotal
    from Dlyndx ds,dbo.DlyBuy buy LEFT JOIN Fcdly fc ON buy.Vchcode = fc.vchcode AND buy.Vchtype = fc.vchtype AND buy.DlyOrder = fc.dlyorder AND buy.PtypeId = fc.ptypeid
    LEFT JOIN ptype p ON buy.PtypeId = p.ptypeid WHERE ds.Vchcode = buy.Vchcode AND (draft=2) AND (RedOld=''F'') AND ds.VchType IN(6,28,34,73,143) and ds.BillBalanceMode in (-1,2) and buy.pstutas = 0
    and ds.VchType IN(11,161,189,45,89,26,6,34,73,28,142,115,116,143,128,129,85,93,84,36,180,181,185,186,187,188,192,197)
    UNION ALL
    select buy.PtypeId,p.pusercode,p.pfullname,ds.Vchcode,CASE WHEN ds.vchtype IN (11,45,142) THEN ds.GatherBtypeId else ds.bTypeId end btypeid,ds.bTypeId buybtypeid,
    ds.summary,ds.VchType,ds.BillType,ds.etypeid,ds.Date,ds.GatheringDate,ds.Number,
    CASE WHEN buy.vchtype = 142 AND  buy.tax_total > 0 THEN -buy.tax_total ELSE ABS(buy.tax_total) END total,
    CASE WHEN buy.vchtype = 142 AND  buy.tax_total > 0 THEN -buy.AssQty ELSE ABS(buy.AssQty) END   qty,buy.DlyOrder,buy.unit, 0 isoverpay,buy.kwtypeid
    ,ds.fctypeid,ds.fcrate,CASE WHEN buy.vchtype = 142 AND  buy.tax_total > 0 THEN -sign(buy.tax_total)*(fc.tax_total) ELSE ABS(fc.tax_total) END fctotal
    from Dlyndx ds,dbo.DlySale buy LEFT JOIN Fcdly fc ON buy.Vchcode = fc.vchcode AND buy.Vchtype = fc.vchtype AND buy.DlyOrder = fc.dlyorder AND buy.PtypeId = fc.ptypeid
    LEFT JOIN ptype p ON buy.PtypeId = p.ptypeid WHERE ds.Vchcode = buy.Vchcode AND (draft=2) AND (RedOld=''F'') AND ds.VchType IN(11,26,45,89,142) and '+ @CondRetail +' and buy.pstutas <> 1
    and ds.VchType IN(11,161,189,45,89,26,6,34,73,28,142,115,116,143,128,129,85,93,84,36,180,181,185,186,187,188,192,197)
    UNION ALL
    select buy.IStypeid PtypeId,i.pusercode,i.pfullname,ds.Vchcode, ds.GatherBtypeId btypeid,ds.bTypeId buybtypeid,
    ds.summary,ds.VchType,ds.BillType,ds.etypeid,ds.Date,ds.GatheringDate,ds.Number, ABS(buy.tax_total) total, ABS(buy.AssQty) qty,buy.DlyOrder,buy.unit, 0 isoverpay,buy.kwtypeid
    ,ds.fctypeid,ds.fcrate,ABS(buy.tax_total) fctotal  --组合套件不支持外币，所以暂时取本币代替
    from Dlyndx ds,dbo.IsDlySale buy LEFT JOIN ptype i ON buy.IStypeid = i.ptypeid WHERE ds.Vchcode = buy.Vchcode AND (draft=2) AND (RedOld=''F'') AND ds.VchType IN(161,189) and ds.BillBalanceMode in (-1,2) and buy.pstutas = 0
    and ds.VchType IN(11,161,189,45,89,26,6,34,73,28,142,115,116,143,128,129,85,93,84,36,180,181,185,186,187,188,192,197)
    UNION ALL
    select '''' PtypeId,'''' pusercode,'''' pfullname,ds.Vchcode,ds.bTypeId btypeid,ds.bTypeId buybtypeid,
    ds.summary,ds.VchType,ds.BillType,ds.etypeid,ds.Date,ds.GatheringDate,ds.Number, total, 0 qty,0 DlyOrder,'''' unit, 0 isoverpay,'''' kwtypeid
    ,ds.fctypeid,ds.fcrate, ds.fctotal
    from Dlyndx ds WHERE (draft=2) AND (RedOld=''F'') AND ds.VchType IN(115,116,128,129,85,93,84,36,180,181,197) and ds.BillBalanceMode in (-1,2)
    and ds.VchType IN(11,161,189,45,89,26,6,34,73,28,142,115,116,143,128,129,85,93,84,36,180,181,185,186,187,188,192,197)
    UNION ALL
    select buy.PtypeId,p.pusercode,p.pfullname,ds.Vchcode, ds.GatherBtypeId btypeid,ds.bTypeId buybtypeid,
    ds.summary,ds.VchType,ds.BillType,ds.etypeid,ds.Date,ds.GatheringDate,ds.Number, ABS(buy.tax_total) total, ABS(buy.AssQty) qty,buy.DlyOrder,buy.unit, 0 isoverpay,buy.kwtypeid
    ,ds.fctypeid,ds.fcrate,ABS(buy.tax_total) fctotal --委外不支持外币，所以暂时取本币代替
    from Dlyndx ds,dbo.dlyOther buy LEFT JOIN ptype p ON buy.PtypeId = p.ptypeid WHERE ds.Vchcode = buy.Vchcode AND (draft=2) AND (RedOld=''F'') AND ds.VchType IN(146) and ds.BillBalanceMode in (-1,2) and buy.pstutas = 0
    and ds.VchType IN(11,161,189,45,89,26,6,34,73,28,142,115,116,143,128,129,85,93,84,36,180,181,185,186,187,188,192,197)
    UNION ALL
    select buy.PtypeId,p.pusercode,p.pfullname,ds.Vchcode, ds.GatherBtypeId btypeid,ds.bTypeId buybtypeid,
    ds.summary,ds.VchType,ds.BillType,ds.etypeid,ds.Date,ds.GatheringDate,ds.Number, ABS(buy.tax_total) total, ABS(buy.AssQty) qty,buy.DlyOrder,buy.unit, 0 isoverpay,buy.kwtypeid
    ,ds.fctypeid,ds.fcrate,ABS(buy.tax_total) fctotal --不支持外币，所以暂时取本币代替
    from Dlyndx ds,dbo.DlyRation buy LEFT JOIN ptype p ON buy.PtypeId = p.ptypeid WHERE ds.Vchcode = buy.Vchcode AND (draft=2) AND (RedOld=''F'') AND ds.VchType IN(185,186,187,188) and ds.BillBalanceMode in (-1,2) and buy.pstutas = 0
    and ds.VchType IN(11,161,189,45,89,26,6,34,73,28,142,115,116,143,128,129,85,93,84,36,180,181,185,186,187,188,192,197)'
  SET @sz_GatheringDetail_1 = 'UNION ALL
    select buy.PtypeId,p.pusercode,p.pfullname,ds.Vchcode, ds.bTypeId btypeid,ds.bTypeId buybtypeid,
    ds.summary,ds.VchType,ds.BillType,ds.etypeid,ds.Date,ds.GatheringDate,ds.Number, ABS(SaleTotal-Profit) total, ABS(buy.SaleQty) qty,buy.DlyOrder,0 unit, 0 isoverpay,'''' kwtypeid
    ,ds.fctypeid,ds.fcrate,ABS(SaleTotal-Profit) fctotal --联营账款不支持外币，所以暂时取本币代替
    from Dlyndx ds,dbo.Union_Detail buy LEFT JOIN ptype p ON buy.PtypeId = p.ptypeid WHERE ds.Vchcode = buy.Vchcode AND (draft=2) AND (RedOld=''F'') AND ds.VchType = 192 and ds.BillBalanceMode in (-1,2)
    and ds.VchType IN(11,161,189,45,89,26,6,34,73,28,142,115,116,143,128,129,85,93,84,36,180,181,185,186,187,188,192,197)
    ) ds  ' + @BTableStr
    + ' WHERE   ds.VchType IN(11,161,189,45,89,26,6,34,73,28,142,115,116,143,128,129,85,93,84,36,180,181,185,186,187,188,192,197) '
    + CASE WHEN @VchCon <> ''
           THEN ' and ' + RIGHT(@VchCon, LEN(@VchCon) - 4)
           ELSE ''
      END + ' ) ds where 1=1'
    + @AllCon + @BuyBTypeCon + ' union all
                select '''' ptypeid,'''' pusercode,''进货运费'' pfullname,btypeid,'' '' buybtypeid,''运费，进货单''+number summary,VchType,BillType,etypeid,Vchcode,Date,GatheringDate,Number,Total,1 lsFee ,0 feeFlag,-1 feetype,0 qty,0 DlyOrder,'''' unit, 0 isoverpay,'''' kwtypeid,fctypeid,fcrate,fctotal
                from
                (select x.feebtypeid btypeid,x.vchtype,x.billtype,x.etypeid,x.vchcode,x.date,x.GatheringDate,x.number,x.draft,x.redold,sum(ds.FeeTotal) total,x.fctypeid,x.fcrate,sum(fc.FeeTotal) fctotal
                    from dbo.fn_GetBtypeTypeid(''' + @OperatorID
                    + ''')  TBRight, dlyndx x,dlybuy ds LEFT JOIN Fcdly fc ON ds.Vchcode = fc.vchcode AND ds.Vchtype = fc.vchtype AND ds.DlyOrder = fc.dlyorder AND ds.PtypeId = fc.ptypeid
                            where TBRight.BTypeid=x.feebtypeid and ds.vchcode=x.vchcode and x.VchType = 34 and x.redold = ''F'' and x.feebtypeid <> '''' and x.BillBalanceMode in (-1,2)

                            group by x.feebtypeid,x.vchtype,x.billtype,x.etypeid,x.vchcode,x.date,x.GatheringDate,x.number,x.draft,x.redold,x.fctypeid,x.fcrate
                            having sum(ds.FeeTotal)<>0
                        ) ds
                        WHERE  VchType = 34 '
                        +
                        '
                        union all
                        select '''' ptypeid,'''' pusercode,''销售运费'' pfullname,btypeid,'' '' buybtypeid,''销售运费，销售单''+number summary,VchType,BillType,etypeid,Vchcode,Date,GatheringDate,Number,
                 Total,1 lsFee ,feeFlag,feetype,0 qty,0 DlyOrder,'''' unit, 0 isoverpay,'''' kwtypeid,fctypeid,fcrate,fctotal
                from
                (select FeeBtypeID  btypeid,
                CASE WHEN ISNULL(feetype,-1) = 0 THEN 0 ELSE  2  end  feeFlag, -- 0 自付，2 代付结算单位，1  代付运费单位
                x.vchtype,x.billtype,x.etypeid,x.vchcode,x.date,x.GatheringDate,x.number,x.draft,x.redold,feetype,
                x.FeeTotal total,x.fctypeid,x.fcrate,x.fcFeeTotal fctotal
                    from dbo.fn_GetBtypeTypeid(''' + @OperatorID
            + ''')  TBRight, dlyndx x  LEFT join DlyndxExte Exte ON x.vchcode = Exte.vchcode
                    where TBRight.BTypeid=x.feebtypeid and x.VchType = 11 and  FeeTotal <> 0 AND x.redold = ''F'' and x.feebtypeid <> ''''
                    AND ((x.BillBalanceMode in (-1,2)) or (x.VchType IN (11, 45) AND x.lsRetail NOT IN (0, 10) and x.BillBalanceMode = 0))
                    --and x.BillBalanceMode in (-1,2)
                ) ds
                WHERE   VchType = 11 ' + @AllCon + ' and (draft = 2) AND (RedOld = ''F'')
                         union all
                select '''' ptypeid,'''' pusercode,''代付运费'' pfullname,btypeid,'' '' buybtypeid,''代付运费，销售单''+number summary,VchType,BillType,etypeid,Vchcode,Date,GatheringDate,Number,
                 Total,1 lsFee ,feeFlag,feetype,0 qty,0 DlyOrder,'''' unit, 0 isoverpay,'''' kwtypeid,fctypeid,fcrate,fctotal
                from
                (select x.btypeid,
                --case WHEN  (ISNULL(feetype,-1) = 0 AND 66 = '  + CAST(@VchType AS VARCHAR(4)) +  ')  OR (ISNULL(feetype,-1) = 1) then 1 else -1 end fee,
                1  feeFlag, -- 0 自付，2 代付结算单位，2  代付运费单位
                x.vchtype,x.billtype,x.etypeid,x.vchcode,x.date,x.GatheringDate,x.number,x.draft,x.redold,feetype,
                x.FeeTotal total,x.fctypeid,x.fcrate,x.fcFeeTotal fctotal
                    from dbo.fn_GetBtypeTypeid(''' + @OperatorID
            + ''')  TBRight, dbo.f_GetGatherBtypeId_Ndx() x , DlyndxExte Exte
                    where TBRight.BTypeid= x.btypeid AND x.vchcode = Exte.vchcode AND x.Vchtype = 11
                    and  FeeTotal <> 0 AND x.redold = ''F'' and x.feebtypeid <> '''' AND exte.feetype = 1
                    AND ((x.BillBalanceMode in (-1,2)) or (x.VchType IN (11, 45) AND x.lsRetail NOT IN (0, 10) and x.BillBalanceMode = 0))
                    --and x.BillBalanceMode in (-1,2)
                ) ds
                WHERE   VchType = 11 '
                 + @AllCon + ' and (draft = 2) AND (RedOld = ''F'')
                         union all
                        select '''' ptypeid,'''' pusercode,''加工费'' pfullname,btypeid,'' '' buybtypeid, CASE WHEN VchType = 146  THEN ''加工费，委外加工完工单'' ELSE ''加工费，委外完工退货单'' END + number summary,VchType,BillType,etypeid,Vchcode,Date,GatheringDate,
                            Number,Total,1 lsFee ,-1 feeFlag,-1 feetype,0 qty,0 DlyOrder,'''' unit, 0 isoverpay,'''' kwtypeid,''00001'' as fctypeid,1 fcrate,Total fctotal --不支持外币，所以暂时取本币代替
                        from
                        (select x.btypeid,x.vchtype,x.billtype,x.etypeid,x.vchcode,x.date,x.GatheringDate,x.number,x.draft,x.redold,CASE WHEN x.VchType = 146 then 1 else -1 end * sum(ds.prointaxtotal+ds.OtherTotal) total,x.fctypeid,x.fcrate,0 fctotal
                            from dbo.fn_GetBtypeTypeid(''' + @OperatorID
                    + ''')  TBRight, dlyndx x,dlyother ds
                            where TBRight.BTypeid = x.BTypeid and ds.vchcode = x.vchcode and x.redold = ''F'' and x.BillBalanceMode in (-1,2)
                            group by x.btypeid, x.vchtype,x.billtype,x.etypeid,x.vchcode,x.date,x.GatheringDate,x.number,x.draft,x.redold,x.fctypeid,x.fcrate
                            having sum(ds.prointaxtotal+ds.OtherTotal) <> 0
                ) ds
                WHERE  VchType in (146,191) ' + @AllCon
            + CASE WHEN @VchCon <> ''
                   THEN ' and ' + RIGHT(@VchCon, LEN(@VchCon) - 4)
                   ELSE ''
              END
            + ' and (draft = 2) AND (RedOld = ''F'')

            ) t LEFT JOIN VchType v ON t.VchType  = v.VchType '
  SELECT @viewSql2 = '
         d
        INNER JOIN dbo.Dlyndx ndx ON ndx.Vchcode = d.Vchcode
        LEFT OUTER JOIN  dbo.view_zz_GatheringPtypeDetail  g
        ON d.Vchcode = g.VchCode AND d.ptypeid = g.ptypeid AND g.DlyOrder = d.DlyOrder and d.lsFee=g.lsFee AND d.feeflag = g.lsfeeflag ' +
        CASE WHEN @VchType = 0  THEN '' ELSE ' AND (((4 = '  + CAST(@VchType AS VARCHAR(4)) +  ') AND (g.TYPE = 1)) OR ((66 = '  + CAST(@VchType AS VARCHAR(4)) +  ') AND (g.TYPE = 2)))  ' END + '
        GROUP BY d.btypeid,d.buybtypeid,d.summary,d.VchType,d.BillType,g.type,
        d.Vchcode,d.Date,d.GatheringDate,d.Number,d.Total,d.etypeid,d.VchName,d.lsFee,d.feeflag, d.feetype,d.ptypeid,d.qty,d.DlyOrder,d.unit,d.isoverpay,d.kwtypeid,d.pusercode,d.pfullname,ndx.lsRetail,d.fctypeid,d.fcrate,d.fctotal'

    SELECT  @SQL = '
    SELECT x.*,ISNULL(d.IgnoreTotal,0) AS IgnoreTotal,ISNULL(d.fcIgnoreTotal,0) AS fcIgnoreTotal  ' +@B1TypeColumStr+ @B3TypeColumStr+' INTO #temp  FROM (SELECT a.ptypeid,a.pusercode,a.pfullname,a.btypeid,a.buybtypeid,a.VchName,a.summary,a.VchType,a.BillType,
    a.Vchcode,a.Date,a.GatheringDate, a.Number,a.fctypeid,a.fcrate,a.fcVchTotal,a.fcGatheringTotal,a.fcBalanceTotal, a.VchTotal,a.GatheringTotal,a.gatheringqty,a.BalanceTotal,a.balanceqty,
    a.etypeid,a.lsFee,a.feeFlag,a.feetype,a.vchqty,a.DlyOrder,a.unit,a.isoverpay,a.kwtypeid,a.IsFinished,
    CASE WHEN a.VchType IN (11, 45) AND a.lsRetail NOT IN (0, 10) THEN 1 ELSE ISNULL(b.SubCount,0) END  AS SubCount
    FROM (SELECT ds.ptypeid,ds.pusercode,ds.pfullname,ds.btypeid,ds.buybtypeid,ds.VchName,ds.summary,ds.VchType,ds.BillType,
    ds.Vchcode,ds.Date,ds.GatheringDate, ds.Number, ds.fctypeid,ds.fcrate,isnull(ds.fcVchTotal,0) fcVchTotal,
    CASE WHEN ds.VchType IN (11, 45) AND ds.lsRetail NOT IN (0, 10) THEN VchTotal ELSE isnull(ds.fcGatheringTotal,0) END AS fcGatheringTotal,
    CASE WHEN ds.VchType IN (11, 45) AND ds.lsRetail NOT IN (0, 10) THEN 0 ELSE isnull(ds.fcBalanceTotal,0) END AS fcBalanceTotal,
    ds.VchTotal,
    CASE WHEN ds.VchType IN (11, 45) AND ds.lsRetail NOT IN (0, 10) THEN VchTotal ELSE ds.GatheringTotal END AS GatheringTotal,
    CASE WHEN ds.VchType IN (11, 45) AND ds.lsRetail NOT IN (0, 10) THEN ds.vchqty ELSE ds.gatheringqty END AS gatheringqty,
    CASE WHEN ds.VchType IN (11, 45) AND ds.lsRetail NOT IN (0, 10) THEN 0 ELSE ds.BalanceTotal END AS BalanceTotal,
    CASE WHEN ds.VchType IN (11, 45) AND ds.lsRetail NOT IN (0, 10) THEN 0 ELSE ds.balanceqty END AS balanceqty,
    ds.etypeid,ds.lsFee,ds.feeFlag,ds.feetype,ds.vchqty,ds.DlyOrder,ds.unit,ds.isoverpay,ds.kwtypeid,ds.lsRetail,
    CASE WHEN ds.VchType IN (11, 45) AND ds.lsRetail NOT IN (0, 10) THEN 1 ELSE
    CASE WHEN ds.lsfee = 1 OR ptypeid = ''优惠'' THEN CASE WHEN BalanceTotal = 0 THEN 1   WHEN BalanceTotal = VchTotal THEN 0 ElSE 2 END ELSE ISNULL(ifn.IsFinished,0) END END AS IsFinished
    FROM ('
    SELECT @SQL2 =  @viewSql2  + ') ds
        LEFT JOIN (
            SELECT gpd.Vchcode,CASE WHEN d.Total = ABS(SUM(gpd.GatheringTotal)) THEN 1
            WHEN ABS(SUM(gpd.GatheringTotal)) < d.Total AND ABS(SUM(gpd.GatheringTotal)) > 0 THEN 2 ELSE 0 END IsFinished
            FROM view_zz_GatheringPtypeDetail gpd LEFT JOIN dbo.Dlyndx d ON d.Vchcode = gpd.vchCode
            WHERE lsFee = 0 AND ptypeid <> ''优惠'' GROUP BY gpd.Vchcode,d.Total
        ) ifn  ON ds.vchcode = ifn.vchcode
                WHERE 1=1' + @AllCon  + CASE WHEN @VchCon <> ''
                   THEN ' and (' + RIGHT(@VchCon, LEN(@VchCon) - 4) + '  OR feetype IN(0,1))'
                   ELSE ''
              END
            + '
            ) a
            Left JOIN
            (SELECT Count(1) SubCount,VchCode,lsFee,DlyOrder,lsfeeflag
            FROM   dbo.view_zz_GatheringPtypeDetail  ds ' + @BTableStr + '
            WHERE  1=1 ' + @BTypeCon + @VchCon + '
            GROUP BY VchCode, lsFee,DlyOrder,lsfeeflag
            ) b ON a.VchCode = b.VchCode and a.lsFee = b.lsFee AND a.feeflag = b.lsfeeflag AND a.dlyOrder = b.DlyOrder
        ) x
        LEFT JOIN
        (SELECT VchCode,Total AS IgnoreTotal,fcTotal AS fcIgnoreTotal,0 lsFee
        FROM  DlyA ds ' + @BTableStr + '
        WHERE  1=1 ' + @AllCon + @VchCon + ' and (ATypeID = ('''
            + @YHAType
            + ''')  AND vchtype not in (10,35,36,115,116,128,129,130,131,4,66,146,197))
        ) d ON d.VchCode = x.VchCode and d.lsFee = x.lsFee ' +@B1TypeTableStr  +@B3TypeTableStr +'
        '
        IF(@ETypeID = '00000') -- 录入经手人不查期初数据
            SELECT @SQL1 = '
                union all ' + @IniArAp
    PRINT(@sz_GatheringDetail)
    PRINT(@sz_GatheringDetail_1)
    print(@SQL)
    PRINT(@viewSql )
    PRINT(@SQL2)
    PRINT(@SQL1)
    print(' SELECT a.*,ISNULL(t.unit1,'''') uname FROM #temp a
            LEFT JOIN (SELECT ptypeid,ucode,unit1 FROM fn_ItemPtypeAll(''00000'',-1)) t ON a.unit = t.ucode
            AND a.ptypeid = t.ptypeid WHERE 1 = 1 ' + @PTypeCon +' ORDER BY VchCode,date,lsFee,dlyorder
            DROP TABLE #temp
            drop table #Tmp')
    EXEC(@sz_GatheringDetail + @sz_GatheringDetail_1 + @SQL + @viewSql + @SQL2 + @SQL1 +
        ' SELECT a.*,ISNULL(t.unit1,'''') uname FROM #temp a LEFT JOIN (SELECT ptypeid,ucode,unit1
            FROM fn_ItemPtypeAll(''00000'',-1)) t ON a.unit = t.ucode AND a.ptypeid = t.ptypeid WHERE 1 = 1 ' +
            @PTypeCon +' ORDER BY VchCode,date,lsFee,dlyorder
            DROP TABLE #temp
            drop table #Tmp')

    RETURN @@ERROR