SET XACT_ABORT ON;
if OBJECT_ID ('err_log', 'U') is null
   create table err_log (err_date datetime, num_err int, text_message nvarchar(255), name_proc nvarchar(255), num_line int, kod_state int, level_severity int);
if OBJECT_ID ('in_out_log', 'U') is null
   create table in_out_log (date_in_out datetime, status nvarchar(50), id int, nomer nvarchar(50), tarif nvarchar(50), kol_zak int, kol int, summa money,
          organ nvarchar(50), kontakt nvarchar(50), data datetime, data_enter datetime, data_v datetime, kto nvarchar(50), enter bit, guid uniqueidentifier);      
if OBJECT_ID ('q_log', 'U') is null
   create table q_log (neisp_date datetime, Ch int, nomer nvarchar(50), kol int, id int, data datetime, enter bit, [exit] bit, organ nvarchar(50),
          tarif nvarchar(50), summa money, kol_zak int, kontakt nvarchar(50), Orgid int, status nvarchar(50), data_v datetime, kto nvarchar(50), data_enter datetime);         
if OBJECT_ID ('vyx_log', 'U') is null
   create table vyx_log (date_vyx datetime, ch int, id int, nomer nvarchar(50), tarif nvarchar(50), kol_zak int, kol int, summa money, lex money, 
          organ nvarchar(50), data datetime, data_enter datetime, data_v datetime, kto nvarchar(50), enter bit, kontakt nvarchar(50)); 
if OBJECT_ID ('out_o_log', 'U') is null
   create table out_o_log (out_date datetime, ID int, UserGuid uniqueidentifier, VALUE_41 nvarchar(255), VALUE_43 nvarchar(255),
          VALUE_47 nvarchar(255), VALUE_48 int, VALUE_50 datetime, VALUE_53 datetime, VALUE_54 nvarchar(255), VALUE_60 nvarchar(255));          
