

--Drop procedure [dbo].[PR_WoolinteBillIndex];
--2012-08-09  Wesley219  创建储存过程 */

CREATE procedure [dbo].[PR_WoolinteBillIndex](@BillNumberID varchar(100)) AS
   Declare @State varchar(50),@Lex2 char;
  Declare @BillType varchar(50),@BillTypeID varchar(50),@KTypeID_ varchar(100),@VchCode varchar(50),@BillDate varchar(50),@PriceTrack varchar(2),@PriceTrackOrPDAPrice varchar(50);
  Declare @Num int,@PTypeID_ varchar(100),@UintNum varchar(50),@UnitQty numeric(12,2),@UintRate numeric(12,2),@CostMode int,@dlyQty numeric(18,4);
  Declare @GNumberPDate varchar(50),@UserProductName varchar(100),@BillComment varchar(100),@DeviceName varchar(100);
  Declare @BillName varchar(150),@UserTypeID varchar(150),@UserFullName varchar(150),@UserRec VARCHAR(100),@POSStoreID VARCHAR(50);
  DECLARE @CheckReportNo varchar(150), @Number varchar(150),@loginname  varchar(150),@BillCode varchar(150),@BillGuID varchar(150);
  
     begin tran
        Select @BillType=BillType,@BillTypeID=BillTypeID,@BillDate=BillDate,@KTypeID_=KTypeID_,@State=State,
        @PriceTrack=PriceTrack,@BillName=BillName,@UserTypeID=UserTypeID,@UserFullName=UserFullName,
        @DeviceName=DeviceName,@Number=BillCode,@loginname=LoginName,@BillCode=BillCode From Woolinte_BillIndex where BillNumberID=@BillNumberID;
	
                if @State=1
         begin
         
         ---读取系统设置，是否取价格跟踪，无价格跟踪的取实开单价。
         Select @PriceTrackOrPDAPrice=TypeValues From woolinte_Sysdata where TypeName='BillPriceTrackOrPDAPrice';
         ----产品名称
         Select @UserProductName=IsNull(TypeValues,'') From woolinte_Sysdata where TypeName='ProductName';
         ----单据备注
         Set @BillComment='业务员:'+ @loginname +';【物联管家】开单生成';
         set @CheckReportNo='';
         -----取制单人的REC
         SELECT @UserTypeID=Rec FROM Employee WHERE TypeID=@UserTypeID;
         Select @GNumberPDate=TypeValues From Woolinte_Sysdata where TypeName='GNumberPDate';
         
          ----取开单零售价多单位
          Update Woolinte_BillIndex_List  Set RetailPrice=PR.RetailPrice
          From PTypePrice PR,PType P where P.Rec=PR.PRec And Woolinte_BillIndex_List.PTypeID_=P.TypeID
          and Woolinte_BillIndex_List.UintNum=PR.Unit And Billnumberid=@BillNumberID;
          
                if(@@ERROR<>0)
            goto Error1
          
           ----=======================================================================
		  Declare @MONTH varchar(20),@DAY varchar(20),@BillFlag varchar(20)
          Set @MONTH=substring(@BillDate,6,2)
          Set @DAY=substring(@BillDate,9,2)
          Set @number='';
          IF LEN(@BillCode)<>0
          BEGIN
          Set @number=@BillCode
          END
          ELSE
          begin
          ----获取单据编号
          exec PR_Woolinte_GetBillCode @BillType,@BillTypeID,'00001',@BillDate,'',@MONTH,@DAY,@number output
          if(@@ERROR<>0)
             goto Error1 
             END
         ----=======================================================================
         
          ---单据GUID  
          SET @BillGuID=NEWID();   
            
            
         ----更新商品的PRec  
---收款单
          if (@BillType='CollectionBill')
		  begin
		  
		     UPDATE Woolinte_BillIndex_List SET PTypeID_=A.Rec FROM AType A 
             WHERE A.typeId=Woolinte_BillIndex_List.PTypeID_ AND Billnumberid=@BillNumberID;
             if(@@ERROR<>0)
             goto Error1
          end
--付款但
if (@BillType='PayBill')
		  begin
		  
		     UPDATE Woolinte_BillIndex_List SET PTypeID_=A.Rec FROM AType A 
             WHERE A.typeId=Woolinte_BillIndex_List.PTypeID_ AND Billnumberid=@BillNumberID;
             if(@@ERROR<>0)
             goto Error1
          end
----现金费用
if (@BillType='CashBill')
		  begin
		  
		     UPDATE Woolinte_BillIndex_List SET PTypeID_=A.Rec FROM AType A 
             WHERE A.typeId=Woolinte_BillIndex_List.PTypeID_ AND Billnumberid=@BillNumberID;
             if(@@ERROR<>0)
             goto Error1
          end
