
/*Nie jestem pewien czy to dziala wlasciwie*/
select * from (
	select min(src.addDate) addDate, max(src.outlastAddDate) outlastAddDate, documentID, itemID, documentFullNR, sum(itemValue) itemvalue, sum(sysItemValue) sysitemvalue, sum(src.corrsysitemValue) corrSysItemValue, sum(src.outsysitemValue) outsysitemvalue, sum(src.outCorsysitemValue) outCorSysItemValue
	, sum(sysItemValue) + isnull(sum(src.corrsysitemValue), 0) - isnull(sum(src.outsysitemValue), 0) balance
	from (select dl.* 
		, (select sum(sysItemValue) from document.documentline outdl where outdl.status >= 0 and (outdl.initialSourceDocumentLineId = dl.documentLineID)) corrsysitemValue
		, (select sum(sysItemValue) from document.documentline outdl where outdl.status >= 0 and (outdl.sourceDocumentLineID = dl.documentLineID and outDl.iscorrectiveLine = 0)) outsysitemValue
		, (select max(addDate) from (select dl.addDate from document.documentline outdl where outdl.status >= 0 and (outdl.sourceDocumentLineID = dl.documentLineID and outDl.iscorrectiveLine = 0)) insrc) outlastAddDate
		, (select sum(outCorDl.sysItemValue) from document.documentline outdl inner join document.documentLine outCorDl on outCorDl.status >= 1 and outCorDl.initialSourceDocumentLineId = outDl.documentLineID and outCorDl.itemQuantity != 0 
			where outdl.status >= 0 and (outdl.sourceDocumentLineID = dl.documentLineID and outDl.iscorrectiveLine = 0)) outCorsysitemValue
		from document.documentLine dl) src
	where status >= 1 and sign = 1 and iscorrectiveline = 0
	group by documentID, itemID, documentFullNR, sign, isCorrectiveLine
	having sum(itemquantityleft) = 0
) src where src.balance != 0
order by src.outlastAddDate desc

select count(*) from (
	select documentID, itemID, documentFullNR, sum(itemValue) itemvalue, sum(sysItemValue) sysitemvalue, sum(src.corrsysitemValue) corrSysItemValue, sum(src.outsysitemValue) outsysitemvalue, sum(src.outCorsysitemValue) outCorSysItemValue
	, sum(sysItemValue) + isnull(sum(src.corrsysitemValue), 0) - isnull(sum(src.outsysitemValue), 0) balance
	from (select dl.* 
		, (select sum(sysItemValue) from document.documentline outdl where outdl.status >= 0 and (outdl.initialSourceDocumentLineId = dl.documentLineID)) corrsysitemValue
		, (select sum(sysItemValue) from document.documentline outdl where outdl.status >= 0 and (outdl.sourceDocumentLineID = dl.documentLineID and outDl.iscorrectiveLine = 0)) outsysitemValue
		, (select sum(outCorDl.sysItemValue) from document.documentline outdl inner join document.documentLine outCorDl on outCorDl.status >= 1 and outCorDl.initialSourceDocumentLineId = outDl.documentLineID and outCorDl.itemQuantity != 0 
			where outdl.status >= 0 and (outdl.sourceDocumentLineID = dl.documentLineID and outDl.iscorrectiveLine = 0)) outCorsysitemValue
		from document.documentLine dl) src
	where status >= 1 and sign = 1 and iscorrectiveline = 0
	group by documentID, itemID, documentFullNR, sign, isCorrectiveLine
	having sum(itemquantityleft) = 0
) src where src.balance != 0




/* Wykrywanie zle zbilansowanych przychodow tez to chyba nie dzial prawidlowo*/


begin tran

--select initialIncomeSourceLineID from document.documentLine where documentLineID = '70367C4A-CCAD-E411-9412-D89D6719E0CB'

declare @itemID uniqueidentifier = '2951EA99-D654-4FED-8798-4761AED42EA3'

declare @faultID uniqueidentifier = 'D1914CC1-B2C0-E411-9413-D89D6719E0C9'

select initialIncomeSourceLineID, sum(sign * itemQuantity) itemquantitybalance, sum(signValue * sysItemValue) sysItemValueBalance 
from document.documentLine where itemID = @itemID
--and (initialIncomeSourceLineID is null or initialIncomeSourceLineID = @faultID)
group by initialIncomeSourceLineID
having sum(sign * itemQuantity) = 0 and sum(signValue * sysItemValue) != 0

select dl.addDate addeddate, dl.documentFullNR, sign * itemQuantity, itemPrice,correctedItemPrice, itemValue, dl.sysItemValue
, d.modDate, d.modUsername
, currency, d.currencyExchangeRate, d.currencyExchangeDate
, signValue* sysItemValue, round(itemQuantity * correctedItemPrice, 3)
, * 
from document.documentLine dl inner join document.document d on d.documentID = dl.documentID
where initialIncomeSourceLineID = @faultID order by dl.addDate

