Фискализация

-- ??????? ?????

use kv2022

-- ??????? - ?????? ??????? ????, ??????? ??? ???????? ??????

-- ??????? - ??????, ??????? ????????? ? ???? ?? ??????? ???????????, ????????? ?? ?????????. ???????? ?????? ??????? ???? ? ???????? ?????? ????? ????????????? ???? ? ????? ?????????? ???????????

-- ??????? ????????? - ????? ????? ???? ?? ????????? ????????? ?? ????????? ??. 

declare 

@kod_adres bigint

, @corrected_kod_adres bigint

, @previous_data_gash smalldatetime

, @vid smallint

, @no_kv bigint

, @corrected_no_kv bigint

, @final_transaction bit

, @month tinyint

, @external_id uniqueidentifier

, @external_id_reserv uniqueidentifier

, @cMonth tinyint

, @cYear smallint

, @return varchar(100)

, @Error varchar(150)

Select @cMonth=month, @cYear=year From spr..uf_GetCurrentPeriod()

Declare tCursor Cursor for

Select  [kod_adres]

      ,[corrected_kod_adres]

      ,[no_kv]

      ,[corrected_no_kv]

      ,[month]

      ,[vid]

      ,[final_transaction]

      ,[previous_data_gash]

From sbrf_no_kv_refunds 

Where is_taken = 0 

--And previous_data_gash  is not null

--And Year(previous_data_gash)>=2022

--AND month=5

--And corrected_kod_adres<>25025005005

--

And no_kv not in (

290000105 -- ???????? ???, ??? ???? ????? ??? ???? ????, ?? ???? ??? ???? - ????????, ??? ??????? ??? ??????? ? post_t

,1170051906-- ? ????? ???? ??? ???????, ???? ??? ????

,1170048172 ) -- ? ????? ???? ??? ???????, ???? ??? ????

Open tCursor

Fetch Next From tCursor Into

@kod_adres

,@corrected_kod_adres

,@no_kv

,@corrected_no_kv

,@month

,@vid

,@final_transaction

,@previous_data_gash

While @@FETCH_STATUS = 0 