----一般费用
if (@BillType='MoneyBill')
		  begin
		  
		     UPDATE Woolinte_BillIndex_List SET PTypeID_=A.Rec FROM AType A 
             WHERE A.typeId=Woolinte_BillIndex_List.PTypeID_ AND Billnumberid=@BillNumberID;
             if(@@ERROR<>0)
             goto Error1
          end
          
          if (@BillType='Sale' or @BillType='SaleBack'  or @BillType='SaleOrder' or @BillType='Buy' or @BillType='BuyBack' or @BillType='BuyOrder' or @BillType='SameMove')		
          begin
             UPDATE Woolinte_BillIndex_List SET PTypeID_=P.Rec FROM PType P 
             WHERE P.typeId=Woolinte_BillIndex_List.PTypeID_ AND Billnumberid=@BillNumberID;
          
             if(@@ERROR<>0)
             goto Error1
           end
           
          if (@BillType='SameMove')
		  begin
          ----更新仓库的KRec出库仓库
          UPDATE Woolinte_BillIndex SET KTypeID_=S.Rec,KTypeID2_=k.Rec FROM Stock S ,Stock k
          WHERE S.TYpeId=Woolinte_BillIndex.KTypeID_ and k.TYpeId=Woolinte_BillIndex.KTypeID2_ AND Billnumberid=@BillNumberID;
          if(@@ERROR<>0)
            goto Error1   
            
          ----更新仓库的KRec入库仓库
          UPDATE Woolinte_BillIndex_List SET KTypeID_=S.Rec ,KTypeID2_=k.Rec FROM Stock S ,Stock k
          WHERE S.TYpeId=Woolinte_BillIndex_List.KTypeID_ and k.TYpeId=Woolinte_BillIndex_List.KTypeID2_ AND Billnumberid=@BillNumberID;
         
          if(@@ERROR<>0)
            goto Error1    
          end
           if (@BillType='Sale' or @BillType='SaleBack'  or @BillType='SaleOrder' or @BillType='Buy' or @BillType='BuyBack' or @BillType='BuyOrder'
 or @BillType='CollectionBill' or @BillType='PayBill' or @BillType='MoneyBill' or @BillType='CashBill')		
          begin
           ----更新仓库的KRec
          UPDATE Woolinte_BillIndex SET KTypeID_=S.Rec FROM Stock S 
          WHERE S.TYpeId=Woolinte_BillIndex.KTypeID_ AND Billnumberid=@BillNumberID;
          
          UPDATE Woolinte_BillIndex_List SET KTypeID_=S.Rec FROM Stock S 
          WHERE S.TYpeId=Woolinte_BillIndex_List.KTypeID_ AND Billnumberid=@BillNumberID;
          if(@@ERROR<>0)
            goto Error1  
        
          end


           ---如果是提交取价格跟踪后，没有价格跟踪取开单价的 或者非价格跟踪
         if (@PriceTrackOrPDAPrice='是' or @PriceTrack='0')
         begin
           Update  Woolinte_BillIndex_List set Unitprice=PDAPrice,Discount=PDADiscount where billnumberid=@BillNumberID 
         if(@@ERROR<>0)
            goto Error1 
         end 

                  
         if(@BillType='Sale' or @BillType='SaleBack'  or @BillType='SaleOrder')
         begin
            if(@PriceTrack='1')
            begin
           ---销售单据取价格跟踪
           
           Update Woolinte_BillIndex_List  set Unitprice=T.Price*UintRate,Discount=T.DisCount
           From Woolinte_BillIndex A,TrackSalePrice T,BType B,PType P
           Where T.PRec=P.Rec AND T.BRec=B.Rec and A.BillnumberID=Woolinte_BillIndex_List.BillnumberID
           and A.BTypeID_=B.TypeID AND Woolinte_BillIndex_List.PTypeID_=P.TypeID and A.BillnumberID=@BillNumberID

        if(@@ERROR<>0)
            goto Error1 
            end
         end

         if(@BillType='Buy' or @BillType='BuyBack' or @BillType='BuyOrder')
         begin
            if(@PriceTrack='1')
            begin
           ---进货类单据取价格跟踪
           
           Update Woolinte_BillIndex_List set Unitprice=T.Price*UintRate,Discount=T.DisCount
           From Woolinte_BillIndex A,TrackBuyPrice T,BType B,PType P
           Where T.PRec=P.Rec AND T.BRec=B.Rec and A.BillnumberID=Woolinte_BillIndex_List.BillnumberID
           and A.BTypeID_=B.TypeID AND Woolinte_BillIndex_List.PTypeID_=P.TypeID and A.BillnumberID=@BillNumberID
         
            
        if(@@ERROR<>0)
            goto Error1 

            end 
         end 
         
         ---更新计算表体的，参与计算的合计金额
          Update Woolinte_BillIndex_List set BillQty=PDAQty*UintRate,BillPrice=cast(Unitprice/UintRate as decimal(18,4)),
         BillTotal=PDAQty*Unitprice,BillDistPrice=cast(Unitprice/UintRate as decimal(18,4))*Discount where billnumberid=@BillNumberID;
         
         if(@@ERROR<>0)
                   goto Error1

        ---赋值表头的合计数量
         Update Woolinte_BillIndex set DlySumQty=Sum_Qty From Woolinte_BillIndex a ,(Select billnumberid,sum(PDAQty) as Sum_Qty
		 From Woolinte_BillIndex_List where billnumberid=@BillNumberID group by billnumberid ) b where a.billnumberid=b.billnumberid
       
         ---赋值表头的合计金额
         Update Woolinte_BillIndex set DlySumTotal=Sum_Total From Woolinte_BillIndex a ,(Select billnumberid,sum(BillTotal) as Sum_Total
		 From Woolinte_BillIndex_List where billnumberid=@BillNumberID group by billnumberid ) b where a.billnumberid=b.billnumberid
		 
		 if(@@ERROR<>0)
                   goto Error1
         
         
         -------出库商品明细StockUniqueid默认为Null          
         --UPDATE Woolinte_BillIndex_List SET Feld3=NULL,Feld4='1' WHERE BillNumberID=@BillNumberID;
         --if(@@ERROR<>0)
         --          goto Error1
         -------出库商品明细StockUniqueid关联取值，根据商品、仓库、批次关联 
         --UPDATE Woolinte_BillIndex_List  SET Feld3=GS.StockUniqueid,CostPrice=GS.Price,Feld4=GS.ProviderId FROM GoodsStocks GS
         --WHERE BillNumberID=@BillNumberID AND PRec_=GS.PRec AND KRec_=GS.KRec AND GoodsNumber=GS.JobNumber;
         --if(@@ERROR<>0)
         --          goto Error1 
         
          ---批次成本，数据处理，启用批次，并且是出库类单据
         if (@GNumberPDate='是' and ((@BillType='Sale')) )
         begin
           Update Woolinte_BillIndex_List Set CostPrice= price,Woolinte_BillIndex_List.ProduceDate=gs.OutFactoryDate, Woolinte_BillIndex_List.ValidDate=gs.ValidityPeriod FROM  GoodsStocks GS,Woolinte_BillIndex_List bl 
           WHERE GS.PRec=bl.PTypeID_ AND GS.KRec=BL.KTypeID_ AND GS.JobNumber=BL.GoodsNumber
           and BillNumberID=@BillNumberID;
           Select @CheckReportNo= GS.CheckReportNo From GoodsStocks GS,Woolinte_BillIndex_List BL where gs.KRec=bl.KTypeID_ AND gs.PRec=bl.PTypeID_ AND GS.JobNumber=BL.GoodsNumber and BillNumberID=@BillNumberID ;
                if(@@ERROR<>0)
            goto Error1 
         end
                  
            
         if (@BillType='BuyOrder' or @BillType='SaleOrder')
		 BEGIN
		 
		 IF @BillType='BuyOrder'
		 BEGIN
		 set @BillTypeID='301';
		 END	
		 
		 IF @BillType='SaleOrder'
		 BEGIN
		 set @BillTypeID='300';
		 END
		 
		 
		   Insert OrderIndex(BillDate,ReachDate,BillCode,BillType,Comment,Explain,BillEID,BRec,ERec,KRec,TotalMoney,TotalQty,Tax,Period,GspID,CheckEID)
		   Select BillDate,BillDate,@Number,@BillTypeID,A.Comment,@BillComment,EE.REC,B.Rec,E.Rec,A.KTypeID_,A.DlySumTotal,DlySumQty,-10,'1','0',ee.REC
		   From Woolinte_BillIndex A Left Join Department D On D.TypeID=A.DTypeID_,
		   BType B,Employee E,Employee EE ,Woolinte_BillIndex_List L where A.BTypeID_=B.TypeID and A.ETypeID_=E.TypeID AND A.BillnumberID=L.BillnumberID AND A.UserTypeID=EE.TypeID and A.BillnumberID=@BillNumberID;
		   
           if(@@ERROR<>0)
                   goto Error1 
           Select @VchCode=@@IDENTITY;
           
           Insert Into OrderBill(BillID,PRec,Qty,AssQty,Price,AssPrice,Total,Tax,TaxPrice,AssTaxPrice,TaxTotal,Comment,Unit,UnitRate,mxord)
            Select @VchCode,P.Rec,BillQty,PDAQty,BillPrice,PDAPrice,Cast(Sum(PDAQty*PDAPrice) as Decimal(18,2)),
            P.LTax,BillPrice,CASE WHEN UintNum ='1' then BillPrice ELSE PDAPrice end,Cast(Sum(PDAQty*PDAPrice) as Decimal(18,2)),
            AL.Comment,AL.UintNum,AL.UintRate,AL.DetailRowID From Stock S,PType P,Woolinte_BillIndex_List AL
            where S.TypeID=@KTypeID_ and P.Rec=AL.PTypeID_ and AL.Billnumberid=@BillNumberID 
            Group by P.Rec,BillQty,BillPrice,PDAQty,PDAPrice,UnitPrice,AL.UintRate,P.LTax,AL.Comment,AL.UintNum,AL.UintRate,AL.DetailRowID,AL.BillDistPrice
            Order by MIN(DetailRowID) Asc,AL.UintNum desc;
       if(@@ERROR<>0)
                   goto Error1
                   
            UPDATE OrderIndex SET TotalMoney=a.TaxTotal FROM (SELECT ISNULL(SUM(TaxTotal),0) TaxTotal,BillID FROM OrderBill WHERE BillID=@VchCode GROUP BY BillID) a WHERE OrderIndex.BillID=a.BillID;  
		 end    

		 if @BillType='Sale'
		 begin
		 set @BillTypeID='11'
		 
		   Insert Billindex(UniqueBillID,BillDate,BillCode,BillType,Comment,Explain,BRec,ERec,KRec,KRec2,Checke,IFChecked,BillE,
		   TotalMoney,Totalinmoney,TotalQty,BenefitMoney,Tax,Period,RedWord,Draft,OrderID,Department,
		   PosID,Area,MoneyDate,JSYeTotal,JSFlag,QualityStatus,GspID,PriceDiff,BillStatus,PrintTime,
		   NSysFlag,OfficeID,OutStockLaw,SendWay,IsRedWordNew,BillDetailCount,CanDelete,Invoice,Status,Flag,VipCardID)
		   Select @BillGuID,BillDate,@Number,@BillTypeID,A.Comment,@BillComment,B.Rec,E.Rec,S.Rec,'0',EE.Rec,'1',EE.Rec,
		   DlySumTotal,PayMoney,DlySumQty,PreferenceMoney,-10,'1','0','1','0',IsNULL(D.Rec,''),
		   '0','0',BillDate,DlySumTotal-PayMoney-PreferenceMoney,'','0','7','0',
		   '0','0','0','0','z','','0','0','1','','0','0',CASE when A.VipCardID ='-1' THEN '0' ELSE a.VipCardID end
		   From Woolinte_BillIndex A Left Join Department D On D.TypeID=A.DTypeID_,
		   BType B,Stock S,Employee E,Employee EE	   
		   where A.BTypeID_=B.TypeID and A.ETypeID_=E.TypeID and A.KTypeID_=S.Rec 
		   and A.UserTypeID=EE.TypeID and A.BillnumberID=@BillNumberID 
		   group by BillDate,A.Comment,B.Rec,E.Rec,S.Rec,EE.Rec,PayMoney,DlySumQty,PreferenceMoney,a.VipCardID,
		   D.Rec,BillDate,DlySumTotal;
		   
           if(@@ERROR<>0)
                   goto Error1 
           Select @VchCode=@@IDENTITY;
           
           Insert Into SaleBill(BillID,KRec,GRec,PRec,Qty,AssQty,SalePrice,AssSalePrice,DisCount,DiscountPrice,AssDiscountPrice,
            Total,Tax,TaxPrice,AssTaxPrice,TaxTotal,CostPrice,JobNumber,OutFactoryDate,ValidityPeriod,JobCode,GermJobNumber,CheckReportNo,
            Providerid,PTypeSource,Comment,Eligible,Unit,UnitRate,Retailprice,Ord,StockUniqueid,PriceType)
            Select @VchCode,S.Rec,'0',P.Rec,BillQty,PDAQty,BillDistPrice,PDAPrice,Discount,BillDistPrice,Discount*PDAPrice,
            Cast(Sum(BillDistPrice*BillQty) as Decimal(18,2)),P.LTax,BillDistPrice*(1+P.LTax/100),PDAPrice*Discount*(1+P.LTax/100),Cast(Sum(BillDistPrice*BillQty)*(1+P.LTax/100) as Decimal(18,2)),
            AL.CostPrice,GoodsNumber,Case when ProduceDate='' Then Null Else ProduceDate End,Case when ValidDate='' Then Null Else ValidDate End,
            '','',@CheckReportNo,ISNULL(gs.ProviderId,'2'),'0',AL.Comment,'',AL.UintNum,AL.UintRate,AL.RetailPrice,AL.DetailRowID,ISNULL(GS.StockUniqueid,NULL),al.detailType
            From Stock S,PType P,Woolinte_BillIndex_List AL
            LEFT JOIN GoodsStocks GS ON AL.PTypeID_=GS.PRec AND AL.KTypeID_=GS.KRec AND AL.GoodsNumber=GS.JobNumber
            where S.TypeID=@KTypeID_ and P.Rec=AL.PTypeID_ and AL.Billnumberid=@BillNumberID 
            Group by S.Rec,P.Rec,BillQty,BillPrice,PDAQty,UnitPrice,Discount,BillDistPrice,AL.UintRate,P.LTax,PDAPrice,
            GoodsNumber,ProduceDate,ValidDate,AL.Comment,AL.UintNum,AL.UintRate,AL.RetailPrice,AL.CostPrice,AL.DetailRowID,GS.StockUniqueid,PriceTrack,gs.ProviderId,al.detailType
            Order by MIN(DetailRowID) Asc,AL.UintNum desc;
          if(@@ERROR<>0)
                   goto Error1
                   
                     UPDATE Billindex SET TotalMoney=a.TaxTotal FROM (SELECT ISNULL(SUM(TaxTotal),0) TaxTotal,BillID FROM SaleBill WHERE BillID=@VchCode GROUP BY BillID) a WHERE Billindex.BillID=a.BillID;  
		 end
		 if @BillType='Buy'
		 begin
		 
		    Update Woolinte_BillIndex_List Set CostPrice=BillDistPrice where @BillNumberID=@BillNumberID;
		    if(@@ERROR<>0)
                   goto Error1 
                   
		   set @BillTypeID='34'
		   
		   Insert Billindex(UniqueBillID,BillDate,BillCode,BillType,Comment,Explain,BRec,ERec,KRec,KRec2,Checke,IFChecked,BillE,
		   TotalMoney,Totalinmoney,TotalQty,BenefitMoney,Tax,Period,RedWord,Draft,OrderID,Department,
		   PosID,Area,MoneyDate,JSYeTotal,JSFlag,QualityStatus,GspID,PriceDiff,BillStatus,PrintTime,
		   NSysFlag,OfficeID,OutStockLaw,SendWay,IsRedWordNew,BillDetailCount,CanDelete,Invoice,Status,Flag,VipCardID)
		   Select @BillGuID,BillDate,@Number,@BillTypeID,A.Comment,@BillComment,B.Rec,E.Rec,S.Rec,'0',EE.Rec,'1',EE.Rec,
		    DlySumTotal,PayMoney,DlySumQty,PreferenceMoney,-10,'1','0','1','0',IsNULL(D.Rec,''),
		   '0','0',BillDate,DlySumTotal-PayMoney-PreferenceMoney,'','0','7','0',
		   '0','0','0','0','z','','0','0','1','','0','0','0'
		   From Woolinte_BillIndex A Left Join Department D On D.TypeID=A.DTypeID_,
		   BType B,Stock S,Employee E,Employee EE	   
		   where A.BTypeID_=B.TypeID and A.ETypeID_=E.TypeID and A.KTypeID_=S.Rec
		   and A.UserTypeID=EE.TypeID and A.BillnumberID=@BillNumberID
		    group by BillDate,A.Comment,B.Rec,E.Rec,S.Rec,EE.Rec,PayMoney,DlySumQty,PreferenceMoney,
		    D.Rec,BillDate,DlySumTotal;
		   
		   
           if(@@ERROR<>0)
                   goto Error1 
            Select @VchCode=@@IDENTITY;
            
            Insert Into BuyBill(BillID,KRec,GRec,PRec,Qty,AssQty,SalePrice,AssSalePrice,DisCount,DiscountPrice,AssDiscountPrice,
            Total,Tax,TaxPrice,AssTaxPrice,TaxTotal,CostPrice,JobNumber,OutFactoryDate,ValidityPeriod,JobCode,GermJobNumber,CheckReportNo,
            Providerid,PTypeSource,Comment,Eligible,Unit,UnitRate,Retailprice,Ord,PriceType)
            Select @VchCode,S.Rec,'0',P.Rec,BillQty,PDAQty,BillDistPrice,PDAPrice,Discount,BillDistPrice,Discount*PDAPrice,
            Cast(Sum(BillDistPrice*BillQty) as Decimal(18,2)),P.LTax,BillDistPrice*(1+P.LTax/100),PDAPrice*Discount*(1+P.LTax/100),Cast(Sum(BillDistPrice*BillQty)*(1+P.LTax/100) as Decimal(18,2)),
            AL.CostPrice,GoodsNumber,Case when ProduceDate='' Then Null Else ProduceDate End,Case when ValidDate='' Then Null Else ValidDate End,
            '','',@CheckReportNo,'2','0',AL.Comment,'',AL.UintNum,AL.UintRate,AL.RetailPrice,AL.DetailRowID,al.detailType
            From Woolinte_BillIndex_List AL,Stock S,PType P
            where S.TypeID=@KTypeID_ and P.Rec=AL.PTypeID_ and AL.Billnumberid=@BillNumberID 
            Group by S.Rec,P.Rec,BillQty,BillPrice,PDAQty,UnitPrice,Discount,BillDistPrice,AL.UintRate,P.LTax,PDAPrice,
            GoodsNumber,ProduceDate,ValidDate,AL.Comment,AL.UintNum,AL.UintRate,AL.RetailPrice,AL.CostPrice,AL.DetailRowID,PriceTrack,al.detailType
            Order by MIN(DetailRowID) Asc,AL.UintNum desc;
          if(@@ERROR<>0)
                   goto Error1

 UPDATE Billindex SET TotalMoney=a.TaxTotal FROM (SELECT ISNULL(SUM(TaxTotal),0) TaxTotal,BillID FROM BuyBill WHERE BillID=@VchCode GROUP BY BillID) a WHERE Billindex.BillID=a.BillID;   
		 end
