--KREIRA BACKUP I RADI RESTORE BAZE, ZATIM BRISE PODATKE IZ RANIJIH GODINA I KREIRA PS
use master
go

if exists (select * from sysdatabases where name = 'dbTEST')
begin
	DROP DATABASE dbTEST
end

go


BACKUP DATABASE [ZUINS] TO  DISK = N'D:\fullBackup\zuins.bak' WITH NOFORMAT, INIT,  NAME = N'ZUINS-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO


RESTORE DATABASE [dbTEST] FROM  DISK = N'D:\fullBackup\zuins.bak' WITH  FILE = 1,  MOVE N'DataLab_Data' TO N'C:\datalab\db\dbTEST.mdf',  MOVE N'DataLab_Log' TO N'C:\datalab\db\dbTEST.ldf',  NOUNLOAD,  STATS = 5

GO

-----------------------------------------------------
use dbTEST
go

declare @i int, @m int
set @m = (select count(*) from tHE_Move where acKey not like '19%')
set @m = @m / 1000 + 5
set @i = 0
while @i <= @m
begin
delete top (1000) tHE_Move where acKey not like '19%'

set @i = @i + 1
end

-----------------------------------------------------
use ZUINS
go

if object_id('tempdb..#z')is not null
drop table #z

create table #z
(acWareHouse char (30),
  acIdent char (16),
  anQty float)

 
insert into #z (acWareHouse, acIdent, anQty)
select acWarehouse, acIdent, sum(anQty) as anQty from
(select g.acReceiver as acWarehouse, p.acIdent, sum(p.anQty) as anQty
from tHE_Move g
inner join tHE_MoveItem p on g.acKey = p.acKey and g.adDate <= '2018-12-31'
inner join tPA_setDocType d on g.acDocType = d.acDoctype and d.acEvidence = 'F'
inner join tHE_SetItem m on p.acIdent = m.acIdent and m.acSetOfItem in (select vrstams from vrstams where tip not in ('S', 'U') and vrstams <> '')
where g.acReceiverStock = 'Y'
group by g.acReceiver, p.acIdent
    union all
select g.acIssuer as acWarehouse, p.acIdent, sum(p.anQty*-1) as anQty
from tHE_Move g
inner join tHE_MoveItem p on g.acKey = p.acKey and g.adDate <= '2018-12-31'
inner join tPA_setDocType d on g.acDocType = d.acDoctype and d.acEvidence = 'F'
inner join tHE_SetItem m on p.acIdent = m.acIdent and m.acSetOfItem in (select vrstams from vrstams where tip not in ('S', 'U') and vrstams <> '')
where g.acIssuerStock = 'Y'
group by g.acIssuer, p.acIdent) as tbl
group by acWarehouse, acIdent
having sum(anQty) <> 0

use dbTEST
go

declare @acWarehouse char (30),
@adDateinv smalldatetime,
@cKey char (13),
@acIdent char (16),
@anQty float,
@cOK varchar(2), -- status of adding
@nPoz int, -- addid position
@cError VarChar(1024)


declare btsPoz cursor local fast_forward for
    select acWarehouse from #z group by acWarehouse
open btsPoz
fetch from btsPoz into @acWarehouse
while @@fetch_status = 0
begin

-- Kreiraj glavu
exec pHE_MoveCreAll '1PS0', @acWarehouse, @acWarehouse, '2018-12-31', 2, '', @cKey output, 'F'

if object_id('tempdb..#mitem') is not null
    drop table #mitem

create table #mitem
(acIdent char (16),
anQty float,
anNo int identity (1,1) not null)

insert into #mitem (acIdent, anQty)
select acIdent, anQty from #z where acWarehouse = @acWarehouse

---tkm
if object_id('tempdb..#xps') is not null
    drop table #xps

