
/*Wyrownanie stanow magazynowych z dokumentami*/

begin tran

declare @guidEmpty uniqueidentifier = newId()
declare @ges char(36) = cast(@guidEmpty as char(36))

select  sb.value, calcStB.itemValue, sb.quantity, calcStB.itemquantity, sb.itemID, sb.warehouseID
from wms.stockBalance sb
full join
(
	select sum(dl.sign * dl.itemQuantity) itemQuantity, sum(isnull(cordl.sign, dl.sign) * dl.sysItemValue) itemValue, dl.itemID, dl.locationID, dl.itemSerialNumber, warehouseID 
	from document.documentLine dl
	inner join document.document d on d.documentID = dl.documentID
	LEFT JOIN document.documentLine cordl ON dl.initialSourceDocumentLineId = cordl.documentLineID and dl.isCorrectiveLine = 1
	where dl.status >= 1
	group by dl.itemID, dl.locationID, dl.itemSerialNumber, d.warehouseID
) calcStB
on sb.itemID = calcStB.itemID and sb.warehouseID = calcStB.warehouseID and isnull(sb.locationID, @guidEmpty) = isnull(calcStB.locationID, @guidEmpty) and isnull(sb.itemSerialNumber, @ges) = isnull(calcStB.itemSerialNumber, @ges)
where isnull(sb.quantity, -1) != isnull(calcStB.itemQuantity, -1)
or isnull(sb.value, -1) != isnull(calcStB.itemValue, -1)

update sb
set sb.value = isnull(calcStB.itemValue, 0), sb.quantity = isnull(calcStB.itemquantity,0)
from 
wms.stockBalance sb
full join
(
	select sum(dl.sign * dl.itemQuantity) itemQuantity, sum(isnull(cordl.sign, dl.sign) * dl.sysItemValue) itemValue, dl.itemID, dl.locationID, dl.itemSerialNumber, warehouseID 
	from document.documentLine dl
	inner join document.document d on d.documentID = dl.documentID
	LEFT JOIN document.documentLine cordl ON dl.initialSourceDocumentLineId = cordl.documentLineID and dl.isCorrectiveLine = 1
	where dl.status >= 1
	group by dl.itemID, dl.locationID, dl.itemSerialNumber, d.warehouseID
) calcStB
on sb.itemID = calcStB.itemID and sb.warehouseID = calcStB.warehouseID and isnull(sb.locationID, @guidEmpty) = isnull(calcStB.locationID, @guidEmpty) and isnull(sb.itemSerialNumber, @ges) = isnull(calcStB.itemSerialNumber, @ges)
where isnull(sb.quantity, -1) != isnull(calcStB.itemQuantity, -1)
or isnull(sb.value, -1) != isnull(calcStB.itemValue, -1)

select  sb.value, calcStB.itemValue, sb.quantity, calcStB.itemquantity
from wms.stockBalance sb
full join
(
	select sum(dl.sign * dl.itemQuantity) itemQuantity, sum(isnull(cordl.sign, dl.sign) * dl.sysItemValue) itemValue, dl.itemID, dl.locationID, dl.itemSerialNumber, warehouseID 
	from document.documentLine dl
	inner join document.document d on d.documentID = dl.documentID
	LEFT JOIN document.documentLine cordl ON dl.initialSourceDocumentLineId = cordl.documentLineID and dl.isCorrectiveLine = 1
	where dl.status >= 1
	group by dl.itemID, dl.locationID, dl.itemSerialNumber, d.warehouseID
) calcStB
on sb.itemID = calcStB.itemID and sb.warehouseID = calcStB.warehouseID and isnull(sb.locationID, @guidEmpty) = isnull(calcStB.locationID, @guidEmpty) and isnull(sb.itemSerialNumber, @ges) = isnull(calcStB.itemSerialNumber, @ges)
where isnull(sb.quantity, -1) != isnull(calcStB.itemQuantity, -1)
or isnull(sb.value, -1) != isnull(calcStB.itemValue, -1)