if (@BillType='Retail' AND @POSStoreID<>'')
		 begin
		 set @BillTypeID='12'
		 
		   
		   INSERT INTO RetailBillIndex
		   (BillDate,BillCode,BillType,Comment,ERec,KRec,Posid,SaleEtypeid,totalmoney,totalqty,DisTinyMoney,
		    DiscountMoney,Cash,explain,Flags,multiple,Vipid,ifcheck,draft,printtime,LinkStatus,HandOverDate,
		    CurrIntegral,CurrIntegralTotal,SaleChecke,ShopCashID,ShopCashCode,ShopSaleID,ShopPromoID,ShopPromoCode)
		    
		   Select Convert(varchar(20),getdate(),120),@Number,@BillTypeID,GB.Comment,E.Rec,S.Rec,@POSStoreID,Emp.REC,BillSumTotal,BillSumQty,
		   PreferenceMoney,-(BillSumTotal-PreferenceMoney),
		   CASE WHEN PayATypeID_='0000100003' THEN BillSumTotal-PreferenceMoney ELSE 0 End,
		   @BillComment,'0','1',VipCardID,'f','1','0','0',Convert(varchar(20),getdate(),120),
		   CASE WHEN VipCardID='' THEN '0' ELSE BillSumTotal-PreferenceMoney END,
		   CASE WHEN VipCardID='' THEN '0' ELSE BillSumTotal-PreferenceMoney END,
		   '0','','','','',''
		   From GoodsUnicom_BillIndex GB, Stock S,Employee E,Employee Emp
		   where GB.ETypeID_=E.TypeID and GB.KTypeID_=S.TypeID AND GB.UserTypeID=Emp.TypeID
		   and BillnumberID=@BillNumberID;
		   		   		   
           if(@@ERROR<>0)
                   goto Error1 
           Select @VchCode=@@IDENTITY;
           
           INSERT INTO RetailBill
           (BillID,Ord,PRec,Qty,AssQty,RetailPrice,AssRetailPrice,Discount,DiscountPrice,AssDiscountPrice,DiscountTotal,
           total,comment,JobNumber,OutFactoryDate,validityPeriod,JobCode,GermJobNumber,CheckReportNo,
           CostPrice,Unit,UnitRate,KRec,ProviderId,PtypeSource,GRec,DisRec,
           SourceRetailPrice,StockUniqueid,SaleEtypeid,RecipeNum,PriceType,ROutStockLaw)

           SELECT @VchCode,GBL.DetailRowID,P.Rec,BillQty,Unitqty,GBL.RetailPrice,GBL.RetailPrice*GBL.UnitRate,Discount*100,
           BillDstPrice,Discount*UnitPrice,Cast(UnitPrice*Unitqty*Discount as Decimal(18,2)),Cast(UnitPrice*Unitqty as Decimal(18,2)),
           GBL.Comment,GoodsNumber,Case when ProduceDate='' Then Null Else ProduceDate End,Case when ValidDate='' Then Null Else ValidDate End,
           ISNULL(GS.JobCode,''),ISNULL(GS.GermJobNumber,''),ISNULL(GS.CheckReportNo,''),
           GBL.CostPrice,GBL.UnitNum,UnitRate,S.Rec,GBL.Feld4,'0',ISNULL(GS.GRec,'0'),'0000100','0',GBL.Feld3,@UserRec,'1','0','z'
           FROM PType P,Stock S,GoodsUnicom_BillIndex_List GBL
           LEFT JOIN GoodsStocks GS ON GBL.KRec_=GS.KRec AND GBL.PRec_=GS.PRec AND GS.JobNumber=GBL.GoodsNumber AND GoodsOrder=GBL.Feld5
           WHERE GBL.PTypeID_=P.typeId AND GBL.KTypeID_=S.TypeID AND GBL.Billnumberid=@BillNumberID AND GBL.IsPayType='0';

          if(@@ERROR<>0)
                   goto Error1
                   
          -----零售默认现金结算 
          INSERT INTO RetailPayInfo(BillID,ord,ARec,Total)
          SELECT @VchCode,'0',A.Rec,PayMoney
          FROM GoodsUnicom_BillIndex GB,AType A
          WHERE A.typeId=GB.PayATypeID_ AND GB.PayATypeID_<>'' AND GB.BillNumberID=@BillNumberID;    

          if(@@ERROR<>0)
                   goto Error1    
                                        

         
		 end		 

		 if @BillType='SaleBack'
		 begin
		    SET @BillTypeID='45'
		    Insert Billindex(UniqueBillID,BillDate,BillCode,BillType,Comment,Explain,BRec,ERec,KRec,KRec2,Checke,IFChecked,BillE,
		   TotalMoney,Totalinmoney,TotalQty,BenefitMoney,Tax,Period,RedWord,Draft,OrderID,Department,
		   PosID,Area,MoneyDate,JSYeTotal,JSFlag,QualityStatus,GspID,PriceDiff,BillStatus,PrintTime,
		   NSysFlag,OfficeID,OutStockLaw,SendWay,IsRedWordNew,BillDetailCount,CanDelete,Invoice,Status,Flag,VipCardID)
		   Select @BillGuID,BillDate,@Number,@BillTypeID,A.Comment,@BillComment,B.Rec,E.Rec,S.Rec,'0',EE.Rec,'1',EE.Rec,
		    DlySumTotal,PayMoney,DlySumQty,PreferenceMoney,-10,'1','0','1','0',IsNULL(D.Rec,''),
		   '0','0',BillDate,DlySumTotal-PayMoney-PreferenceMoney,'','0','7','0',
		   '0','0','0','0','z','','0','0','1','','0','0','0'
		   From Woolinte_BillIndex A Left Join Department D On D.TypeID=A.DTypeID_,
		   BType B,Stock S,Employee E,Employee EE 
		   where A.BTypeID_=B.TypeID and A.ETypeID_=E.TypeID and A.KTypeID_=S.Rec 
		   and A.UserTypeID=EE.TypeID and A.BillnumberID=@BillNumberID
		   group by BillDate,A.Comment,B.Rec,E.Rec,S.Rec,EE.Rec,PayMoney,DlySumQty,PreferenceMoney,
		   D.Rec,BillDate,DlySumTotal;
		   
            if(@@ERROR<>0)
                   goto Error1 
            Select @VchCode=@@IDENTITY;
            
            Insert Into SaleBill(BillID,KRec,GRec,PRec,Qty,AssQty,SalePrice,AssSalePrice,DisCount,DiscountPrice,AssDiscountPrice,
            Total,Tax,TaxPrice,AssTaxPrice,TaxTotal,CostPrice,JobNumber,OutFactoryDate,ValidityPeriod,JobCode,GermJobNumber,CheckReportNo,
            Providerid,PTypeSource,Comment,Eligible,Unit,UnitRate,Retailprice,Ord,PriceType)
             Select @VchCode,S.Rec,'0',P.Rec,BillQty,PDAQty,BillDistPrice,PDAPrice,Discount,BillDistPrice,Discount*PDAPrice,
            Cast(Sum(BillDistPrice*BillQty) as Decimal(18,2)),P.LTax,BillDistPrice*(1+P.LTax/100),PDAPrice*Discount*(1+P.LTax/100),Cast(Sum(BillDistPrice*BillQty)*(1+P.LTax/100) as Decimal(18,2)),
            AL.CostPrice,GoodsNumber,Case when ProduceDate='' Then Null Else ProduceDate End,Case when ValidDate='' Then Null Else ValidDate End,
            '','',@CheckReportNo,'2','0',AL.Comment,'',AL.UintNum,AL.UintRate,AL.RetailPrice,AL.DetailRowID,'0'
            From Woolinte_BillIndex_List AL,Stock S,PType P
            where S.TypeID=@KTypeID_ and P.Rec=AL.PTypeID_ and AL.Billnumberid=@BillNumberID 
            Group by S.Rec,P.Rec,BillQty,BillPrice,PDAQty,UnitPrice,Discount,BillDistPrice,AL.UintRate,P.LTax,PDAPrice,
            GoodsNumber,ProduceDate,ValidDate,AL.Comment,AL.UintNum,AL.UintRate,AL.RetailPrice,AL.CostPrice,AL.DetailRowID
            Order by MIN(DetailRowID) Asc,AL.UintNum desc;
          if(@@ERROR<>0)
                   goto Error1
                   
                    UPDATE Billindex SET TotalMoney=a.TaxTotal FROM (SELECT ISNULL(SUM(TaxTotal),0) TaxTotal,BillID FROM SaleBill WHERE BillID=@VchCode GROUP BY BillID) a WHERE Billindex.BillID=a.BillID;   
	
		 end
		 
		 if @BillType='BuyBack'
		  begin
		     SET @BillTypeID='6'
		     
		   Insert Billindex(UniqueBillID,BillDate,BillCode,BillType,Comment,Explain,BRec,ERec,KRec,KRec2,Checke,IFChecked,BillE,
		   TotalMoney,Totalinmoney,TotalQty,BenefitMoney,Tax,Period,RedWord,Draft,OrderID,Department,
		   PosID,Area,MoneyDate,JSYeTotal,JSFlag,QualityStatus,GspID,PriceDiff,BillStatus,PrintTime,
		   NSysFlag,OfficeID,OutStockLaw,SendWay,IsRedWordNew,BillDetailCount,CanDelete,Invoice,Status,Flag,VipCardID)
		   Select @BillGuID,BillDate,@Number,@BillTypeID,A.Comment,@BillComment,B.Rec,E.Rec,S.Rec,'0',EE.Rec,'1',EE.Rec,
		   DlySumTotal,PayMoney,DlySumQty,PreferenceMoney,-10,'1','0','1','0',IsNULL(D.Rec,''),
		   '0','0',BillDate,DlySumTotal-PayMoney-PreferenceMoney,'','0','7','0',
		   '0','0','0','0','z','','0','0','1','','0','0','0'
		   From Woolinte_BillIndex A Left Join Department D On D.TypeID=A.DTypeID_,
		   BType B,Stock S,Employee E,Employee EE
		   where A.BTypeID_=B.TypeID and A.ETypeID_=E.TypeID and A.KTypeID_=S.Rec 
		   and A.UserTypeID=EE.TypeID and A.BillnumberID=@BillNumberID
		   group by BillDate,A.Comment,B.Rec,E.Rec,S.Rec,EE.Rec,PayMoney,DlySumQty,PreferenceMoney,
		   D.Rec,BillDate,DlySumTotal;
		   
             if(@@ERROR<>0)
                   goto Error1 
              Select @VchCode=@@IDENTITY;
              
              Insert Into BuyBill(BillID,KRec,GRec,PRec,Qty,AssQty,SalePrice,AssSalePrice,DisCount,DiscountPrice,AssDiscountPrice,
            Total,Tax,TaxPrice,AssTaxPrice,TaxTotal,CostPrice,JobNumber,OutFactoryDate,ValidityPeriod,JobCode,GermJobNumber,CheckReportNo,
            Providerid,PTypeSource,Comment,Eligible,Unit,UnitRate,Retailprice,Ord,PriceType)
             Select @VchCode,S.Rec,'0',P.Rec,BillQty,PDAQty,BillDistPrice,PDAPrice,Discount,BillDistPrice,Discount*PDAPrice,
            Cast(Sum(BillDistPrice*BillQty) as Decimal(18,2)),P.LTax,BillDistPrice*(1+P.LTax/100),PDAPrice*Discount*(1+P.LTax/100),Cast(Sum(BillDistPrice*BillQty)*(1+P.LTax/100) as Decimal(18,2)),
            AL.CostPrice,GoodsNumber,Case when ProduceDate='' Then Null Else ProduceDate End,Case when ValidDate='' Then Null Else ValidDate End,
            '','',@CheckReportNo,'2','0',AL.Comment,'',AL.UintNum,AL.UintRate,AL.RetailPrice,AL.DetailRowID,'0'
            From Stock S,PType P,Woolinte_BillIndex_List AL
            where S.TypeID=@KTypeID_ and P.Rec=AL.PTypeID_ AND AL.Billnumberid=@BillNumberID 
            Group by S.Rec,P.Rec,BillQty,BillPrice,PDAQty,UnitPrice,Discount,BillDistPrice,AL.UintRate,P.LTax,PDAPrice,
            GoodsNumber,ProduceDate,ValidDate,AL.Comment,AL.UintNum,AL.UintRate,AL.RetailPrice,AL.CostPrice,AL.DetailRowID
            Order by MIN(DetailRowID) Asc,AL.UintNum desc;
            
          if(@@ERROR<>0)
                   goto Error1
                     UPDATE Billindex SET TotalMoney=a.TaxTotal FROM (SELECT ISNULL(SUM(TaxTotal),0) TaxTotal,BillID FROM BuyBill WHERE BillID=@VchCode GROUP BY BillID) a WHERE Billindex.BillID=a.BillID;   
	
		  END
		--收款单  
	  if @BillType='CollectionBill'
		  begin
		     SET @BillTypeID='4'
		   Insert Billindex(UniqueBillID,BillDate,BillCode,BillType,Comment,Explain,BRec,ERec,KRec,KRec2,Checke,IFChecked,BillE,
		   TotalMoney,Totalinmoney,TotalQty,BenefitMoney,Tax,Period,RedWord,Draft,OrderID,Department,
		   PosID,Area,MoneyDate,JSYeTotal,JSFlag,QualityStatus,GspID,PriceDiff,BillStatus,PrintTime,
		   NSysFlag,OfficeID,OutStockLaw,SendWay,IsRedWordNew,BillDetailCount,CanDelete,Invoice,Status,Flag,VipCardID)
		   Select @BillGuID,BillDate,@Number,@BillTypeID,A.Comment,@BillComment,B.Rec,E.Rec,'0','0',EE.Rec,'1',EE.Rec,
		   DlySumTotal,DlySumTotal,DlySumQty,PreferenceMoney,-10,'1','0','1','0',IsNULL(D.Rec,''),
		   '0','0',BillDate,DlySumTotal-PayMoney-PreferenceMoney,'','0','7','0',
		   '0','0','0','0','z','','0','0','1','','0','0','0'
		   From Woolinte_BillIndex A Left Join Department D On D.TypeID=A.DTypeID_,
		   BType B,Employee E,Employee EE
		   where A.BTypeID_=B.TypeID and A.ETypeID_=E.TypeID
		   and A.UserTypeID=EE.TypeID and A.BillnumberID=@BillNumberID
		   group by BillDate,A.Comment,B.Rec,E.Rec,EE.Rec,PayMoney,DlySumQty,PreferenceMoney,
		   D.Rec,BillDate,DlySumTotal;
		   
             if(@@ERROR<>0)
                   goto Error1 
              Select @VchCode=@@IDENTITY;
              
              Insert Into MoneyBill(BillID,Ord,ARec,total,Comment)
            Select @VchCode,'1',A.Rec,BillTotal,AL.Comment From Atype A,Woolinte_BillIndex_List AL
            where A.Rec=AL.PTypeID_ and AL.Billnumberid=@BillNumberID 
            Group by A.Rec,BillTotal,AL.Comment;
          if(@@ERROR<>0)
                   goto Error1
                   
		  END
