--Drop Table ##AcceptanceActPaymentItems
--Drop Table ##Orders

Select 
	Row_Number() Over(Partition By Package.ID , P.ID Order By AI.DateCreated, AI.PayableAmount) As RowNumber,
	Package.ID As PackageID, P.ID As PersonID, AI.ID As AcceptanceActPaymentItemID,

	P.PersonalID As [პირადი #], P.FirstName As [სახელი], P.LastName As [გვარი], P.RegisteredAddress As [იურიდიული მისამართი], 
	(Case When P.Gender = 1 Then N'მამრობითი'
		  When P.Gender = 2 Then N'მდედრობითი'
	 End) As [სქესი]
	,AI.RequestedAmount As [ჯამური თანხა], AI.PayableAmount As [გადარიცხული თანხა], AI.IncomeTax As [საშემოსავლოს თანხა]
	,A.ReportingYear As [საანგარიშგებო წელი], M.Name As [საანგარიშგებო თვე]
	,Package.TreasuryPackageNumber As [პაკეტის # ხაზინაში], Package.TreasuryRegistrationDate As [პაკეტის ხაზინაში რეგისტრაციის თარიღი]

	Into ##AcceptanceActPaymentItems
From dbo.BL_Packages Package
Join dbo.BL_AcceptanceAct A On A.ID = Package.AcceptanceActID
Join dbo.BL_AcceptanceActPaymentItems AI On AI.AcceptanceActID = A.ID
Join dbo.BL_ContractPersons CP ON CP.ID = AI.ContractPersonID
Join dbo.BL_Persons P On P.ID = CP.PersonID
Join dbo.BL_SheduleMonth M On A.ReportingMonth = M.OrderIndex
--Left Join BL_PaymentOrder POrder On POrder.PackageID = Package.ID And P.PersonalID = POrder.ReceiverTax And CP.AccountNumber = POrder.ReceiverBankAccount Collate SQL_Latin1_General_CP1_CI_AS
Where 
	A.StatusID != 'AF9EA4FD-031F-4922-84F1-7778889D91A2' --გაუქმებული
	And Package.StatusID = '714A8C45-AF4C-47D8-AF48-4B63195466F6' --აქტიური
	And AI.DateDeleted Is Null
	And AI.IsPayable = 1
Order By Package.TreasuryRegistrationDate

--Select * From dbo.BL_AcceptanceActStatuses

Select 
	Row_Number() Over(Partition By POrder.PackageID, POrder.ReceiverTax Order By POrder.DateCreated, POrder.Amount) As RowNumber,
	POrder.ReceiverTax, POrder.ReceiverName, POrder.Amount, POrder.PackageID, S.Name OrderStatus, TS.Name OrderTreasuryStatus, POrder.AcceptanceActPaymentItemID, POrder.ID As PaymentOrderID

	Into ##Orders
From dbo.BL_PaymentOrder POrder
Join dbo.BL_PaymentOrderStatuses S On S.ID = POrder.StatusID
Join dbo.BL_TreasuryPaymentOrderStatuses TS On TS.ID = POrder.TreasuryStatusID
Where POrder.PackageID IS Not Null
	And S.ID = 'F8C582E7-F265-4E45-A00E-F8F8DFF06E1E'  --აქტიური'
	And TS.ID Not In ('8AA4C8B8-61D0-4D74-BFBC-700D2CCEAB2B', --წაშლილი
				  '293EEC52-58F5-43C5-BD82-90AD79FCE4EF') --გაუქმებული
	And POrder.IsIncomeTax = 0

--Select * From dbo.BL_PaymentOrderStatuses
--Select * From dbo.BL_TreasuryPaymentOrderStatuses

Select * From ##Orders Where ReceiverTax = '40001007486' And PackageID = 'DCD2463B-0B8E-42D4-8C55-65BFC52F7DE6'
Select * From ##AcceptanceActPaymentItems Where [პირადი #] = '40001007486' And PackageID = 'DCD2463B-0B8E-42D4-8C55-65BFC52F7DE6'

--B09572AF-D5CB-4896-B829-A2616AD328F0
--773F682B-0297-43B5-8599-A4D03FA1A667

Declare @CurrentDate DAteTime
Set @CurrentDate = '2015-02-06 15:00:00.000'

Begin Tran

Update P
Set P.AcceptanceActPaymentItemID = Item.AcceptanceActPaymentItemID,
	P.DateChanged = @CurrentDate
--Select * 
From dbo.BL_PaymentOrder P
Join ##Orders POrder On P.ID = POrder.PaymentOrderID
Join ##AcceptanceActPaymentItems Item On Item.PackageID = POrder.PackageID And Item.[პირადი #] = POrder.ReceiverTax 
										And Item.[გადარიცხული თანხა] = POrder.Amount And Item.RowNumber = POrder.RowNumber
Where P.AcceptanceActPaymentItemID Is Null

Rollback
Commit