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

create procedure SpPCData(@AdepCode varchar(6),@AstoreCode varchar(2),@AsaveDate varchar(10))
With Encryption
as
--drop table pcdata
  create table #PCData
    (prodCode char(18) not null ,barcode char(18),prodName char(40),
     spec char(20),storeCode char(2),DepCode char(6),
    Amount money,Total money ,Cost money,OutCost money,
    RAmount money,RTotal money,RCost money,ROutCost money,
    PyAmount money,pyTotal money ,pyCost money,pyOutCost money,
     PkAmount money,pkTotal money ,pkCost money,pkOutCost money,
    price money,Oprice decimal(19,10),OutOprice decimal(19,10))

  insert into #PCData     
  select a.prodCode,a.barcode,a.ProdName, a.spec,StoreCode,a.DepCode,
    Amount,Total=Amount*price,Cost=Amount*oprice,OutCost=Amount*Outoprice,
    RAmount,RTotal=pc.Total,RCost=pc.Cost,ROuntCost=pc.OutCost,
    PyAmount=Case when RAmount-Amount>0 then RAmount-Amount else 0 end,0,0,0,
    PkAmount=Case when RAmount-Amount<=0 then abs(RAmount-Amount) else 0 end,0,0,0,
    price,Oprice,outOprice 
    from accountData a inner join product b
    on a.prodcode=b.prodcode
    left join (Select b.ProdCode,b.BarCode,Cost=Sum(b.Cost),OutCost=Sum(b.OutCost),Total=Sum(b.Total) from FPCB B join FPCH a 
    on a.FormNo=b.FormNo and a.SaveDate=@AsaveDate and StoreCode=@AstoreCode and a.CheckType='3'
    join (Select DepCode,SubCode from DepItem Union Select '0',';0;') c on a.DepCode=c.DepCode and c.SubCode like '%;'+@AdepCode+';%'
    Group by b.ProdCode,b.BarCode
    ) as pc on b.ProdCode=pc.ProdCode
    where StoreCode=@AstoreCode and chkFlag='1' and SaveDate=@AsaveDate
        and a.DepCode in(select DepCode from DepItem where SubCode like '%;'+@AdepCode+';%')


  update #PCData set pyTotal=pyAmount*Price,pyCost=pyAmount*Oprice,pyOutCost=pyAmount*Oprice,
                    pkTotal=pkAmount*Price,pkCost=pkAmount*Oprice,pkOutCost=pkAmount*Oprice

  select * from #pcdata
  
  drop table #pcdata