select  sb.valuein, calcStB.itemvaluein, sb.valueOut, calcStB.itemvalueOut, sb.itemID, sb.warehouseID, sb.stockBalanceDate, war.locationCode
from wms.stockBalanceDate sb
inner join wms.location war on war.locationID = sb.warehouseID
full join
(
	select 
	sum(case when dl.sign = 1 then 1 else 0 end * dl.itemQuantity) itemQuantityIn
	, sum(case when dl.sign = 1 then 1 else 0 end * isnull(cordl.sign, dl.sign) * dl.sysItemValue) itemValueIn
	, sum(case when dl.sign = -1 then 1 else 0 end * dl.itemQuantity) itemQuantityOut
	, sum(case when dl.sign = -1 then -1 else 0 end * isnull(cordl.sign, dl.sign) * dl.sysItemValue) itemValueOut
	, dl.itemID, dl.locationID, dl.itemSerialNumber, warehouseID 
		, cast(d.accDate as date) accDate
	from document.documentLine dl
	inner join document.document d on d.documentID = dl.documentID
	LEFT JOIN document.documentLine cordl ON dl.initialSourceDocumentLineId = cordl.documentLineID and dl.isCorrectiveLine = 1
	where dl.status >= 1
	group by dl.itemID, dl.locationID, dl.itemSerialNumber, d.warehouseID, cast(d.accDate as date)
) calcStB
on sb.itemID = calcStB.itemID and sb.warehouseID = calcStB.warehouseID and isnull(sb.locationID, @guidEmpty) = isnull(calcStB.locationID, @guidEmpty) and isnull(sb.itemSerialNumber, @ges) = isnull(calcStB.itemSerialNumber, @ges)
and sb.stockBalanceDate = calcStb.accDate
where isnull(sb.quantityIn, -1) != isnull(calcStB.itemQuantityIn, -1)
or isnull(sb.quantityOut, -1) != isnull(calcStB.itemQuantityOut, -1)
or isnull(sb.valueIn, -1) != isnull(calcStB.itemValueIn, -1)
or isnull(sb.valueOut, -1) != isnull(calcStB.itemvalueOut, -1)
order by sb.stockBalanceDate desc

update sb set sb.valuein = calcStB.itemvaluein, sb.valueOut = calcStB.itemvalueOut
, sb.quantityIN = calcStB.itemQuantityIn, sb.quantityOUT = calcStB.itemQuantityOut
from wms.stockBalanceDate sb
full join
(
	select 
	sum(case when dl.sign = 1 then 1 else 0 end * dl.itemQuantity) itemQuantityIn
	, sum(case when dl.sign = 1 then 1 else 0 end * isnull(cordl.sign, dl.sign) * dl.sysItemValue) itemValueIn
	, sum(case when dl.sign = -1 then 1 else 0 end * dl.itemQuantity) itemQuantityOut
	, sum(case when dl.sign = -1 then -1 else 0 end * isnull(cordl.sign, dl.sign) * dl.sysItemValue) itemValueOut
	, dl.itemID, dl.locationID, dl.itemSerialNumber, warehouseID 
		, cast(d.accDate as date) accDate
	from document.documentLine dl
	inner join document.document d on d.documentID = dl.documentID
	LEFT JOIN document.documentLine cordl ON dl.initialSourceDocumentLineId = cordl.documentLineID and dl.isCorrectiveLine = 1
	where dl.status >= 1
	group by dl.itemID, dl.locationID, dl.itemSerialNumber, d.warehouseID, cast(d.accDate as date)
) calcStB
on sb.itemID = calcStB.itemID and sb.warehouseID = calcStB.warehouseID and isnull(sb.locationID, @guidEmpty) = isnull(calcStB.locationID, @guidEmpty) and isnull(sb.itemSerialNumber, @ges) = isnull(calcStB.itemSerialNumber, @ges)
and sb.stockBalanceDate = calcStb.accDate
where isnull(sb.quantityIn, -1) != isnull(calcStB.itemQuantityIn, -1)
or isnull(sb.quantityOut, -1) != isnull(calcStB.itemQuantityOut, -1)
or isnull(sb.valueIn, -1) != isnull(calcStB.itemValueIn, -1)
or isnull(sb.valueOut, -1) != isnull(calcStB.itemvalueOut, -1)

