if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_AutoOrder]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_AutoOrder]
GO

--SaleCYC 计算日均销量的周期
--OrderCYC 采购参数
--OrderParam 采购周期 采购数量=周期内日均销量*采购周期*采购参数/100-现有库存-在途商品
CREATE PROCEDURE sp_AutoOrder @IsInt int=0
With Encryption
AS
  delete from TAutoOrder
    
  insert into TAutoOrder
     (ProdCode,ProdName,BarCode,Spec,DepCode,SupCode,ColorCode,SizeCode,AvgXSCount,
      OTax, Hlimit, Llimit,HOPrice,LOPrice,COPrice,SaleCYC,OrderCYC,OrderParam ,
      OrderCount,OrderPrice,OutOrdPrice,OrderCost,OutOrdCost,OrderTotal,MbarFlag,price,Amount,
      Punit,PunitAmt,CountP,Unit,CountM)
    select a.ProdCode,
         ProdName=Case MbarFlag when '1'  then b.prodName else a.prodName end,
         barcode=Case MbarFlag when '1' then isnull(b.barcode,isnull(a.barCode,'')) else isnull(a.barCode,'') end,
         spec=Case MbarFlag when 0 then a.spec else b.spec end,
         DepCode,SupCode,isnull(ColorCode,''),isnull(SizeCode,''),0,
         OTax, Hlimit, Llimit,HOPrice,LOPrice,COPrice,SaleCYC,OrderCYC,OrderParam ,
         0,COPrice,outOprice,0,0,0,MbarFlag,price,
         Amount=Case MbarFlag when '0'  then a.AmountC+a.AmountS else b.AmountC+b.AmountS end,
         C.UnitName,a.PunitAmt,0,d.UnitName,0        
    from product a left join mbarCode b 
    on a.prodcode=b.Prodcode  and MBarFlag='1' 
    left join tUnitSet c on a.PunitCode=c.UnitCode 
    left join tUnitSet d on a.UnitCode=d.UnitCode 
    where  (KCFlag='1')and(tag<>'2') and (prodType='0') 
      and  (SupCode<>'')and(SaleCYC>0)and(OrderCYC>0)and(OrderParam>0)

   
   update TAutoOrder set OrderPrice=B.Oprice,OutOrdPrice=B.OutOPrice
   from TAutoOrder A,QuotePrice B
   where A.prodCode = B.prodCode and A.SupCode = B.SupCode 
  
 
   update A       --临时报价
     set OrderPrice=B.Oprice,OutOrdPrice=B.OutOPrice
     from TAutoOrder A, tmpQuotePrice B
     where A.prodCode = B.prodCode and A.SupCode = B.SupCode 
          and (convert(varchar(10), getDate(), 121) between B.BeginDate and B.EndDate)



  declare @ProdCode varchar(18),@BarCode varchar(18),@SaleCYC int,@MbarFlag char(1),@TableName varchar(20)
  declare @BegDate varchar(10), @EndDate varchar(10),@SQL varchar(5000)
  declare @Amount money,@OrderCYC int

  DECLARE AutoOrder_csr CURSOR  FOR
   select distinct ProdCode,BarCode,SaleCYC,MbarFlag,OrderCYC from TAutoOrder
  OPEN AutoOrder_csr

  FETCH NEXT FROM AutoOrder_csr INTO  @ProdCode,@BarCode ,@SaleCYC ,@MbarFlag,@OrderCYC
  WHILE (@@FETCH_STATUS =0)
  begin	
    select @BegDate=convert(char(10),getdate()-@SaleCYC,121)
    select @EndDate=convert(char(10),getdate(),121)

   --计算日均销量
    declare MBarRpt_csr CURSOR FOR
     select name from sysobjects 
       where name like 'ProdRpt%' and type='U'
       and (name>='ProdRpt'+Substring(@BegDate,1,4)+Substring(@BegDate,6,2)) and (name<='ProdRpt'+Substring(@EndDate,1,4)+Substring(@EndDate,6,2))
	
    OPEN MBarRpt_csr
    FETCH NEXT FROM MBarRpt_csr INTO @TableName
    While (@@FETCH_STATUS =0)
    begin
      select @SQL=' update TAutoOrder  set AvgXSCount=AvgXSCount+'+
                  '(select isnull(sum(WXCount-WTCount),0) from '+ @TableName +
                  '  where prodCode=TAutoOrder.ProdCode '+
                  ' and wDate>='''+@BegDate+''' and wDate<'''+@EndDate+''''+
                  ') where prodCode='''+@ProdCode+''''
     -- print @Sql
      EXEC (@SQL)
      FETCH NEXT FROM MBarRpt_csr INTO @TableName
    end
    CLOSE MBarRpt_csr
    DEALLOCATE MBarRpt_csr



   --计算在途数量
    
   if @MbarFlag='0'
   begin
    select @Amount=ISNULL(SUM(A.countn-countYs),0) from fcgb A ,fcgh B 
      WHERE A.FormNO=B.FormNo and B.VldDate< convert(varchar(10),getdate(),121)
      and (B.isMulYS='1' OR B.YSTIMES<='0')and(A.countn-countYs>0)and (b.checkType='3')
      and (A.prodCode=@ProdCode) 

     if @Amount>0  update TAutoOrder set Amount=Amount+@Amount where (prodCode=@ProdCode)
   end
   else begin
     select @Amount=ISNULL(SUM(A.countn-countYs),0) from fcgb A ,fcgh B 
      WHERE A.FormNO=B.FormNo and B.VldDate< convert(varchar(10),getdate(),121)
      and (B.isMulYS='1' OR B.YSTIMES<='0')and(A.countn-countYs>0)and (b.checkType='3')
      and (A.prodCode=@ProdCode) and(
A.BarCode=@BarCode)

     if @Amount>0  update TAutoOrder set Amount=Amount+@Amount where (prodCode=@ProdCode) and(BarCode=@BarCode)

   end
   
    FETCH NEXT FROM AutoOrder_csr INTO  @ProdCode,@BarCode ,@SaleCYC ,@MbarFlag,@OrderCYC
  end

  CLOSE AutoOrder_csr
  DEALLOCATE AutoOrder_csr

  update TAutoOrder set AvgXSCount=AvgXSCount/SaleCYC
 -- 采购数量=周期内日均销量*采购周期*采购参数/100-（现有库存(实时库存已经去掉正在销售的)-最低库存）-在途商品
  update TAutoOrder set OrderCount=isnull(AvgXSCount*OrderCYC*OrderParam/100.0-Amount,0)

  delete from TAutoOrder where OrderCount<=0

  update TAutoOrder set  CountM=OrderCount where isnull(Punit,'')=''

-------Punit,PunitAmt,CountP,Unit


 if  @IsInt=0 
 begin
   update TAutoOrder set CountP=FLOOR(OrderCount/PUnitAmt),
     CountM=OrderCount-FLOOR(OrderCount/PUnitAmt)*PUnitAmt
   where isnull(Punit,'')<>''
   
 end
 else begin

   update TAutoOrder set CountP=CEILING(OrderCount/PUnitAmt),
   CountM=0,OrderCount=CEILING(OrderCount/PUnitAmt)*PUnitAmt
  where isnull(Punit,'')<>''

 end

  update TAutoOrder set OrderCost=OrderCount*OrderPrice,OutOrdCost=OrderCount*OutOrdPrice,OrderTotal=OrderCount*price

  --删除禁止采购进货的商品

  delete from tAutoOrder 
  where prodCode in (select ProdCode from product where ForOrder='1')

Declare @LimitHighStorage varchar(1)
Select @LimitHighStorage=OptValue from tSysOpt where OptName='LimitHighStorage'
 
if IsNull(@LimitHighStorage,'')='1'
  Delete from TAutoOrder where ProdCode in
  ( Select a.ProdCode from TAUtoOrder a 
   left join (Select b.ProdCode,Sum(Case when C.ProdCode is Null then b.CountN else 0 end) as CountN
	    From FWXHH a join 

  (Select a.FormNo,ProdCode=Case when b.BindCode is Null then a.ProdCode else b.ProdCode end
	    ,CountN=Case when b.BindCode is Null then a.CountN else b.CountN*a.CountN end
	    from FWXHB a left join prodBind b on a.ProdType='2' and a.ProdCode=b.BindCode) b

  on a.FormNo=b.FormNo and a.CheckType='3'
	    left join (Select Distinct b.ProdCode from FWJSH a join FWJSB b on a.FormNo=b.FormNo and a.CheckType='3' and b.FType='0') as C
	    on a.FormNo=C.ProdCode  Group by b.ProdCode
	    )  as C on a.ProdCode=C.ProdCode  where a.Hlimit>0 and a.OrderCount+a.Amount+C.CountN>HLimit )