--3240 Kasa Prodavnica 4,   01.01.2020 – 20.4.2020 

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

select g.datum, p.ident, sum(p.kolicina) as kolicina
into #t1
from rGlava g
inner join rPozicije p on g.kljuc = p.kljuc
where g.datum between '2020-01-01' and '2020-04-20' and g.blagajna = '3240'
group by g.datum, p.ident
--54952
--select count(*) from #t2

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

select g.datum, p.ident, sum(p.kolicina) as kolicina
into #t2
from rGlavaSUM g
inner join rPozicijeSUM p on g.kljuc = p.kljuc
where g.datum between '2020-01-01' and '2020-04-20' and g.blagajna = '3240'
group by g.datum, p.ident
--54952

update #t1 set ident = '' where ident is null
update #t2 set ident = '' where ident is null


--provjera artikala, da mozda nema sta sinhronizovano, preskoceno
select distinct ident from #t1 where ident not in (select distinct ident from #t2)

--ovo je obrnuto, ne bi smjelo biti jer zbirni nastaju od detaljnih prometa
select distinct ident from #t2 where ident not in (select distinct ident from #t1)

--ako je ovo iznad sve ok, mozemo ici na poredjenje kolicina
--ako nema stavki onda je sve ok, sad trba provjeriti zbirne racune sa pantheonom!
select g.*, t.kolicina, round(g.kolicina - t.kolicina,3) as Razlika
from #t1 g
inner join #t2 t on g.datum = t.datum and g.ident = t.ident
where round(g.kolicina - t.kolicina,3)<> 0


--temp tabela iz pantheona
if object_id('tempdb..#t3') is not null
	drop table #t3

select g.addate, p.acident, sum(p.anqty) as anqty
into #t3
from tHE_Move g
inner join tHE_MoveItem p on g.acKey = p.acKey
where g.addate between '2020-01-01' and '2020-04-20' and g.acdoctype = '3240'
group by g.addate, p.acident

update #t3 set acident = '' where acident is null
select distinct acident from #t3 where acident not in (select distinct ident from #t2)

select g.*, t.anqty, round(g.kolicina - t.anqty,3) as Razlika
from #t1 g
inner join #t3 t on g.datum = t.addate and g.ident = t.acident
where round(g.kolicina - t.anqty,3)<> 0

-- posto ima razlika, a ne kontam kako, sad cemo zbirne racune (oni su ok) da uporedimo sa pantheonovim
--nedostajuci racunu
select g.kljuc, t.acKey
from vGlavaSUM g
left join tHE_Move t on g.kljuc = t.acKey
where t.acKey is null
--nedostajuce pozicije
select g.kljuc, t.acKey, g.poz, t.anNo
from vPozicijeSUM g
left join tHE_MoveItem t on g.kljuc = t.acKey and g.poz = t.anNo
where t.acKey is null and t.anNo is null


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

if object_id('tempdb..#t41') is not null
	drop table #t41
	
select g.kljuc, g.poz, g.kolicina
into #t41
from vGlavaSUM gg 
inner join vPozicijeSUM g on gg.kljuc = g.kljuc
where gg.datum between '2020-01-01' and '2020-04-20'


--razlike u kolicini
select g.kljuc, t.acKey, g.poz, t.anNo, round(g.kolicina - t.anqty,3) as Razlika
into #t4
from #t41
inner join tHE_MoveItem t on g.kljuc = t.acKey and g.poz = t.anNo
where round(g.kolicina - t.anqty,3)<> 0
--------



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

if object_id('tempdb..#t41') is not null
	drop table #t41
	
select g.kljuc, g.poz, g.kolicina
into #t41
from rGlavaSUM gg 
inner join rPozicijeSUM g on gg.kljuc = g.kljuc
where gg.datum between '2020-01-01' and '2020-04-20'


alter table #t41
add kljuc1 char (13)


update  #t41
set kljuc1 = left(replace(kljuc, '-',''),6)+ replace(str(rtrim(substring(replace(kljuc, '-',''),7,6)),7),' ','0')


--razlike u kolicini
select g.kljuc1, t.acKey, g.poz, t.anNo, round(g.kolicina - t.anqty,3) as Razlika
--into #t4
from #t41 g
inner join tHE_MoveItem t on g.kljuc1 = t.acKey and g.poz = t.anNo
where round(g.kolicina - t.anqty,3)<> 0

------------

select sum((p.mpc - p.mpc * p.rabat / 100)*p.kolicina)
from rGlava g
inner join rPozicije p on g.kljuc = p.kljuc
where g.blagajna = '3240' and g.datum between '2020-01-01' and '2020-04-20'


select sum((p.mpc - p.mpc * p.rabat / 100)*p.kolicina)
from rGlavaSUM g
inner join rPozicijeSUM p on g.kljuc = p.kljuc
where g.blagajna = '3240' and g.datum between '2020-01-01' and '2020-04-20'



select sum((p.anRTPRice - p.anRTPRice * p.anRebate / 100)*p.anQty)
from tHE_Move g
inner join tHE_MoveItem p on g.acKey = p.acKey
where g.acDocType = '3240' and g.adDate between '2020-01-01' and '2020-04-20'

--------------




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

if object_id('tempdb..#t41') is not null
	drop table #t41
	
select g.kljuc, g.poz, g.kolicina
into #t41
from rGlavaSUM gg 
inner join rPozicijeSUM g on gg.kljuc = g.kljuc
where gg.datum between '2020-01-01' and '2020-04-20'


alter table #t41
add kljuc1 char (13)


update  #t41
set kljuc1 = left(replace(kljuc, '-',''),6)+ replace(str(rtrim(substring(replace(kljuc, '-',''),7,6)),7),' ','0')


--razlike u kolicini
select g.kljuc1, t.acKey, g.poz, t.anNo, round(g.kolicina - t.anqty,3) as Razlika
--into #t4
from #t41 g
inner join tHE_MoveItem t on g.kljuc1 = t.acKey and g.poz = t.anNo
where round(g.kolicina - t.anqty,3)<> 0

----------
razlika u blagajnoj 
drop table #r
drop table #b

select p.kljuc, sum((p.mpc-p.mpc*p.rabat/100)* p.kolicina) as Iznos
into #r
from rGlava g
inner join rPozicije p on g.kljuc = p.kljuc
where g.datum between '2020-01-01' and '2020-04-14'
group by p.kljuc

select p.kljuc, sum(p.Iznos) as Iznos
into #b
from rGlava g
inner join rGlavaBlg p on g.kljuc = p.kljuc
where g.datum between '2020-01-01' and '2020-04-14'
group by p.kljuc


select *, g.iznos-t.iznos 
from #r g
inner join #b t on g.kljuc = t.kljuc
where g.iznos-t.iznos  <> 0