PS Roba sa tacnim mpc radi TKM-a

--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