--付款单  
	  if @BillType='PayBill'
		  begin
		     SET @BillTypeID='66'
		   Insert Billindex(UniqueBillID,BillDate,BillCode,BillType,Comment,Explain,BRec,ERec,KRec,KRec2,Checke,IFChecked,BillE,
		   TotalMoney,Totalinmoney,TotalQty,BenefitMoney,Tax,Period,RedWord,Draft,OrderID,Department,
		   PosID,Area,MoneyDate,JSYeTotal,JSFlag,QualityStatus,GspID,PriceDiff,BillStatus,PrintTime,
		   NSysFlag,OfficeID,OutStockLaw,SendWay,IsRedWordNew,BillDetailCount,CanDelete,Invoice,Status,Flag,VipCardID)
		   Select @BillGuID,BillDate,@Number,@BillTypeID,A.Comment,@BillComment,B.Rec,E.Rec,'0','0',EE.Rec,'1',EE.Rec,
		   DlySumTotal,DlySumTotal,DlySumQty,PreferenceMoney,-10,'1','0','1','0',IsNULL(D.Rec,''),
		   '0','0',BillDate,DlySumTotal-PayMoney-PreferenceMoney,'','0','7','0',
		   '0','0','0','0','z','','0','0','1','','0','0','0'
		   From Woolinte_BillIndex A Left Join Department D On D.TypeID=A.DTypeID_,
		   BType B,Employee E,Employee EE
		   where A.BTypeID_=B.TypeID and A.ETypeID_=E.TypeID
		   and A.UserTypeID=EE.TypeID and A.BillnumberID=@BillNumberID
		   group by BillDate,A.Comment,B.Rec,E.Rec,EE.Rec,PayMoney,DlySumQty,PreferenceMoney,
		   D.Rec,BillDate,DlySumTotal;
		   
             if(@@ERROR<>0)
                   goto Error1 
              Select @VchCode=@@IDENTITY;
              
              Insert Into MoneyBill(BillID,Ord,ARec,total,Comment)
            Select @VchCode,'1',A.Rec,BillTotal,AL.Comment From Atype A,Woolinte_BillIndex_List AL
            where A.Rec=AL.PTypeID_ and AL.Billnumberid=@BillNumberID 
            Group by A.Rec,BillTotal,AL.Comment;
          if(@@ERROR<>0)
                   goto Error1
                   
		  END
