if exists(select * from systable t join syscolumn c on t.table_id = c.table_id where
    LCase(t.table_name) = LCase('Currencies') and LCase(c.column_name) = LCase('ExchangeRate') and c.width = 12 and c.scale = 4) then
  alter table Currencies
  modify ExchangeRate numeric(12,6) not null
end if

GO

if not exists(select * from Registers where RegisterName='DateChangeDbBGNtoEUR') then
  insert into Registers(RegisterName, IntStojnost, Komentar ) 
    values('DateChangeDbBGNtoEUR', 46023, 'Дата за промяна на базата от лева към евро. 01.01.2026 => 46023')
end if 

GO

IF (EXISTS(Select * from sys.sysprocedure where LCase(proc_name) = LCase('ChangeBGNtoEUR')))
THEN Drop procedure ChangeBGNtoEUR
END IF

GO

create procedure DBA.ChangeBGNtoEUR()
begin
  declare @LevToEUR numeric(12,6);
  declare @EURtoUSD numeric(12,6);
  declare @EURToLev numeric(12,6);
  declare @Cnt integer;
  declare @CurrentCnt integer;
  declare @TotalSum numeric(12,2);
  declare @TotalWithoutVAT numeric(12,6);
  set @EURToLev = .511292; --0.51129188
  set @EURtoUSD = .86076158; --1.162107;
  set @LevToEUR = 1.95583;
  if(not exists(select * from Currencies where Symbol = 'EUR' and KodCurrency = 1))
    and(exists(select * from Registers where IntStojnost > 0 and RegisterName = 'DateChangeDbBGNtoEUR')) then
    message 'Start LevToEur - ' || current timestamp to console;
    call DBA.BackUpDb('C:\\ORAK'); --  C:\\ORAK\\Archives
    set option fire_triggers = 'off'; --спиране на тригерите
    begin atomic
	  if VAREXISTS('bvrUserN') = 0 then
		create variable bvrUserN integer
	  end if;
	  set bvrUserN = (select Min(AdminNo) from Admin where AdminNo > 0);
	  if VAREXISTS('bvrErrorMessage') = 0 then
		create variable bvrErrorMessage char(200)
	  end if;
	  set bvrErrorMessage = '';      
	  
	  update Currencies set Description = 'Евро',Symbol = 'EUR',ExchangeRate = 1 where KodCurrency = 1;
      update Currencies set Description = 'Долар',Symbol = 'USD',ExchangeRate = @EURtoUSD where KodCurrency = 2;
      update Currencies set Description = 'Лев',Symbol = 'BGN',ExchangeRate = @EURToLev where KodCurrency = 3;
      set @Cnt = (select count() from Invoices where KodCurrency = 1 and GrandTotal <> 0);
      set @CurrentCnt = 0;
      message '(' || current timestamp || ') LEV TO EUR - START Invoices UPDATE - CNT : ' || @Cnt to console;
      for f as curs scroll cursor for
        select InvoiceNo as @InvNo,
          IsNull(WithoutVAT,0) as @WithoutVAT
 		from Invoices
          where KodCurrency = 1 and GrandTotal <> 0 order by InvoiceNo asc do
        set @CurrentCnt = @CurrentCnt+1;
        if(remainder(@CurrentCnt,100) = 0) then
          message '(' || current timestamp || ') LEV TO EUR - Invoices - ' || @CurrentCnt || ' of ' || @Cnt to console
        end if;
		
        update Articles set Price = Round(Price/@LevToEUR,2) where KodCurrency = 1 and InvoiceNo = @InvNo;
        update Articles set TotalSum = Round(Qty*Price*(1+Discount/100),2),TotalSumInvCurrency = Round(Qty*Price,2) where KodCurrency = 1 and InvoiceNo = @InvNo;
        set @TotalWithoutVAT = IsNull((select Sum(TotalSum) from Articles where InvoiceNo = @InvNo),0);
        set @TotalSum = IsNull((select Sum(TotalSum*(1+abs(VatRate))) from Articles where InvoiceNo = @InvNo),0);
        
		if @WithoutVAT <> 0 then
		  update Invoices set GrandTotal = Round(@TotalSum,2),
            WithoutVAT = Round(@TotalSum,2)
          where KodCurrency = 1 and InvoiceNo = @InvNo;		
		else		
		  update Invoices set GrandTotal = Round(@TotalSum,2),
            TotalNoVAT = @TotalWithoutVAT,
            VAT = Round(@TotalSum-@TotalWithoutVAT,2)
          where KodCurrency = 1 and InvoiceNo = @InvNo;
	    end if;	  
      end for;
	  
	  set @Cnt = (select count() from Invoices where KodCurrency = 3);
      message '(' || current timestamp || ') LEV TO EUR - START Invoices FROM EUR - CNT : ' || @Cnt to console;
	  update Invoices set KodCurrency = 1 where KodCurrency = 3;
	  update Articles set KodCurrency = 1 where KodCurrency = 3;
	  
      update DayRates set ExchangeRate = @EURToLev where KodCurrency = 3;
      update DayRates set ExchangeRate = @EURtoUSD where KodCurrency = 2;
      --update DayRates set KodCurrency = 3 where KodCurrency = 1;
      --update DayRates set KodCurrency = 1 where KodCurrency = 3;
      
	  set @Cnt = (select count() from ResMoney where Currency = 1 and Sum <> 0);
      set @CurrentCnt = 0;
      message '(' || current timestamp || ') LEV TO EUR - START ResMoney UPDATE - CNT : ' || @Cnt to console;
      for f1 as curs1 scroll cursor for
        select Sum as @Sum,DepositNo as @DepositNo,InvoiceNo as @InvNo
          from ResMoney
          where Sum <> 0 and Currency = 1 order by InvoiceNo asc do
        set @CurrentCnt = @CurrentCnt+1;
        if(remainder(@CurrentCnt,100) = 0) then
          message '(' || current timestamp || ') LEV TO EUR - ResMoney - ' || @CurrentCnt || ' of ' || @Cnt to console
        end if;
        --set @TotalSum=(Select IsNull(GrandTotal,0) from Invoices where InvoiceNo=@InvNo);
        update ResMoney set Sum = Round(Sum/@LevToEUR,2)
          where Currency = 1 and Sum = @Sum and DepositNo = @DepositNo and InvoiceNo = @InvNo
      end for;
	  
	  set @Cnt = (select count() from ResMoney where Currency = 3);
      message '(' || current timestamp || ') LEV TO EUR - START ResMoney FROM EUR - CNT : ' || @Cnt to console;
	  update ResMoney set Currency = 1 where Currency = 3;
	  
      set @Cnt = (select count() from Book where KodCurrency = 1 and Price <> 0);
      set @CurrentCnt = 0;
      message '(' || current timestamp || ') LEV TO EUR - START Book UPDATE - CNT : ' || @Cnt to console;
      for f2 as curs2 scroll cursor for
        select RegNum as @RN
          from Book
          where Price <> 0 and KodCurrency = 1 order by RegNum asc do
        set @CurrentCnt = @CurrentCnt+1;
        if(remainder(@CurrentCnt,100) = 0) then
          message '(' || current timestamp || ') LEV TO EUR - Book - ' || @CurrentCnt || ' of ' || @Cnt to console
        end if;
        update Book set Price = Round(Price/@LevToEUR,2) where KodCurrency = 1 and RegNum = @RN
      end for;

	  set @Cnt = (select count() from Book where KodCurrency = 3);
      message '(' || current timestamp || ') LEV TO EUR - START Book FROM EUR - CNT : ' || @Cnt to console;	  
	  update Book set KodCurrency = 1 where KodCurrency = 3;
	  
      set @Cnt = (select count() from BoardPlan where Price <> 0);
      set @CurrentCnt = 0;
      message '(' || current timestamp || ') LEV TO EUR - START BoardPlan UPDATE - CNT : ' || @Cnt to console;
      for f3 as curs3 scroll cursor for
        select N as @BPN
          from BoardPlan
          where IsNull(Price,0) <> 0 order by N asc do
        update BoardPlan set Price = Round(IsNull(Price,0)/@LevToEUR,2) where N = @BPN
      end for;
	  	  
      set @Cnt = (select count() from ServiceCredits where KodCurrency = 1 and SumCredit <> 0);
      set @CurrentCnt = 0;
      message '(' || current timestamp || ') LEV TO EUR - START ServiceCredits UPDATE - CNT : ' || @Cnt to console;
      for f4 as curs4 scroll cursor for
        select CreditNo as @CrNo
          from ServiceCredits
          where KodCurrency = 1 and SumCredit <> 0 order by CreditNo asc do
        set @CurrentCnt = @CurrentCnt+1;
        if(remainder(@CurrentCnt,100) = 0) then
          message '(' || current timestamp || ') LEV TO EUR - ServiceCredits - ' || @CurrentCnt || ' of ' || @Cnt to console
        end if;
        update ServiceCredits set SumCredit = Round(SumCredit/@LevToEUR,2) where KodCurrency = 1 and CreditNo = @CrNo
      end for;

	  set @Cnt = (select count() from ServiceCredits where KodCurrency = 3);
      message '(' || current timestamp || ') LEV TO EUR - START ServiceCredits FROM EUR - CNT : ' || @Cnt to console;		  
	  update ServiceCredits set KodCurrency = 1 where KodCurrency = 3;	  
	  
      set @Cnt = (select count() from PriceList where KodCurrency = 1 and PriceAll <> 0);
      set @CurrentCnt = 0;
      message '(' || current timestamp || ') LEV TO EUR - START PriceList UPDATE - CNT : ' || @Cnt to console;
      for f5 as curs5 scroll cursor for
        select TariffNo as @TN
          from PriceList
          where KodCurrency = 1 and PriceAll <> 0 order by TariffNo asc do
        set @CurrentCnt = @CurrentCnt+1;
        if(remainder(@CurrentCnt,100) = 0) then
          message '(' || current timestamp || ') LEV TO EUR - PriceList - ' || @CurrentCnt || ' of ' || @Cnt to console
        end if;
        update PriceList set PriceAll = Round(PriceAll/@LevToEUR,2) where KodCurrency = 1 and TariffNo = @TN;
      end for;
	  
      set @Cnt = (select count() from PriceList where KodCurrency = 3);
      message '(' || current timestamp || ') LEV TO EUR - START PriceList FROM EUR - CNT : ' || @Cnt to console;
      update PriceList set KodCurrency = 1 where KodCurrency = 3;
      
	  set @Cnt = (select count() from ContractsPriceList where KodCurrency = 1);
      set @CurrentCnt = 0;
      message '(' || current timestamp || ') LEV TO EUR - START ContractsPriceList UPDATE - CNT : ' || @Cnt to console;
      for f15 as curs15 scroll cursor for
        select TariffNo as @TN
          from ContractsPriceList
          where KodCurrency = 1 and PriceAll <> 0 order by TariffNo asc do
        set @CurrentCnt = @CurrentCnt+1;
        if(remainder(@CurrentCnt,10) = 0) then
          message '(' || current timestamp || ') LEV TO EUR - ContractsPriceList - ' || @CurrentCnt || ' of ' || @Cnt to console
        end if;
        update ContractsPriceList set PriceAll = Round(PriceAll/@LevToEUR,2) where KodCurrency = 1 and TariffNo = @TN;
      end for;
      
	  set @Cnt = (select count() from ContractsPriceList where KodCurrency = 3);
      message '(' || current timestamp || ') LEV TO EUR - START ContractsPriceList FROM EUR - CNT : ' || @Cnt to console;
      update ContractsPriceList set KodCurrency = 1 where KodCurrency = 3;
      
	  set @Cnt = (select count() from Services where KodCurrency = 1 and ServicePrice <> 0);
      set @CurrentCnt = 0;
      message '(' || current timestamp || ') LEV TO EUR - START Services UPDATE - CNT : ' || @Cnt to console;
      for f6 as curs6 scroll cursor for
        select ServiceNo as @SNo
          from Services
          where KodCurrency = 1 and ServicePrice <> 0 order by ServiceNo asc do
        set @CurrentCnt = @CurrentCnt+1;
        if(remainder(@CurrentCnt,10) = 0) then
          message '(' || current timestamp || ') LEV TO EUR - Services - ' || @CurrentCnt || ' of ' || @Cnt to console
        end if;
        update Services set ServicePrice = Round(ServicePrice/@LevToEUR,2)
          where KodCurrency = 1 and ServiceNo = @SNo
      end for;
      
	  set @Cnt = (select count() from Services where KodCurrency = 3);
      message '(' || current timestamp || ') LEV TO EUR - START Services FROM EUR - CNT : ' || @Cnt to console;		  
      update Services set KodCurrency = 1 where KodCurrency = 3;	  		  
	  
	  set @Cnt = (select count() from SubsidyNormativ where Normativ <> 0);
      message '(' || current timestamp || ') LEV TO EUR - START SubsidyNormativ FROM EUR - CNT : ' || @Cnt to console;		  
      update SubsidyNormativ set Normativ = Round(Normativ/@LevToEUR,2) where Normativ <> 0;
	  
	  set @Cnt = (select count() from VaucherInvoices where KodCurrency = 1 and GrandTotal <> 0);
      set @CurrentCnt = 0;
      message '(' || current timestamp || ') LEV TO EUR - START VaucherInvoices UPDATE - CNT : ' || @Cnt to console;
      for f9 as curs9 scroll cursor for
        select VaucherInvoiceNo as @InvNo,
		    IsNull(WithoutVAT,0) as @WithoutVAT
          from VaucherInvoices
          where KodCurrency = 1 and GrandTotal <> 0 order by VaucherInvoiceNo asc do
        set @CurrentCnt = @CurrentCnt+1;
        if(remainder(@CurrentCnt,100) = 0) then
          message '(' || current timestamp || ') LEV TO EUR - VaucherInvoices - ' || @CurrentCnt || ' of ' || @Cnt to console
        end if;
		
        update VaucherArticles set Price = Round(Price/@LevToEUR,2) where KodCurrency = 1 and InvoiceNo = @InvNo;
        update VaucherArticles set TotalSum = Round(Qty*Price*(1+Discount/100),2),TotalSumInvCurrency = Round(Qty*Price,2) where Currency = 1 and InvoiceNo = @InvNo;
        set @TotalWithoutVAT = IsNull((select Sum(TotalSum) from VaucherArticles where InvoiceNo = @InvNo),0);
        set @TotalSum = IsNull((select Sum(TotalSum*(1+abs(VatRate))) from VaucherArticles where InvoiceNo = @InvNo),0);
        
	    if @WithoutVAT <> 0 then
		  update VaucherInvoices set GrandTotal = Round(@TotalSum,2),
            WithoutVAT = Round(@TotalSum,2)
          where KodCurrency = 1 and VaucherInvoiceNo = @InvNo;		
		else		
		  update VaucherInvoices set GrandTotal = Round(@TotalSum,2),
            TotalNoVAT = @TotalWithoutVAT,
            VAT = Round(@TotalSum-@TotalWithoutVAT,2)
          where KodCurrency = 1 and VaucherInvoiceNo = @InvNo;
	    end if;	  
      end for;
	 
	  set @Cnt = (select count() from VaucherInvoices where KodCurrency = 3);
      message '(' || current timestamp || ') LEV TO EUR - START VaucherInvoices FROM EUR - CNT : ' || @Cnt to console;		 
	  update VaucherInvoices set KodCurrency = 1 where KodCurrency = 3;	  
	  update VaucherArticles set Currency = 1 where Currency = 3;		  
	  
      set @Cnt = (select count() from Proform where KodCurrency = 1 and GrandTotal <> 0);
      set @CurrentCnt = 0;
      message '(' || current timestamp || ') LEV TO EUR - START Proform UPDATE - CNT : ' || @Cnt to console;
      for f11 as curs11 scroll cursor for
        select ProformNo as @ProNo,
            IsNull(WithoutVAT,0) as @WithoutVAT		
          from Proform
          where KodCurrency = 1 and GrandTotal <> 0 order by ProformNo asc do
        set @CurrentCnt = @CurrentCnt+1;
        if(remainder(@CurrentCnt,100) = 0) then
          message '(' || current timestamp || ') LEV TO EUR - Proform - ' || @CurrentCnt || ' of ' || @Cnt to console
        end if;
        
		update ProArticles set Price = Round(Price/@LevToEUR,2) where KodCurrency = 1 and ProformNo = @ProNo;
        update ProArticles set TotalSum = Round(Qty*Price*(1+Discount/100),2),TotalSumInvCurrency = Round(Qty*Price,2) where KodCurrency = 1 and ProformNo = @ProNo;
        set @TotalWithoutVAT = IsNull((select Sum(TotalSum) from ProArticles where ProformNo = @ProNo),0);
        set @TotalSum = IsNull((select Sum(TotalSum*(1+abs(VatRate))) from ProArticles where ProformNo = @ProNo),0);
        
		if @WithoutVAT <> 0 then
		  update Proform set GrandTotal = Round(@TotalSum,2),
            WithoutVAT = Round(@TotalSum,2)
          where KodCurrency = 1 and ProformNo = @ProNo;
		else
		  update Proform set GrandTotal = Round(@TotalSum,2),
            TotalNoVAT = @TotalWithoutVAT,
            VAT = Round(@TotalSum-@TotalWithoutVAT,2)
          where KodCurrency = 1 and ProformNo = @ProNo;
	    end if;	  
      end for;
	 
	  set @Cnt = (select count() from Proform where KodCurrency = 3);
      message '(' || current timestamp || ') LEV TO EUR - START Proform FROM EUR - CNT : ' || @Cnt to console;		 
	  update Proform set KodCurrency = 1 where KodCurrency = 3;	  
	  update ProArticles set KodCurrency = 1 where KodCurrency = 3;	  	  	   	  
	  
      set @Cnt = (select count() from Guests where(Balance <> 0 or ServiceSum <> 0 or DepositSum <> 0));
      set @CurrentCnt = 0;
      message '(' || current timestamp || ') LEV TO EUR - START Guests UPDATE - CNT : ' || @Cnt to console;
      for f7 as curs7 scroll cursor for
        select GuestNum as @GN
          from Guests
          where(Balance <> 0 or ServiceSum <> 0 or DepositSum <> 0)
          order by GuestNum asc do
        set @CurrentCnt = @CurrentCnt+1;
        if(remainder(@CurrentCnt,100) = 0) then
          message '(' || current timestamp || ') LEV TO EUR - CalcBalance - ' || @CurrentCnt || ' of ' || @Cnt to console
        end if;
        call CalcBalance(@GN)
      end for;
	  
      set @Cnt = (select count() from Contracts where Balance <> 0);
      set @CurrentCnt = 0;
      message '(' || current timestamp || ') LEV TO EUR - START Contracts UPDATE - CNT : ' || @Cnt to console;
      for f10 as curs10 scroll cursor for
        select ContractNo as @ContractNo
          from Contracts
          where Balance <> 0
          order by ContractNo asc do
        set @CurrentCnt = @CurrentCnt+1;
        if(remainder(@CurrentCnt,10) = 0) then
          message '(' || current timestamp || ') LEV TO EUR - CalcBalanceContract - ' || @CurrentCnt || ' of ' || @Cnt to console
        end if;
        call CalcBalanceContract(@ContractNo)
      end for;
      --update VidSmetki set KodCurrency=1 where KodCurrency=3;      
	  update Relations set BGColName = 'Сума EUR' where lcase(TableName) = lcase('v_PayTypes') and lcase(ColName) = lcase('AllSum');
      update Relations set BGColName = 'Цена EUR' where lcase(TableName) = lcase('v_WaitingBills') and lcase(ColName) = lcase('PriceLv')
    end;
    --update Registers set IntStojnost = 0 where RegisterName = 'DateChangeDbBGNtoEUR';
    set option fire_triggers = 'on'; --пускане на тригерите
    message 'Happy New Year - ' || current timestamp to console
  end if;
  update Registers set IntStojnost = 0 where RegisterName = 'DateChangeDbBGNtoEUR'
end

GO