select sysItemValue, * from document.documentLine where sourceDocumentLineID = 'B3ABBE4A-D5BC-E411-9413-D89D6719E0C9'
select sysItemValue, * from document.documentLine where sourceDocumentLineID = '975AD29A-B7BD-E411-9413-D89D6719E0C9'
select sysItemValue, * from document.documentLine where sourceDocumentLineID = '985AD29A-B7BD-E411-9413-D89D6719E0C9'

select * from (
	select 
	sum(sysItemValue) k1, sum(src.corrsysitemValue) k2,
	  sum(sysItemValue) + isnull(sum(src.corrsysitemValue), 0) - isnull(sum(src.outsysitemValue), 0) - isnull(sum(src.outCorsysitemValue), 0) + isnull(sum(src.outzwsysitemValue), 0) balance
	, sum(sysItemValue) + isnull(sum(src.corrsysitemValue), 0) - isnull(sum(src.outsysitemValue), 0) - isnull(sum(src.outCorsysitemValueValCorr), 0) balance2
	, sum(src.sysCorrectedItemPrice * src.itemQuantityLeft) sysValLeftCalc
	, case when src.hasCorrQPlus = 1 or src.isPlusQuantityCorrection = 1 then 1 else 0 end plusCorr
	, sum(sysItemValue) + isnull(sum(src.corrsysitemValue), 0) inbalance
	, - isnull(sum(src.outsysitemValue), 0) - isnull(sum(src.outCorsysitemValueValCorr), 0) + isnull(sum(src.outzwsysitemValue), 0) outbalance
	, max(src.outlastAddDate) outlastAddDate, documentID, src.documentOrdinalNumber, src.documentLineID, itemID, documentFullNR, sum(itemValue) itemvalue, sum(sysItemValue) sysitemvalue, sum(src.corrsysitemValue) corrSysItemValue, sum(src.outsysitemValue) outsysitemvalue, sum(src.outCorsysitemValue) outCorSysItemValue
	, sum(outCorsysitemValueValCorr) outCorsysitemValueValCorr, sum(src.outzwsysitemValue) outzwsysitemValue
	, sum(srcInCorrQuantity) srcInCorrQuantity
	, sum(srcInCorrPlusQuantity) srcInCorrPlusQuantity
	, sum(srcOutCorrQuantity) srcOutCorrQuantity
	, sum(srcInCorrValueQuantity) srcInCorrValueQuantity
	, sum(delValueBalance) delValueBalance
	, sum(delQuantityBalance) delQuantityBalance
	, src.sysCorrectedItemPrice
	, src.initialIncomeSourceLineID
	, sum(sysItemValue) - sum(src.outsysitemValue) balance666
	, sum(src.corrsysitemValue) -sum(src.outCorsysitemValueValCorr) balance777
	from (select dl.* 
		,  case when exists(select outdl.documentLineID from document.documentline outdl where outdl.status >= 1 and (outdl.initialSourceDocumentLineId = dl.documentLineID) and outdl.itemQuantity > 0 and outdl.sign = 1) then 1 else 0 end hasCorrQPlus
		,  case when dl.isCorrectiveLine = 1 and dl.sign = 1 and dl.itemQuantity > 0 then 1 else 0 end isPlusQuantityCorrection
		, (select sum(sysItemValue) from document.documentline outdl where outdl.status >= 1 and (outdl.initialSourceDocumentLineId = dl.documentLineID)) corrsysitemValue
		, (select sum(sysItemValue) from document.documentline outdl where outdl.status >= 1 and (outdl.sourceDocumentLineID = dl.documentLineID and outDl.iscorrectiveLine = 0)) outsysitemValue
		, (select max(addDate) from (select dl.addDate from document.documentline outdl where outdl.status >= 1 and (outdl.sourceDocumentLineID = dl.documentLineID and outDl.iscorrectiveLine = 0)) insrc) outlastAddDate
		, (select sum(outCorDl.sysItemValue) from document.documentline outdl inner join document.documentLine outCorDl on outCorDl.status >= 1 and outCorDl.initialSourceDocumentLineId = outDl.documentLineID 
			where outdl.status >= 1 and (outdl.sourceDocumentLineID = dl.documentLineID and outDl.iscorrectiveLine = 0)) outCorsysitemValue
		, (select sum(outCorDl.sysItemValue) from document.documentline outdl inner join document.documentLine outCorDl on outCorDl.status >= 1 and outCorDl.initialSourceDocumentLineId = outDl.documentLineID and outCordl.itemQuantity = 0
			where outdl.status >= 1 and (outdl.sourceDocumentLineID = dl.documentLineID and outDl.iscorrectiveLine = 0)) outCorsysitemValueValCorr
		, (select sum(outCorDl.correctedItemValue) from document.documentline outdl inner join document.documentLine outCorDl on outCorDl.status >= 1 and outCorDl.sourceDocumentLineID = outDl.documentLineID and outCorDl.sign = 1 and outCorDl.isCorrectiveLine = 0
			where outdl.status >= 1 and (outdl.sourceDocumentLineID = dl.documentLineID and outDl.iscorrectiveLine = 0)) outzwsysitemValue
		, (select sum(idl.correctedItemQuantity) from document.documentline outdl inner join document.documentLine idl on idl.status >= 1 and idl.documentLineID = outdl.sourceDocumentLineID and idl.sign = 1 and idl.itemQuantity > 0
			where outdl.documentLineID = dl.sourceDocumentLineID and outdl.status >= 1 and outdl.isCorrectiveLine = 0 and outdl.sign = -1) srcInCorrQuantity
		, (select sum(idl.correctedItemQuantity) from document.documentline outdl inner join document.documentLine idl on idl.status >= 1 and idl.documentLineID = outdl.sourceDocumentLineID and idl.sign = 1 and idl.itemQuantity > 0 and idl.isCorrectiveLine = 1
			where outdl.documentLineID = dl.sourceDocumentLineID and outdl.status >= 1 and outdl.isCorrectiveLine = 0 and outdl.sign = -1) srcInCorrPlusQuantity
		, (
			select sum(icdl.sysItemValue) from document.documentline outdl 
			INNER JOIN document.documentLine zwdl on zwdl.sourceDocumentLineID = outdl.documentLineID 
			INNER JOIN document.documentLine idl on idl.status >= 1 and idl.documentLineID = outdl.sourceDocumentLineID
			INNER JOIN document.documentLine icdl on icdl.status >= 1 and icdl.initialSourceDocumentLineId = idl.documentLineID and icdl.itemQuantity = 0 and icdl.isCorrectiveLine = 1
			where zwdl.documentLineID = dl.documentLineID and outdl.status >= 1 and outdl.isCorrectiveLine = 0 and outdl.sign = -1
		) srcInCorrValueQuantity
		, (select sum(alldl.sysItemValue * alldl.signValue) from document.documentLine alldl
			where alldl.initialIncomeSourceLineID = dl.initialIncomeSourceLineID and alldl.status >= 1
		) delValueBalance
		, (
			select sum(indl.sign * indl.itemQuantityLeft) from document.documentline indl where indl.initialIncomeSourceLineID = dl.initialIncomeSourceLineID and indl.sign = 1 and dl.itemQuantity != 0
		) delQuantityBalance
		, (select sum(routDl.correctedItemQuantity) from document.documentline outdl 
		inner join document.documentLine routdl on routdl.documentID = outdl.documentID and routdl.documentOrdinalNumber = outdl.documentOrdinalNumber
		where outdl.status >= 1 and (outdl.documentLineID = dl.sourceDocumentLineID)) srcOutCorrQuantity
		from document.documentLine dl) src
	where status >= 1 and sign = 1 and iscorrectiveline = 0
	group by documentID, documentOrdinalNumber, src.documentLineID, itemID, documentFullNR, sign, isCorrectiveLine, src.hasCorrQPlus, src.isPlusQuantityCorrection, src.sysCorrectedItemPrice, src.initialIncomeSourceLineID
	having sum(itemquantityleft) = 0 and not((src.hasCorrQPlus = 1 or src.isPlusQuantityCorrection = 1) and sum(src.sysCorrectedItemPrice * src.itemQuantityLeft) = 0)
) src where src.balance != 0 and isnull(src.balance2, 0) != 0 and isnull(src.srcInCorrQuantity, -1) != 0 and (src.srcInCorrQuantity is null or src.corrSysItemValue is null) and src.delValueBalance != 0
and not(isnull(src.balance2, 0) = 0 and src.corrSysItemValue is null)
and not (isnull(src.balance2, 0) = 0 and isnull(src.inbalance, 0) = 0 and ISNULL(src.outbalance, 0) = 0)
and not ((src.documentFullNR like 'ZW%' or src.documentFullNR like 'SZW%') and (src.srcOutCorrQuantity = 0 and src.sysitemvalue = src.outsysitemvalue or src.balance + src.srcInCorrValueQuantity = 0)) --pomijamy ZW które są do w pełni skorygowanych ilościowo RW-P
and round(src.delQuantityBalance * src.sysCorrectedItemPrice, 2) != src.delValueBalance
--or src.delQuantityBalance = 0 and round(src.delQuantityBalance * src.sysCorrectedItemPrice, 2) != src.delValueBalance
and src.documentID not in ('36F032DF-AA31-4663-AF1C-4CAD9C46D19A', '8F6A689A-37A2-E411-9411-D89D6719E0CA', '025EC31E-86B2-E411-9412-D89D6719E0CB')
order by src.outlastAddDate asc


rollback tran