--现金费用单  
	  if @BillType='CashBill'
		  begin
		     SET @BillTypeID='35'
		   Insert Billindex(UniqueBillID,BillDate,BillCode,BillType,Comment,Explain,BRec,ERec,KRec,KRec2,Checke,IFChecked,BillE,
		   TotalMoney,Totalinmoney,TotalQty,BenefitMoney,Tax,Period,RedWord,Draft,OrderID,Department,
		   PosID,Area,MoneyDate,JSYeTotal,JSFlag,QualityStatus,GspID,PriceDiff,BillStatus,PrintTime,
		   NSysFlag,OfficeID,OutStockLaw,SendWay,IsRedWordNew,BillDetailCount,CanDelete,Invoice,Status,Flag,VipCardID)
		   Select @BillGuID,BillDate,@Number,@BillTypeID,A.Comment,@BillComment,B.Rec,E.Rec,'0','0',EE.Rec,'1',EE.Rec,
		   DlySumTotal,DlySumTotal,DlySumQty,PreferenceMoney,-10,'1','0','1','0',IsNULL(D.Rec,''),
		   '0','0',BillDate,DlySumTotal-PayMoney-PreferenceMoney,'','0','7','0',
		   '0','0','0','0','z','','0','0','1','','0','0','0'
		   From Woolinte_BillIndex A Left Join Department D On D.TypeID=A.DTypeID_,
		   BType B,Employee E,Employee EE
		   where A.BTypeID_=B.TypeID and A.ETypeID_=E.TypeID
		   and A.UserTypeID=EE.TypeID and A.BillnumberID=@BillNumberID
		   group by BillDate,A.Comment,B.Rec,E.Rec,EE.Rec,PayMoney,DlySumQty,PreferenceMoney,
		   D.Rec,BillDate,DlySumTotal;
		   
             if(@@ERROR<>0)
                   goto Error1 
              Select @VchCode=@@IDENTITY;
              
              Insert Into MoneyBill(BillID,Ord,ARec,total,Comment)
            Select @VchCode,'1',A.Rec,BillTotal,AL.Comment From Atype A,Woolinte_BillIndex_List AL
            where A.Rec=AL.PTypeID_ and AL.Billnumberid=@BillNumberID 
            Group by A.Rec,BillTotal,AL.Comment;
          if(@@ERROR<>0)
                   goto Error1
                   
		  END