Begin

	Select @external_id = Null

	-- ???? ??? ?? 2022 ???, ?? ??????? ?? ?? ?????? ?????, ?.?. ????? ?? ??????????? ???? ????. ????? ??????? ????? ???.

	If Year(@previous_data_gash)<=2021 Goto Create_Body

	-- ?????? ????? ????????, ??? ?? ???? ??? ??????????????? ? ???, ??????? ???? ??? ???? ???????. 

	-- Step1 Begin>> 

	-- ????  *??????? ?????????*

	If (@previous_data_gash is Null)

	Begin

		-- ? ?????? ?????? ??? ??????? ??????????? ??????? ?? ??????????, ?.?. ?? ?? ???????? ???

		-- ????????????? ??????????? ??? ???????? ??????????

		-- ????? ??????? ??? ????

		-- 1 ?? ??????? ??????? ??????? ????? ??? ? payment_details ?? ???? ????? ?? ????, ??????? ???? ? post_t

		Select @external_id = NewId()

		Insert [172.16.1.150].fz54_details.dbo.link_storage

		Select @no_kv, @external_id, @kod_adres,null

		Insert [172.16.1.150].fz54_details.dbo.payment_details

		Select 

		@external_id external_id/*NEWID()*/

		,ctdtl.kod_adres

		,null

		,null

		,null

		,null

		,tPhone.phone phone

		,@cYear --ctdtl.year

		,ctdtl.mes

		,null date_operation/*???? ????????*/

		,prvds.id_provider id_company /*???????????-??????????? 1048*/

		,srvs.id_service as id_item

		,Round(Summa*-0.01,2) price-- ????????? ??????/??????

		,1 quantity-- ??????????

		,Round(Summa*-0.01,2) summa-- ?????

		,1 payment_method -- 1214 ??????? ??????? ??????? - ?????? ??????

		,4 payment_object -- ???-?? ?? ?????? ????? ??? 1212 

		-- ??? ??????????? ?????? ??? ??? ??????? ??????

		,IsNull(_agnt.vat,1) vat -- ?????? ??? -- ??? ????? ?????????-- Prokopenko 10/06 ??? ????,?????????? ??????????? ????? ???????? ???20 ? ???? ????????? ????

		, 5 as payments -- ??? ????? 5 - ???? ????? ?????? (????????? ??????????????)

		,Case When agnts.id_agent is not null Then 'another' Else null End agent_type -- ??? ??????

		,isnull(agnts.id_agent,1) supplier_info  -- ??? ??????????, ???? ???????? ??? ??????. ???? null, ?? ????????? ???

		,1 -- ???????

		,Null

		From 

		(Select *, @cMonth mes From post_t Where no_kv = @no_kv and kod_adres = @kod_adres

		Union all

		Select * From post_a Where no_kv = @no_kv and kod_adres = @kod_adres) ctdtl

		Left Join [172.16.1.150].fz54.dbo.vServices srvs On srvs.id_service=ctdtl.kod_uslugi

		Left Join [172.16.1.150].fz54.dbo.providers_to_kvar prvds on prvds.kod_postav=1--??? ?????? ???

		Left Join [172.16.1.150].fz54.dbo.agents_to_kvar agnts on agnts.kod_postav=ctdtl.kod_postav

		Left Join (

		Select * From 

		(

			Select Row_Number() over (PARTITION BY kod_adres Order by stel) as rank -- #ChangeContact08032022 

				,kod_adres

					,'+7'+Replace(Replace(Replace(stel,'(',''),')',''),'-','') phone

						From spr.dbo.contact 

							where stel like '(9%'

		) Tstel

		Where rank=1 

		) tPhone On ctdtl.kod_adres=tPhone.kod_adres

		-- ????? ??? ????????? ?????? ???

		Left join [172.16.1.150].fz54.dbo.agents _agnt On _agnt.id_agent=agnts.id_agent

		-- ?? ??????????? ?????????? ????? ??????? ???? ??????? ?? ??? #Stp2

	End

	-- ???????? ? ???????? ?????

	If (@previous_data_gash is Not Null) and (Year(@previous_data_gash)>=2022)

	Begin

		If exists (Select * From [172.16.1.150].fz54_details.dbo.link_storage Where no_kv = @no_kv And payer_id = @kod_adres)

			Begin

				Select 'cntr', @no_kv

				Select @external_id = NewId()

			-- ???? ??, ?? ?????????? ??? ? ????????, ??? ???? 

			-- Step 1.1 Begin>>

			Select @external_id_reserv = external_id

			From [172.16.1.150].fz54_details.dbo.link_storage

			Where no_kv = @no_kv 

			And refund_number is null

			And payer_id = @kod_adres

			--refunds

			-- ?.?. ?????? ?????????????? ?????????? ??????, ??? ??????????? ??????? ????? ?????? ? ? 

			Insert [172.16.1.150].fz54_details.dbo.refunds

			Select  

			   @external_id

			  ,[payer_id]

			  ,[timestamp]

			  ,[operation_type]

			  ,[operation_number]

			  ,[rollback_number]

			  ,[phone]

			  ,[year]

			  ,[month]

			  ,[date_operation]

			  ,[id_company]

			  ,[id_item]

			  ,[price]

			  ,[quantity]

			  ,[summa]

			  ,[payment_method] -- ??? ?????????, ??? ????

			  ,[payment_object]

			  ,[vat]

			  ,5 -- ??? ????????????? "???? ????? ??????" id = 5, name = 4

			  ,[agent_type]

			  ,[supplier_info]

			  ,1 is_refund

			  ,0 is_taken

			  ,6 ErrorCode

			  ,1 PayOnPrpay

			  ,1 KvOnPrpay

			From [172.16.1.150].fz54_details.dbo.payment_details

			Where external_id = @external_id_reserv

			--payment_details

			Insert [172.16.1.150].fz54_details.dbo.payment_details

			Select  

			   @external_id

			  ,[payer_id]

			  ,[timestamp]

			  ,[operation_type]

			  ,[operation_number]

			  ,[rollback_number]

			  ,[phone]

			  ,[year]

			  ,[month]

			  ,[date_operation]

			  ,[id_company]

			  ,[id_item]

			  ,[price]

			  ,[quantity]

			  ,[summa]

			  ,[payment_method] -- ??? ?????????, ??? ????

			  ,[payment_object]

			  ,[vat]

			  ,5 -- ??? ????????????? "???? ????? ??????" id = 5, name = 4

			  ,[agent_type]

			  ,[supplier_info]

			  ,1 is_refund

			  ,Null is_taken

			From [172.16.1.150].fz54_details.dbo.payment_details

			Where external_id = @external_id_reserv

			-- ????????? ?????? ?? ??????? ? ???????? ?????????

			Update [172.16.1.150].fz54_details.dbo.link_storage

			Set   refund_number = @external_id

			Where external_id = @external_id_reserv

			-- ??????????? ????????? ??????? ????? ????????? ????, ????? ????? ????? ???? ?? ??????? ??? ???????

			Insert [172.16.1.150].fz54_details.dbo.link_storage 

			Select @no_kv, @external_id, @kod_adres, null

			-- Step 1.1 End<<

			End

		Else

			Begin

			-- ???? ???, ?? ?????? ??? ? ????? ????, ??? ???????? ??? ???????? ?????? ????

			-- Step 1.2 Begin>>

			  If exists (Select * From [172.16.1.150].fz54_details.dbo.prepayment_details Where no_kv = @no_kv And payer_id = @kod_adres)

				  Begin

					Select 'ulus', @no_kv

					--prepayment_details

					-- ?? ??????? ????? ??? ? ????? external_id

					exec [172.16.1.150].fz54_details.dbo.usp_construct_prepayment @no_kv,5,1,0,'',@kod_adres

					-- ??????? ???????? ????? ???? ? ??????? ?????????. ???-?? ?? ???????? ?????? ??? ??? ? ????? ???????

					Insert [172.16.1.150].fz54_details.dbo.refunds

					Select *

					,6 ErrorCode

					,1 PayOnPrpay

					,1 KvOnPrpay

					From [172.16.1.150].fz54_details.dbo.payment_details

					Where external_id = (Select external_id From [172.16.1.150].fz54_details.dbo.link_storage Where no_kv=@no_kv And refund_number is null And payer_id = @kod_adres)

					-- ??????? ????????????? ????? ????

					Select @external_id = external_id From [172.16.1.150].fz54_details.dbo.link_storage Where no_kv=@no_kv And refund_number is null And payer_id = @kod_adres

				  End

			-- Step 1.2 End<<

			  Else

				-- ???? ? ????? ???, ?? ????????????? ??? ?? ??? ??????, ??????? ???? ? ??, ? ??????? ??????? "?????? ??????" ? ??????? ???????

				-- ????? ???????? ?????????? ????????? ?????? ??? ????? ????

				-- Step 1.3 Begin>>

				Begin

					Select '????? ?????!', @no_kv

						Close tCursor

							Deallocate tCursor

					Return

				End

			-- Step 1.3 End<<				

			End

	End

	-- Step1 End<< 

	-- ????? ????, ??? ??????? ???????????, ????? ??????? ??? ???????

	-- ??? ????? ???? ??? ????????, ???? ?????? ????? ????? ?? ??????, ?? ????? ???????????? ????????? usp_calculate_prepayment_cheking

	-- ???? ??????? usp_calculate_prepayment_cheking_current

	-- ?? ???? ??? ????????? ?????? ?? ??, ??? ? ??????? ????????? usp_calculate_prepayment, ?????? ???????? ? ????????? post_a(t)

	--#Stp2

	Create_Body:

	If @month=@cMonth

	Begin

		-- ??? 1. ????????? ?????? ????

		exec [172.16.1.150].fz54_details.dbo.usp_calculate_prepayment_cheking_current @month, @cYear, @corrected_no_kv,@return Output, @corrected_kod_adres

		Select '??????:'+@return

		-- ??? 2. ????????? ???

			If (Select PATINDEX('%?????%', @return))>0

			Begin

				exec [172.16.1.150].fz54_details.dbo.usp_construct_prepayment @corrected_no_kv, 5, 0, 0, @return Output, @corrected_kod_adres

					Select '????:'+@return

			End

		Select @Error=''

		exec usp_fz54_CheckOperation @no_kv, @corrected_no_kv, @kod_adres, @corrected_kod_adres, @previous_data_gash, @Error Output

		If @Error in ('001', '002', '003')

		Begin

			Update sbrf_no_kv_refunds Set is_taken = 1 Where no_kv=@no_kv And corrected_no_kv = @corrected_no_kv And is_taken = 0

			If @external_id is not Null

			Begin

				Update [172.16.1.150].fz54_details.dbo.payment_details

					Set is_taken = 0

						Where external_id = @external_id

			End

			-- ????????? ???????? is_taken ? ???????? 0

			Select @external_id = external_id

				From [172.16.1.150].fz54_details.dbo.link_storage

					Where no_kv = @corrected_no_kv And payer_id = @corrected_kod_adres and refund_number is null

			Update [172.16.1.150].fz54_details.dbo.payment_details

					Set is_taken = 0

						Where external_id = @external_id

        End

		Else

		Begin

			Select @Error

			-- rollback

			return

		End

	End

	Else

	Begin

		-- ??? 1. ????????? ?????? ????

		exec [172.16.1.150].fz54_details.dbo.usp_calculate_prepayment_cheking @month, @cYear, @corrected_no_kv, @return Output,@corrected_kod_adres

		Select '??????:'+@return

		-- ??? 2. ????????? ???

		If (Select PATINDEX('%?????%', @return))>0

		Begin

			exec [172.16.1.150].fz54_details.dbo.usp_construct_prepayment @corrected_no_kv, 5, 0, 0, @return Output, @corrected_kod_adres

				Select '????:'+@return

		End

		Select @Error=''

		exec usp_fz54_CheckOperation @no_kv, @corrected_no_kv, @kod_adres, @corrected_kod_adres, @previous_data_gash, @Error Output

		If @Error in ('001', '002', '003')

		Begin

			Update sbrf_no_kv_refunds Set is_taken = 1 Where no_kv=@no_kv And corrected_no_kv = @corrected_no_kv And is_taken = 0

			-- 

			If @external_id is not Null

			Begin

				Update [172.16.1.150].fz54_details.dbo.payment_details

					Set is_taken = 0

						Where external_id = @external_id

			End

			-- ????????? ???????? is_taken ? ???????? 0

			Select @external_id = external_id

				From [172.16.1.150].fz54_details.dbo.link_storage

					Where no_kv = @corrected_no_kv And payer_id = @corrected_kod_adres

			Update [172.16.1.150].fz54_details.dbo.payment_details

					Set is_taken = 0

						Where external_id = @external_id

        End

		Else

		Begin

			Select @Error

			-- rollback

			return

		End

	End

Fetch Next From tCursor Into

	@kod_adres

	,@corrected_kod_adres

	,@no_kv

	,@corrected_no_kv

	,@month

	,@vid

	,@final_transaction

	,@previous_data_gash

End

Close tCursor

Deallocate tCursor

--Select * From [172.16.1.150].fz54_details.dbo.link_storage Where no_kv in (1170008793, 1170043491)

-- ????? ?????? ??????? ?????????. ? ???? ?????? ? ??? ????? ?????? ????? ?????? ??????