while 1=1
begin     
BEGIN TRY
    BEGIN TRANSACTION
       if OBJECT_ID ('tmp3', 'U') is not null drop table tmp3;
       select GETDATE() as date_in_out, 'Въезд' as status, zyivki.id, nomer, tarif, kol_zak, kol, summa, organ, kontakt, data, VALUE_50 as data_enter, data_v, kto,
              enter, UserGuid as guid
          into tmp3   
          from zyivki
          join Users on zyivki.id=Users.ID
          join USER_VALUES_10 on Users.ID=USER_VALUES_10.ID
          where VALUE_50 is not null and enter=0;   
       update zyivki set zyivki.enter=1, zyivki.status='На тер', zyivki.data_enter=tmp3.data_enter
          from zyivki 
          join tmp3 on zyivki.id=tmp3.id; 
       insert into in_out_log (date_in_out, status, id, nomer, tarif, kol_zak, kol, summa, organ, kontakt, data, data_enter, data_v, kto, enter, guid) select * from tmp3;
       if exists (select * from tmp3) select status as I, date_in_out as I, nomer as I from tmp3;        
    COMMIT TRANSACTION
    BEGIN TRANSACTION
       if OBJECT_ID ('tmp3', 'U') is not null drop table tmp3;
       select GETDATE() as date_in_out, 'Выезд' as status, zyivki.id, nomer, tarif, kol_zak, kol, summa, organ, kontakt, data, data_enter, data_v, kto,
              enter, UserGuid as guid
          into tmp3   
          from zyivki
          join Users on zyivki.id=Users.ID
          join USER_VALUES_10 on Users.ID=USER_VALUES_10.ID
          where VALUE_50 is null and enter=1 and kol>1;       
       update zyivki set enter=0, status='Не исп', kol=kol-1
          from zyivki where id in (select id from tmp3);
       insert into in_out_log (date_in_out, status, id, nomer, tarif, kol_zak, kol, summa, organ, kontakt, data, data_enter, data_v, kto, enter, guid) select * from tmp3;  
       if exists (select * from tmp3) select status as I, date_in_out as I, nomer as I from tmp3;          
    COMMIT TRANSACTION
    BEGIN TRANSACTION
       if object_id('tmp3', 'U') is not null drop table tmp3;
       select GETDATE() as date_in_out, 'Использованная' as status, zyivki.id, nomer, tarif, kol_zak, kol, summa, organ, kontakt, data, data_enter, data_v, kto,
              enter, UserGuid as guid
          into tmp3   
          from zyivki
          join Users on zyivki.id=Users.ID
          join USER_VALUES_10 on Users.ID=USER_VALUES_10.ID
          where VALUE_50 is null and enter=1 and kol=1;         
       insert into last_claims (Zakaz, Organ, kol_zak, Kol, Data_akt, Data, Tarif, Nomer, summa, Date_enter, Id, Status, guid, kto)
          select kontakt, Organ, kol_zak, kol_zak, data, data_v, Tarif, Nomer, summa, Data_enter, Id, Status, guid, kto from tmp3;
       insert q_log (neisp_date, Ch, nomer, kol, id, data, enter, [exit], organ, tarif, summa, kol_zak, kontakt, Orgid, status, data_v, kto, data_enter)
         select GETDATE(), Ch, nomer, kol, zyivki.id, data, enter, [exit], organ, tarif, summa, kol_zak, kontakt, Orgid, 'kol<1', data_v, kto, data_enter
           from zyivki
           join Users on zyivki.id=Users.ID
           join USER_VALUES_10 on Users.ID=USER_VALUES_10.ID
           where VALUE_50 is null and enter=1 and kol<1;
       delete from zyivki where id in (select id from tmp3);
       delete from Users where ID in (select ID from tmp3);
       insert into in_out_log (date_in_out, status, id, nomer, tarif, kol_zak, kol, summa, organ, kontakt, data, data_enter, data_v, kto, enter, guid) select * from tmp3;  
       if exists (select * from tmp3) select status as I, date_in_out as I, nomer as I from tmp3;    
    COMMIT TRANSACTION
    BEGIN TRANSACTION
       if OBJECT_ID ('tmp3', 'U') is not null drop table tmp3;
       select GETDATE() as date_in_out, 'По карточкам' as status, zyivki.id, nomer, tarif, kol_zak, kol, summa, organ, kontakt, data,
              data as data_enter, data_v, kto, enter, NEWID() as guid
          into tmp3   
          from zyivki
          where tarif like 'По карточкам%';       
       insert into last_claims (Zakaz, Organ, kol_zak, Kol, Data_akt, Data, Tarif, Nomer, summa, Date_enter, Id, Status, kto)
          select kontakt, Organ, kol_zak, kol_zak, data, data_v, Tarif, Nomer, summa, Data_enter, Id, 'Использованная', kto from tmp3;
       delete from zyivki where id in (select id from tmp3);
       insert into in_out_log (date_in_out, status, id, nomer, tarif, kol_zak, kol, summa, organ, kontakt, data, data_enter, data_v, kto, enter, guid) select * from tmp3;  
       if exists (select * from tmp3) select status as I, date_in_out as I, nomer as I from tmp3;     
    COMMIT TRANSACTION
    BEGIN TRANSACTION
       if OBJECT_ID ('tmp9', 'U') is not null drop table tmp9;
       if GETDATE()>='2015-12-29'
         select getdate() as date_vyx, ch, id, nomer, tarif, kol_zak, kol, Lex*kol_zak*1.7142857 as summa, lex, zyivki.organ, data, data_enter, data_v, kto, enter, kontakt
          into tmp9
          from zyivki
          join organization on zyivki.organ=organization.organ
          where Tarif in ('Заявка-выходной', 'Заявка-легковой') and Data in (select vyx from vyx) and summa<>cast(Lex*kol_zak*1.7142857 as money) and kuda<>4
        else
         select getdate() as date_vyx, ch, id, nomer, tarif, kol_zak, kol, Lex*kol_zak*2 as summa, lex, zyivki.organ, data, data_enter, data_v, kto, enter, kontakt
          into tmp9
          from zyivki
          join organization on zyivki.organ=organization.organ
          where Tarif in ('Заявка-выходной', 'Заявка-легковой') and Data in (select vyx from vyx) and summa<>Lex*kol_zak*2 and kuda<>4   
       update zyivki set zyivki.summa=tmp9.summa from zyivki join tmp9 on zyivki.Ch=tmp9.ch
       insert into vyx_log (date_vyx, ch, id, nomer, tarif, kol_zak, kol, summa, lex, organ, data, data_enter, data_v, kto, enter, kontakt) select * from tmp9
    COMMIT TRANSACTION
    
    BEGIN TRANSACTION
    if object_id('tmp10', 'U') is not null drop table tmp10;
       declare @uid as int, @uguid as uniqueidentifier, @v41 as nvarchar(255), @v43 as nvarchar(255), @v47 as nvarchar(255), @v48 as int,
               @v49 as uniqueidentifier, @v50 as datetime2(3), @v53 as datetime2(3), @v54 as nvarchar(255), @v60 as nvarchar(255);
       declare @mid as int, @ct as int;
       set @ct=0;
       SELECT a.ID, a.UserGuid, VALUE_41, VALUE_43, VALUE_47, VALUE_48, VALUE_49, VALUE_50, VALUE_53, VALUE_54, VALUE_60
          into tmp10
          FROM Users as a
          join USER_VALUES_9 on a.id=USER_VALUES_9.id
          join USER_VALUES_7 on a.ID=USER_VALUES_7.ID
          join USER_VALUES_10 on a.ID=USER_VALUES_10.ID
          join USER_VALUES_8 on a.ID=USER_VALUES_8.ID
          join Users as b on VALUE_49=b.UserGuid
          join USER_VALUES_11 on b.ID=USER_VALUES_11.ID
          where VALUE_60='Кнопка "Отменить"' and VALUE_50 is not null  
          order by 1;
       declare cur cursor local fast_forward
         for select id, UserGuid, VALUE_41, VALUE_43, VALUE_47, VALUE_48, VALUE_49, VALUE_50, VALUE_53, VALUE_54, VALUE_60 from tmp10 order by id;
       open cur;
       fetch next from cur into @uid, @uguid, @v41, @v43, @v47, @v48, @v49, @v50, @v53, @v54, @v60;
       while @@FETCH_STATUS=0
       begin 
          update USER_VALUES_10 set VALUE_50=null, VALUE_151=GETDATE() where id=@uid;
	      insert into messages (msgDate, type, categoryid, eventid, terminalid, objectid, userid, appid, computerid)
                 values (GETDATE(), 8, 65537, 6, '60B32A89-B57D-4ADD-A4A4-9EE3C54581D3', @uguid, '050831DC-109A-47D7-A650-801C5082738D', 2, 8);
	      set @mid=@@IDENTITY;
	      insert into strings (messageid,argumentid,strdata) values (@mid, 0, @v43+'|^'+@v41);
	      insert into strings (messageid,argumentid,strdata) values (@mid, 1, @v49);
	      insert into strings (messageid,argumentid,strdata) values (@mid, 2, @v50);
	      insert into strings (messageid,argumentid,strdata) values (@mid, 3, @v50);
	      insert into strings (messageid,argumentid,strdata) values (@mid, 4, '0');
	      insert into strings (messageid,argumentid,strdata) values (@mid, 5, @v47);
	      insert into strings (messageid,argumentid,strdata) values (@mid, 6, @v54);
	      insert into strings (messageid,argumentid,strdata) values (@mid, 7, @uguid);
          insert into strings (messageid,argumentid,strdata) values (@mid, 8, '1');
	      insert into out_o_log (out_date, ID, UserGuid, VALUE_41, VALUE_43, VALUE_47, VALUE_48, VALUE_50, VALUE_53, VALUE_54, VALUE_60)
	             values (GETDATE(), @uid, @uguid, @v41, @v43, @v47, @v48, @v50, @v53, @v54, @v60); 
          update USER_VALUES_14 set VALUE_107=VALUE_107-1, VALUE_171=VALUE_171-1;
	      print @v54 + '   выведена'; 
	      set @ct = @ct + 1;
          fetch next from cur into @uid, @uguid, @v41, @v43, @v47, @v48, @v49, @v50, @v53, @v54, @v60;  
       end
       close cur;
       deallocate cur;
       print '...';
       print 'Всего выведено с территории: ' + cast(@ct as char);
       print '...';
    COMMIT TRANSACTION
    
    BEGIN TRANSACTION
       update zyivki set status='Не исп' where kol=kol_zak and enter=0
    COMMIT TRANSACTION
    BEGIN TRANSACTION
       update zyivki set status='На тер', data_enter=VALUE_50
          FROM zyivki 
          join USER_VALUES_10 on zyivki.id=USER_VALUES_10.ID
          where enter=1 and data_enter is null and VALUE_50 is not null
    COMMIT TRANSACTION
    BEGIN TRANSACTION
       update zyivki set data_v=GETDATE() where data_v is null
    COMMIT TRANSACTION
    BEGIN TRANSACTION
       update USER_VALUES_10 set VALUE_53='1958-01-01 00:00:00.000'
          from USER_VALUES_10
          join cards on USER_VALUES_10.ID=cards.id and Bloc=1
    COMMIT TRANSACTION
END TRY
BEGIN CATCH
   if XACT_STATE() = -1 ROLLBACK TRANSACTION;
   if XACT_STATE() = 1 COMMIT TRANSACTION;
   insert into err_log (err_date, num_err, text_message, name_proc, num_line, kod_state, level_severity)
      values (GETDATE(), ERROR_NUMBER(), ERROR_MESSAGE(), ERROR_PROCEDURE(), ERROR_LINE(), ERROR_STATE(), ERROR_SEVERITY());
   print 'Аварийное завершение программы.';
   print 'Ошибка: ' + cast(error_number() as char);
   print error_message();   
END CATCH;
waitfor delay '00:00:40'
end