
begin tran

/*Usuni�cie obrotu zwi�zanego z konkretnym przyj�ciem o ile wszystkie dokumenty s� skorygowane do 0 oraz nie ma jakich� przesuni�c oraz 
dokumenty s� wycofane z FK*/

declare @documentID uniqueidentifier = '269e4858-6e6f-e611-80c5-9c8e994dc647'
declare @guidEmpty uniqueidentifier = newId()
declare @ges char(36) = cast(@guidEmpty as char(36))

declare @balanceByLine table(signedValue decimal(18,2), signedQuantity decimal(18,3), initialIncomeSourceLineID uniqueidentifier)

;with ids as (
	select initialIncomeSourceLineID from document.documentLine where documentID = @documentID and [status]= 1
)
insert into @balanceByLine
select sum(dl.sysItemValue * dl.signValue), sum(dl.itemQuantity * dl.sign), dl.initialIncomeSourceLineID from document.documentLine dl
inner join ids i on i.initialIncomeSourceLineID = dl.initialIncomeSourceLineID
and dl.[status] >= 1
group by dl.initialIncomeSourceLineID

if not exists(
	select 1 from @balanceByLine where signedValue != 0 or signedQuantity != 0 or signedValue is null or signedQuantity is null
)
begin
	declare @validation table (documentFullNR varchar(max), financialSystemExport date, outcomeShiftID uniqueidentifier)


	;with ids as (
		select initialIncomeSourceLineID from document.documentLine where documentID = @documentID and [status]= 1
	)
	insert into @validation
	select distinct d.documentFullNR, d.financialSystemExport, d.outcomeShiftID from document.documentLine dl
	inner join ids i on i.initialIncomeSourceLineID = dl.initialIncomeSourceLineID
	inner join document.document d on d.documentID = dl.documentID
	and dl.[status] >= 1

	if not exists(select 1 from @validation where financialSystemExport is not null or outcomeShiftID is not null) begin
		select 'OK'

		declare @initialIncomeSourceLineIDTable table(initialIncomeSourceLineID uniqueidentifier)
		insert into @initialIncomeSourceLineIDTable
		select initialIncomeSourceLineID from document.documentLine where documentID = @documentID and [status]= 1

		declare @documentLinesToDelete table(documentLineID uniqueidentifier, documentID uniqueidentifier, itemID uniqueidentifier)

		insert into @documentLinesToDelete
		select dl.documentLineID, dl.documentID, dl.itemID from document.documentLine dl
		inner join @initialIncomeSourceLineIDTable idl on idl.initialIncomeSourceLineID = dl.initialIncomeSourceLineID and dl.status = 1

		update dl set dl.[status] = -1
		from document.documentLine dl
		inner join @documentLinesToDelete src on src.documentLineID = dl.documentLineID

		update d set d.[status] = -1
		from document.document d
		inner join @documentLinesToDelete src on src.documentID = d.documentID

		declare @itemID uniqueidentifier 

		while exists(select 1 from @documentLinesToDelete)
		begin
			select top 1 @itemID = itemID from @documentLinesToDelete
			delete from @documentLinesToDelete where itemID = @itemID

			exec wms.stockBalanceFix @itemID = @itemID, @username = 'sys'
		end

		
		select 'niezgodne stany dzienne'
		declare @dateUntilStockIsByAccDateStr varchar(max) = (select value from configuration.configuration with(nolock) where configurationID = 'instance.document.dateUntilStockIsByAccDateStr')
								if @dateUntilStockIsByAccDateStr is not null and ISDATE(@dateUntilStockIsByAccDateStr)= 1 begin
	select @dateUntilStockIsByAccDateStr = left(@dateUntilStockIsByAccDateStr, 10)
	declare @dateUntilStockIsByAccDate date = convert(date, @dateUntilStockIsByAccDateStr, 102)
end


																																																						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 
	--!UWAGA data
	, case when cast(d.accDate as date) > @dateUntilStockIsByAccDate and cast(d.documentDate as date) <= @dateUntilStockIsByAccDate then cast(d.accDate as date) when @dateUntilStockIsByAccDate is null or cast(d.accDate as date) > @dateUntilStockIsByAccDate then cast(d.documentDate as date) else cast(d.accDate as date) end stockDate
	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
	, case when cast(d.accDate as date) > @dateUntilStockIsByAccDate and cast(d.documentDate as date) <= @dateUntilStockIsByAccDate then cast(d.accDate as date) when @dateUntilStockIsByAccDate is null or cast(d.accDate as date) > @dateUntilStockIsByAccDate then cast(d.documentDate as date) else cast(d.accDate as date) end
) 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.stockDate
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


																																																																				select w.locationCode, 
sum(isnull(calcStB.itemQuantityIn, 0) - isnull(sb.quantityIN, 0))
- sum(isnull(calcStB.itemQuantityOut, 0) - isnull(sb.quantityOUT, 0)) quantityCommitedDiff,

sum(isnull(calcStB.itemvaluein, 0) - isnull(sb.valuein, 0))
- sum(isnull(calcStB.itemvalueOut, 0) - isnull(sb.valueOut, 0)) valueCommitedDiff

from wms.stockBalanceDate sb
inner join wms.item i on i.itemID = sb.itemID
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 
	--!UWAGA data
	, case when cast(d.accDate as date) > @dateUntilStockIsByAccDate and cast(d.documentDate as date) <= @dateUntilStockIsByAccDate then cast(d.accDate as date) when @dateUntilStockIsByAccDate is null or cast(d.accDate as date) > @dateUntilStockIsByAccDate then cast(d.documentDate as date) else cast(d.accDate as date) end stockDate
	from document.documentLine dl
	inner join wms.item i on i.itemID = dl.itemID
	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
	, case when cast(d.accDate as date) > @dateUntilStockIsByAccDate and cast(d.documentDate as date) <= @dateUntilStockIsByAccDate then cast(d.accDate as date) when @dateUntilStockIsByAccDate is null or cast(d.accDate as date) > @dateUntilStockIsByAccDate then cast(d.documentDate as date) else cast(d.accDate as date) end
) 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.stockDate
inner join wms.location w on w.locationID = isnull(sb.warehouseID, calcStb.warehouseID)
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)
group by w.locationCode, isnull(sb.stockBalanceDate, calcStb.stockDate)
order by locationCode

		select 'niezgodne stany'

																										select * 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, 0)
or isnull(sb.value, -1) != isnull(calcStB.itemValue, 0)

	end

end
	;with ids as (
		select initialIncomeSourceLineID from document.documentLine where documentID = @documentID and [status]= 1
	)
	select distinct d.documentFullNR, d.financialSystemExport, d.outcomeShiftID from document.documentLine dl
	inner join ids i on i.initialIncomeSourceLineID = dl.initialIncomeSourceLineID
	inner join document.document d on d.documentID = dl.documentID
	and dl.[status] >= 1

rollback tran