select @acWarehouse as acWarehouse, p.acIdent,
sum(case 
    when d.acType = 'P' then p.anQty * p.anSalePrice 
    when d.acType = 'I' then p.anQty * (-1) * p.anSalePrice
    when d.acType = 'C' and g.acVerifiedPrices <> 'F' then p.anRebate * p.anPrice 
    when d.acType = 'C' and g.acVerifiedPrices = 'F' then 0
    when d.acType = 'E' and g.acReceiver = @acWarehouse and g.acIssuer <> @acWarehouse then p.anQty * p.anInSalePrice
    when d.acType = 'E' and g.acReceiver <> @acWarehouse and g.acIssuer = @acWarehouse then p.anQty * (-1) * p.anSalePrice
    when d.acType = 'M' then (round(p.anQty *(-1) * (p.anRTPrice-p.anRTPrice*p.anRebate/100),3))-(round(p.anQty *(-1) * (p.anRTPrice-p.anRTPrice*p.anRebate/100),3) - round(p.anQty *(-1) * p.anSalePrice,3))
end) as NabavaNeto
into #xps
from tHE_Move g
inner join tHE_MoveItem p on g.acKey = p.acKey
inner join tHE_SetItem m on p.acIdent = m.acIdent
inner join tHE_SetItemType v on m.acSetofItem = v.acSetofItem and v.acType not in ('U','S')
inner join tPA_SetDocType d on g.acDocType = d.acDocType and d.acEvidence = 'F'
where ((g.acReceiver = @acWarehouse and g.acReceiverStock = 'Y') or (g.acIssuer = @acWarehouse and g.acIssuerStock = 'Y') or (g.acReceiver = @acWarehouse and d.acType = 'C'))
and (g.adDate <= '2018-12-31')
group by p.acIdent
-- tkm

 
insert into tHE_MoveItem (anPVValue, anPVOCVATBase, anPVOCValue, anPVOCStockValue, anPVOCVATBaseWoExc,
anPVOCForPay, anPVOCForPayWoExc,
anPVOCIncVAT, anPVOCBeatShare, anPVOCIncVATWoExc, anPVOCBeatShareWoExc,
anPriceCurrency, anQty, acKey, anNo, acIdent, acName, acUm, anPrice, anRTPrice, anSalePrice,
    acVATCode, anVAT, anStockPrice, anInRTPrice, anInSalePrice,
    acVATCodeTR, anVATIn)

select m.anBuyPrice * t.anQty, m.anBuyPrice * t.anQty, m.anBuyPrice * t.anQty, m.anBuyPrice * t.anQty, m.anBuyPrice * t.anQty, 
    (m.anBuyPrice * t.anQty) * (m.anVAT / 100 + 1 ), (m.anBuyPrice * t.anQty) * (m.anVAT / 100 + 1 ),
    (m.anBuyPrice * t.anQty) * m.anVAT / 100 , (m.anBuyPrice * t.anQty) * m.anVAT / 100 , (m.anBuyPrice * t.anQty) * m.anVAT / 100 , (m.anBuyPrice * t.anQty) * m.anVAT / 100 ,
    m.anBuyPrice, t.anQty, @cKey, t.anNo, m.acIdent, m.acName, m.acUM, m.anBuyPrice, case when c.NabavaNeto is null then m.anRTPrice else c.NabavaNeto/t.anQty/1.17 end, case when c.NabavaNeto is null then m.anSalePrice else c.NabavaNeto/t.anQty end,
    m.acVATCode, m.anVAT, m.anBuyPrice, case when c.NabavaNeto is null then m.anRTPrice else c.NabavaNeto/t.anQty/1.17 end, case when c.NabavaNeto is null then m.anSalePrice else c.NabavaNeto/t.anQty end,
    m.acVATCode, m.anVAT
    from tHE_SetItem m
    inner join #mitem t on m.acIdent = t.acIdent
    left join #xps c on m.acIdent = c.acIdent and c.acWarehouse = @acWarehouse

fetch from btsPoz into @acWarehouse
end
close btsPoz
deallocate btsPoz