--一般费用  
	  if @BillType='MoneyBill'
		  begin
		     SET @BillTypeID='36'
		   Insert Billindex(UniqueBillID,BillDate,BillCode,BillType,Comment,Explain,BRec,ERec,KRec,KRec2,Checke,IFChecked,BillE,
		   TotalMoney,Totalinmoney,TotalQty,BenefitMoney,Tax,Period,RedWord,Draft,OrderID,Department,
		   PosID,Area,MoneyDate,JSYeTotal,JSFlag,QualityStatus,GspID,PriceDiff,BillStatus,PrintTime,
		   NSysFlag,OfficeID,OutStockLaw,SendWay,IsRedWordNew,BillDetailCount,CanDelete,Invoice,Status,Flag,VipCardID)
		   Select @BillGuID,BillDate,@Number,@BillTypeID,A.Comment,@BillComment,B.Rec,E.Rec,'0','0',EE.Rec,'1',EE.Rec,
		   DlySumTotal,DlySumTotal,DlySumQty,PreferenceMoney,-10,'1','0','1','0',IsNULL(D.Rec,''),
		   '0','0',BillDate,DlySumTotal-PayMoney-PreferenceMoney,'','0','7','0',
		   '0','0','0','0','z','','0','0','1','','0','0','0'
		   From Woolinte_BillIndex A Left Join Department D On D.TypeID=A.DTypeID_,
		   BType B,Employee E,Employee EE
		   where A.BTypeID_=B.TypeID and A.ETypeID_=E.TypeID
		   and A.UserTypeID=EE.TypeID and A.BillnumberID=@BillNumberID
		   group by BillDate,A.Comment,B.Rec,E.Rec,EE.Rec,PayMoney,DlySumQty,PreferenceMoney,
		   D.Rec,BillDate,DlySumTotal;
		   
             if(@@ERROR<>0)
                   goto Error1 
              Select @VchCode=@@IDENTITY;
              
              Insert Into MoneyBill(BillID,Ord,ARec,total,Comment)
            Select @VchCode,'1',A.Rec,BillTotal,AL.Comment From Atype A,Woolinte_BillIndex_List AL
            where A.Rec=AL.PTypeID_ and AL.Billnumberid=@BillNumberID 
            Group by A.Rec,BillTotal,AL.Comment;
          if(@@ERROR<>0)
                   goto Error1
                   
		  END
		  
		   if @BillType='SameMove'
		 begin
		   set @BillTypeID='17'
		   
			Insert Billindex(UniqueBillID,BillDate,BillCode,BillType,Comment,Explain,BRec,ERec,KRec,KRec2,Checke,IFChecked,BillE,
		   TotalMoney,Totalinmoney,TotalQty,BenefitMoney,Tax,Period,RedWord,Draft,OrderID,Department,
		   PosID,Area,MoneyDate,JSYeTotal,JSFlag,QualityStatus,GspID,PriceDiff,BillStatus,PrintTime,
		   NSysFlag,OfficeID,OutStockLaw,SendWay,IsRedWordNew,BillDetailCount,CanDelete,Invoice,Status,Flag,VipCardID)
		   Select @BillGuID,BillDate,@Number,@BillTypeID,A.Comment,@BillComment,'0',E.Rec,KTypeID_,KTypeID2_,EE.Rec,'1',EE.Rec,
		   DlySumTotal,PayMoney,DlySumQty,PreferenceMoney,-10,'1','0','1','0','0',
		   '0','0',BillDate,DlySumTotal-PayMoney-PreferenceMoney,'','0','7','0',
		   '0','0','0','0','z','','0','0','1','','0','0','0'
		   From Woolinte_BillIndex A ,Employee E,Employee EE		   
		   where A.ETypeID_=E.TypeID and A.UserTypeID=EE.TypeID and BillnumberID=@BillNumberID;
		   
           if(@@ERROR<>0)
                   goto Error1 
            Select @VchCode=@@IDENTITY;
            
            Insert Into MoveBill(BillID,Ord,PRec,KRec,GRec,KRec2,GRec2,Unit,UnitRate,Qty,Rate,Price,AssQty,AssPrice,
            Total,JobNumber,OutFactoryDate,ValidityPeriod,JobCode,GermJobNumber,CheckReportNo,CostPrice,
            Providerid,PTypeSource,StopUse,Retailprice,Comment)
     
            Select @VchCode,al.DetailRowID,P.Rec,KTypeID_,'0',KTypeID2_,'0',AL.UintNum,AL.UintRate,PDAQty,AL.UintRate,BillPrice,BillQty,BillPrice,
            BillTotal,GoodsNumber,Case when ProduceDate='' Then Null Else ProduceDate End,Case when ValidDate='' Then Null Else ValidDate End,'','',@CheckReportNo,AL.CostPrice,
            '0','0','0',BillPrice,AL.Comment
            From Woolinte_BillIndex_List AL,PType P
            where P.Rec=AL.PTypeID_ and AL.Billnumberid=@BillNumberID 
            Group by P.Rec,KTypeID_,KTypeID2_,AL.UintNum,AL.UintRate,BillQty,BillPrice,
            BillTotal,GoodsNumber,ProduceDate,AL.CostPrice,BillPrice,AL.Comment,PDAQty,al.DetailRowID,ValidDate
          if(@@ERROR<>0)
                   goto Error1
		 end
		 
		 
		 INSERT	INTO ShortMessages(SendTime,PostID,ReceiveID,Data,Status,BillType,SendType,BillGuid,IsSend)            
         SELECT Convert(varchar(100),getdate(),120),@UserTypeID,A.ERec,
         '【'+ @UserProductName + '】-提醒您:'+ Char(13) + Char(13) 
         +'【'+ @UserFullName +'】开了一张单据编号为【'+ @number +'】 的'+@BillName +'!',
         '0',@BillTypeID,'2',@BillGuID,'0'
         From LoginUser A;
         if(@@ERROR<>0)
         goto Error1
           
            Update Woolinte_BillIndex set state='3',VchCode=@VchCode where billnumberid=@BillNumberID and deleted='0' and state='1';
            Select '1' as ReturnValue;
        end
      commit tran
      return ;
  Error1:
        RollBack Tran
        ----ROLLBACK TRANSACTION