insert into wms.stockBalanceDate
select NEWID(), accDate, calcStB.itemID, i.itemCode, calcStB.warehouseID, calcStB.itemQuantityIn, calcStB.itemValueIn, calcStB.itemQuantityOut, calcStB.itemValueOut, calcStB.itemSerialNumber, calcStB.locationID
from wms.stockBalanceDate sb
full join
(
	select 
	sum(case when dl.sign = 1 then 1 else 0 end * dl.itemQuantity) itemQuantityIn
	, sum(case when dl.sign = 1 then 1 else 0 end * isnull(cordl.sign, dl.sign) * dl.sysItemValue) itemValueIn
	, sum(case when dl.sign = -1 then 1 else 0 end * dl.itemQuantity) itemQuantityOut
	, sum(case when dl.sign = -1 then -1 else 0 end * isnull(cordl.sign, dl.sign) * dl.sysItemValue) itemValueOut
	, dl.itemID, dl.locationID, dl.itemSerialNumber, warehouseID 
	, cast(d.accDate as date) accDate
	from document.documentLine dl
	inner join document.document d on d.documentID = dl.documentID
	LEFT JOIN document.documentLine cordl ON dl.initialSourceDocumentLineId = cordl.documentLineID and dl.isCorrectiveLine = 1
	where dl.status >= 1
	group by dl.itemID, dl.locationID, dl.itemSerialNumber, d.warehouseID, cast(d.accDate as date)
) calcStB
on sb.itemID = calcStB.itemID and sb.warehouseID = calcStB.warehouseID and isnull(sb.locationID, @guidEmpty) = isnull(calcStB.locationID, @guidEmpty) and isnull(sb.itemSerialNumber, @ges) = isnull(calcStB.itemSerialNumber, @ges)
and sb.stockBalanceDate = calcStb.accDate
left join wms.item i on i.itemID = calcStB.itemID
where sb.itemID is null and calcStB.itemID is not null


select  sb.valuein, calcStB.itemvaluein, sb.valueOut, calcStB.itemvalueOut
from wms.stockBalanceDate sb
full join
(
	select 
	sum(case when dl.sign = 1 then 1 else 0 end * dl.itemQuantity) itemQuantityIn
	, sum(case when dl.sign = 1 then 1 else 0 end * isnull(cordl.sign, dl.sign) * dl.sysItemValue) itemValueIn
	, sum(case when dl.sign = -1 then 1 else 0 end * dl.itemQuantity) itemQuantityOut
	, sum(case when dl.sign = -1 then -1 else 0 end * isnull(cordl.sign, dl.sign) * dl.sysItemValue) itemValueOut
	, dl.itemID, dl.locationID, dl.itemSerialNumber, warehouseID 
		, cast(d.accDate as date) accDate
	from document.documentLine dl
	inner join document.document d on d.documentID = dl.documentID
	LEFT JOIN document.documentLine cordl ON dl.initialSourceDocumentLineId = cordl.documentLineID and dl.isCorrectiveLine = 1
	where dl.status >= 1
	group by dl.itemID, dl.locationID, dl.itemSerialNumber, d.warehouseID, cast(d.accDate as date)
) calcStB
on sb.itemID = calcStB.itemID and sb.warehouseID = calcStB.warehouseID and isnull(sb.locationID, @guidEmpty) = isnull(calcStB.locationID, @guidEmpty) and isnull(sb.itemSerialNumber, @ges) = isnull(calcStB.itemSerialNumber, @ges)
and sb.stockBalanceDate = calcStb.accDate
where isnull(sb.quantityIn, -1) != isnull(calcStB.itemQuantityIn, -1)
or isnull(sb.quantityOut, -1) != isnull(calcStB.itemQuantityOut, -1)
or isnull(sb.valueIn, -1) != isnull(calcStB.itemValueIn, -1)
or isnull(sb.valueOut, -1) != isnull(calcStB.itemvalueOut, -1)

rollback tran
