/****** Object:  StoredProcedure [dbo].[pOT_ANCheckVersion]    Script Date: 02/25/2009 19:11:27 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[pOT_ANCheckVersion]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[pOT_ANCheckVersion]
GO
/****** Object:  StoredProcedure [dbo].[pOT_ANExportOneDocType]    Script Date: 02/25/2009 19:11:27 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[pOT_ANExportOneDocType]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[pOT_ANExportOneDocType]
GO
/****** Object:  StoredProcedure [dbo].[pOT_ANGenerateCopyOrderOtos]    Script Date: 02/25/2009 19:11:27 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[pOT_ANGenerateCopyOrderOtos]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[pOT_ANGenerateCopyOrderOtos]
GO
/****** Object:  StoredProcedure [dbo].[pOT_ANGetColumns]    Script Date: 02/25/2009 19:11:27 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[pOT_ANGetColumns]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[pOT_ANGetColumns]
GO
/****** Object:  StoredProcedure [dbo].[pOT_ANGetOneExportTable]    Script Date: 02/25/2009 19:11:27 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[pOT_ANGetOneExportTable]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[pOT_ANGetOneExportTable]
GO
/****** Object:  StoredProcedure [dbo].[pOT_ANImportAllTables]    Script Date: 02/25/2009 19:11:27 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[pOT_ANImportAllTables]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[pOT_ANImportAllTables]
GO
/****** Object:  StoredProcedure [dbo].[pOT_ANImportCreateTempOTOS]    Script Date: 02/25/2009 19:11:27 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[pOT_ANImportCreateTempOTOS]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[pOT_ANImportCreateTempOTOS]
GO
/****** Object:  StoredProcedure [dbo].[pOT_ANImportDelete]    Script Date: 02/25/2009 19:11:27 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[pOT_ANImportDelete]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[pOT_ANImportDelete]
GO
/****** Object:  StoredProcedure [dbo].[pOT_ANImportFinalise]    Script Date: 02/25/2009 19:11:27 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[pOT_ANImportFinalise]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[pOT_ANImportFinalise]
GO
/****** Object:  StoredProcedure [dbo].[pOT_ANImportInsertUpdate]    Script Date: 02/25/2009 19:11:27 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[pOT_ANImportInsertUpdate]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[pOT_ANImportInsertUpdate]
GO
/****** Object:  StoredProcedure [dbo].[pOT_ANImportKey]    Script Date: 02/25/2009 19:11:27 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[pOT_ANImportKey]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[pOT_ANImportKey]
GO
/****** Object:  StoredProcedure [dbo].[pOT_ANImportKeySIFRANTI]    Script Date: 02/25/2009 19:11:27 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[pOT_ANImportKeySIFRANTI]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[pOT_ANImportKeySIFRANTI]
GO
/****** Object:  StoredProcedure [dbo].[pOT_ANImportPrepareTableList]    Script Date: 02/25/2009 19:11:27 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[pOT_ANImportPrepareTableList]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[pOT_ANImportPrepareTableList]
GO
/****** Object:  StoredProcedure [dbo].[pOT_ANManageProfiles]    Script Date: 02/25/2009 19:11:27 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[pOT_ANManageProfiles]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[pOT_ANManageProfiles]
GO
/****** Object:  StoredProcedure [dbo].[pOT_ANManageTempTables]    Script Date: 02/25/2009 19:11:27 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[pOT_ANManageTempTables]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[pOT_ANManageTempTables]
GO
/****** Object:  StoredProcedure [dbo].[pOT_ANManageTriggers]    Script Date: 02/25/2009 19:11:27 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[pOT_ANManageTriggers]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[pOT_ANManageTriggers]
GO
/****** Object:  StoredProcedure [dbo].[pOT_ANPrepareAllDocsForExport]    Script Date: 02/25/2009 19:11:27 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[pOT_ANPrepareAllDocsForExport]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[pOT_ANPrepareAllDocsForExport]
GO
/****** Object:  StoredProcedure [dbo].[pOT_ANPrepareAllTablesForExport]    Script Date: 02/25/2009 19:11:27 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[pOT_ANPrepareAllTablesForExport]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[pOT_ANPrepareAllTablesForExport]
GO
/****** Object:  StoredProcedure [dbo].[pOT_ANPrepareImport]    Script Date: 02/25/2009 19:11:27 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[pOT_ANPrepareImport]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[pOT_ANPrepareImport]
GO
/****** Object:  StoredProcedure [dbo].[pOT_ANPrepareTableForExport]    Script Date: 02/25/2009 19:11:27 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[pOT_ANPrepareTableForExport]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[pOT_ANPrepareTableForExport]
GO
/****** Object:  StoredProcedure [dbo].[pOT_ANRefreshDocTypeChoosed]    Script Date: 02/25/2009 19:11:27 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[pOT_ANRefreshDocTypeChoosed]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[pOT_ANRefreshDocTypeChoosed]
GO
/****** Object:  StoredProcedure [dbo].[pOT_ANSetExchanges]    Script Date: 02/25/2009 19:11:27 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[pOT_ANSetExchanges]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[pOT_ANSetExchanges]
GO
/****** Object:  StoredProcedure [dbo].[pOT_ANSetTreeImportExport]    Script Date: 02/25/2009 19:11:27 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[pOT_ANSetTreeImportExport]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[pOT_ANSetTreeImportExport]
GO
/****** Object:  StoredProcedure [dbo].[pOT_ANCheckVersion]    Script Date: 02/25/2009 19:11:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[pOT_ANCheckVersion]
@Error_Report varchar(200) output
AS

declare		
@DataBaseVersion integer,
@DataBaseExchanges integer

SET @Error_Report='OK'

SET @DataBaseExchanges=isnull((SELECT TOP 1 anBuildNo FROM lOT_ANSettingsExchanges ORDER BY adDateCentralStart desc), 0)
SET @DataBaseVersion =(SELECT TOP 1 anBuildNo FROM tPA_SysParamSys tsps)


IF  @DataBaseVersion<>@DataBaseExchanges SET @Error_Report='Database versions are NOT matched: Imported: '+cast(@DataBaseExchanges AS varchar(10))+
' Current: '+cast(@DataBaseVersion AS varchar(10))









GO
/****** Object:  StoredProcedure [dbo].[pOT_ANExportOneDocType]    Script Date: 02/25/2009 19:11:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



CREATE PROCEDURE [dbo].[pOT_ANExportOneDocType]
   @acProfile char(3),
   @acSetOf char(1),
   @acType char(1),
   @acDocType char(4),
   @dDateOfLastReplicationFROM smalldatetime,
   @dDateOfLastReplicationTo smalldatetime,
   @acChoosed char(1)
AS
set nocount on

  DECLARE
  	@acTables varchar(8000),
  	@acRoot varchar(80),
  	@acWhere varchar(120),
  	@TempMasterTableName varchar(100),
  	@cSQL varchar(5000)

  SELECT       
				@acTables=tpa.acTables,
				@acRoot=tpa.acRoot,
				@acWhere=tpa.acWhere
  FROM tOT_ANDocTypeChoosed tatc
  INNER JOIN tPA_SysDocTypeOfKind tpa ON tpa.acSetOf = tatc.acSetOf AND tpa.acType = tatc.acType
  WHERE
	acKeyProfile=@acProfile AND
	tatc.acChoosed IN ('1', '2') AND
	tatc.acSetOf=@acSetOf AND
	tatc.acType=tatc.acType AND
	tatc.acDocType=@acDocType AND
	len(isnull(tpa.acRoot, ''))<>0

  IF @acRoot in ('tHR_SlryCalcSum', 'lHE_BCPackage_A') GOTO konec
  
  DELETE FROM lOT_ListOfColumns

DECLARE 
	@acFieldName varchar(50),
	@SqlOn varchar(6000)

IF @acRoot<>'' 
begin
insert into lOT_ListOfColumns exec ('pOT_ANGetColumns '+@acRoot+',''PK''')

PRINT 'Root je: '+@acRoot

set @SqlOn=' '
declare keys cursor local fast_forward
for select acFieldName from lOT_ListOfColumns
open keys
fetch next from keys into @acFieldName
while (@@fetch_status = 0)
begin
	set @SqlOn=@SqlOn+' s.'+@acFieldName+'= d.'+@acFieldName +' and'
    fetch next from keys into @acFieldName
end
close keys
deallocate keys

if substring(@sqlOn,len(@SqlOn)-2,3)='and'
	set @SqlOn=substring(@SqlOn,1,len(@SqlOn)-3)
	
DELETE FROM lOT_ListOfColumns
end


  SET @TempMasterTableName='l'+substring(@acRoot, 2, len(@acRoot)-1)+'_A'

  if exists (select * from sysobjects where name=@TempMasterTableName)
  begin  
    SET @cSQL=' if not exists (select * from '+@TempMasterTableName+' s '+
			  ' inner join '+@acRoot+' d on '+@SqlOn+' and s.acDocType='''+@acDocType+''')'+
			  ' insert into '+@TempMasterTableName+
              ' SELECT  newid() as Id, '+''''+@acRoot+''' as acTableName, '+
              ' CASE WHEN '+ @acRoot+'.adTimeIns between ' +
					' convert(datetime,'''+convert(varchar(50),@dDateOfLastReplicationFROM,112)+  ' ' + convert(varchar(50),@dDateOfLastReplicationFROM,114) + ''') and '+
					' convert(datetime,'''+convert(varchar(50),@dDateOfLastReplicationTO,112)+  ' ' + convert(varchar(50),@dDateOfLastReplicationTO,114) + ''')'+
                         ' THEN ''I'' ELSE ''U'' END as acOTOSType,' +       			  		
			   @acRoot+'.* from '+ @acRoot+
			   ' where '+ CASE WHEN len(isnull(@acWhere, ''))=0 THEN (''''+@acDocType+'''=') ELSE @acWhere END+''''+@acDocType+''''+
              ' and   ('+@acRoot+'.adTimeIns between '+
              ' convert(datetime,'''+convert(varchar(50),@dDateOfLastReplicationFROM,112)+  ' ' + convert(varchar(50),@dDateOfLastReplicationFROM,114) + ''') and '+
              ' convert(datetime,'''+convert(varchar(50),@dDateOfLastReplicationTO,112)+  ' ' + convert(varchar(50),@dDateOfLastReplicationTO,114) + ''')'+
              ' or '+@acRoot+'.adTimeChg  between '+
			  ' convert(datetime,'''+convert(varchar(50),@dDateOfLastReplicationFROM,112)+  ' ' + convert(varchar(50),@dDateOfLastReplicationFROM,114) + ''') and '+
              ' convert(datetime,'''+convert(varchar(50),@dDateOfLastReplicationTO,112)+  ' ' + convert(varchar(50),@dDateOfLastReplicationTO,114) + '''))'
  end
  IF (@cSQL<>'') EXEC (@cSQL)

  SET @cSQL = 'INSERT INTO lOT_TableOfAllChanges_A '+
			  'SELECT  acTableName, Id, '+
              'acOTOSType, CASE WHEN acOTOSType=''I'' THEN adTimeIns ELSE adTimeChg END as a,' +
              '''M'', 1'+','+isnull(@acChoosed, '0')+
              ' FROM l'+substring(@acRoot, 2, len(@acRoot)-1)+'_A '
				
  EXEC (@cSQL)

  DELETE FROM lOT_DocSet
  
  EXEC pPA_ParseSemiColToTable 'lOT_DocSet', 'acTableName', @acTables

 DECLARE
  	@acTableName		varchar(100),
  	@acKeyMaster		varchar(100),
  	@acKeyDetail		varchar(100),
  	@TempDetailTableName varchar(100),
  	@SqlOn2				varchar(2000),
  	@cFieldName         varchar(100),
  	@CanJoin			char(1),
    @TimeFieldsOK       char(1),
    @MasterJoinFieldCount     integer

  DECLARE DocTables CURSOR LOCAL FAST_FORWARD FOR
  SELECT *	FROM lOT_DocSet t
			WHERE NOT EXISTS (SELECT * FROM lOT_AllTablesSifranti a WHERE a.acTableName=t.acTableName)

  OPEN DocTables
  FETCH NEXT FROM DocTables INTO @acTableName
  WHILE (@@fetch_status = 0)
  BEGIN
      --Pridobimo ključe...	
    DELETE FROM lOT_ListOfColumns
	insert into lOT_ListOfColumns exec ('pOT_ANGetColumns '+@acRoot+',''PK''')

	set @SqlOn2=' '
	SET @MasterJoinFieldCount=0
	declare keys cursor local fast_forward
	for select acFieldName from lOT_ListOfColumns
	open keys
	fetch next from keys into @cFieldName
	while (@@fetch_status = 0)
	begin
		set @SqlOn2=@SqlOn2+' tm.'+@cFieldName+'=td.'+@cFieldName +' and'	
		SET @MasterJoinFieldCount=@MasterJoinFieldCount+1
		fetch next from keys into @cFieldName
	end
	close keys
	deallocate keys      
    DELETE FROM lOT_ListOfColumns
    
    DELETE FROM lOT_RecordCount
    --Kontrola, ali tabela vsebuje polja actimeins in actimechg    
    SET @cSQL='INSERT lOT_RecordCount SELECT count(*) FROM SysObjects b WHERE b.name='''+@acTableName+''' and '+
	' exists (select * from syscolumns where b.id=id and upper(name)=''ADTIMEINS'') and '+
	' exists (select * from syscolumns where b.id=id and upper(name)=''ADTIMECHG'')'

    EXEC(@cSQL)
    IF (SELECT anCount FROM lOT_RecordCount)>0 SET @TimeFieldsOK='D' ELSE SET @TimeFieldsOK='N'
    DELETE FROM lOT_RecordCount
    ----------------------------------------------------------------------------------
    
    --Kontrola, ali je možen JOIN med root in odvisno tabelo...
    SET @CanJoin='N'
    
    DELETE FROM lOT_JoinCols        
    insert into lOT_JoinCols exec ('pOT_ANGetColumns '+@acTableName+',''ALL''')
    
    DECLARE @keynameDetail varchar(100)
	DECLARE @DetailJoinFieldCount integer
		
    IF (SELECT count(*) FROM lOT_JoinCols)>0
	BEGIN
	  SET @DetailJoinFieldCount=0
	  DECLARE TestJoin CURSOR local fast_forward
	  FOR SELECT acKeys1 FROM lOT_JoinCols	
	  OPEN TestJoin
	  FETCH next FROM TestJoin INTO @keynameDetail
	  WHILE (@@fetch_status=0)
	  BEGIN	  	
	  	IF (patindex('%'+@keynameDetail+'%', @SqlOn2) > 0) SET @DetailJoinFieldCount=@DetailJoinFieldCount+1
	  	FETCH next FROM TestJoin INTO @keynameDetail
	  END
	  CLOSE TestJoin
	  DEALLOCATE TestJoin
	  IF @MasterJoinFieldCount=@DetailJoinFieldCount SET @CanJoin='D'
	END
    
    DELETE FROM lOT_JoinCols

    ---------------------------------------------
    if substring(@SqlOn2,len(@SqlOn2)-2,3)='and'
	  set @SqlOn2=substring(@SqlOn2,1,len(@SqlOn2)-3)
    
    SET @TempDetailTableName='l'+substring(@acTableName, 2, len(@acTableName)-1)+'_A'

	if exists (select * from sysobjects where name=@TempDetailTableName)
    BEGIN    
      ----Preverjamo ali ima zapisov za insert...
      DECLARE @stevilovrstic integer
      SET @cSQL=' insert lOT_RecordCount select count(*) from '+@acTableName+' td '+
			  ' inner join '+@TempMasterTableName+' tm on '+@SqlOn2+' and tm.acDocType='''+@acDocType+''' where '+
			  ' td.adTimeIns between '+
              ' convert(datetime,'''+convert(varchar(50),@dDateOfLastReplicationFROM,112)+  ' ' + convert(varchar(50),@dDateOfLastReplicationFROM,114) + ''') and '+
              ' convert(datetime,'''+convert(varchar(50),@dDateOfLastReplicationTO,112)+  ' ' + convert(varchar(50),@dDateOfLastReplicationTO,114) + ''')'+
              ' or td.adTimeChg  between '+
			  ' convert(datetime,'''+convert(varchar(50),@dDateOfLastReplicationFROM,112)+  ' ' + convert(varchar(50),@dDateOfLastReplicationFROM,114) + ''') and '+
              ' convert(datetime,'''+convert(varchar(50),@dDateOfLastReplicationTO,112)+  ' ' + convert(varchar(50),@dDateOfLastReplicationTO,114) + ''')'     

      IF @TimeFieldsOK='D' EXEC (@cSQL)
      SET @stevilovrstic=(SELECT anCount FROM lOT_RecordCount)
      DELETE FROM lOT_RecordCount
      
      -----------------------------------------------------------
      IF @stevilovrstic>0               
        SET @cSQL='if not exists(select * from '+@TempDetailTableName+' td inner join '+@TempMasterTableName+' tm on '+@SqlOn2+') ' +
              ' insert into '+isnull(@TempDetailTableName, 'X')+
			  ' SELECT  newid() as Id, '+''''+@acRoot+''' as acTableName, '+
              ' CASE WHEN td.adTimeIns between ' +
			  ' convert(datetime,'''+convert(varchar(50),@dDateOfLastReplicationFROM,112)+  ' ' + convert(varchar(50),@dDateOfLastReplicationFROM,114) + ''') and '+
			  ' convert(datetime,'''+convert(varchar(50),@dDateOfLastReplicationTO,112)+  ' ' + convert(varchar(50),@dDateOfLastReplicationTO,114) + ''')'+
              ' THEN ''I'' ELSE ''U'' END as acOTOSType,' +
			  ' td.* from '+isnull(@acTableName, 'X')+' td '+
			  ' inner join '+isnull(@TempMasterTableName, 'X')+' tm on '+@SqlOn2+' and tm.acDocType='''+@acDocType+''' where '+
			  ' td.adTimeIns between '+
              ' convert(datetime,'''+convert(varchar(50),@dDateOfLastReplicationFROM,112)+  ' ' + convert(varchar(50),@dDateOfLastReplicationFROM,114) + ''') and '+
              ' convert(datetime,'''+convert(varchar(50),@dDateOfLastReplicationTO,112)+  ' ' + convert(varchar(50),@dDateOfLastReplicationTO,114) + ''')'+
              ' or td.adTimeChg  between '+
			  ' convert(datetime,'''+convert(varchar(50),@dDateOfLastReplicationFROM,112)+  ' ' + convert(varchar(50),@dDateOfLastReplicationFROM,114) + ''') and '+
              ' convert(datetime,'''+convert(varchar(50),@dDateOfLastReplicationTO,112)+  ' ' + convert(varchar(50),@dDateOfLastReplicationTO,114) + ''')'	    
       ELSE
       	 SET @cSQL='XXX'
    END
    IF @SqlOn2<>' '
    begin
     PRINT 'ROOT: '+@acRoot
     PRINT 'TABLES: '+@acTables
     PRINT 'Table name: '+@acTableName
     PRINT 'Master: ' +@TempMasterTableName
     PRINT 'Detail: '+@TempDetailTableName
     PRINT 'Key name detail: '+@keynameDetail
     PRINT 'SQL ON : '+@SqlOn2
     PRINT 'CAN JOIN: '+@CanJoin
     PRINT 'Time fields OK '+@TimeFieldsOK

     IF (@CanJoin='D') AND (isnull(@cSQL, 'X')<>'XXX') AND (@TimeFieldsOK='D') 
        AND (patindex('%acWorker%', @cSQL)=0) AND (@TempDetailTableName<>'lHE_BCPackage_A') EXEC (@cSQL)
	
     SET @cSQL =' INSERT INTO lOT_TableOfAllChanges_A '+
			  'SELECT  acTableName, Id, '+
              'acOTOSType, CASE WHEN acOTOSType=''I'' THEN adTimeIns ELSE adTimeChg END as a,' +
              '''S'', 1'+','+isnull(@acChoosed, '0')+
              ' FROM l'+substring(@acTableName, 2, len(@acTableName)-1)+'_A '
				
     IF (@CanJoin='D') AND (isnull(@cSQL, 'X')<>'XXX') AND (@TimeFieldsOK='D') 
        AND (patindex('%acWorker%', @cSQL)=0) AND (@TempDetailTableName<>'lHE_BCPackage_A') EXEC (@cSQL)
    end
	FETCH NEXT FROM DocTables INTO @acTableName
  END
  CLOSE DocTables
  DEALLOCATE DocTables
  DELETE FROM lOT_DocSet 
  
  konec:












GO
/****** Object:  StoredProcedure [dbo].[pOT_ANGenerateCopyOrderOtos]    Script Date: 02/25/2009 19:11:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[pOT_ANGenerateCopyOrderOtos]
	@TableNames varchar(4000)
as
set nocount on

create table #tmp
   (Tabela int not null,
    Odvisna int not null,
	Nivo integer not null)

declare @Nivo integer,
        @Vneseno int
set @Nivo = 0

insert #tmp (Tabela, Odvisna, Nivo)
       select distinct object_id (t.acItem), 0, @Nivo
         from dbo.fPA_SplitString (@TableNames, ';') t
         where object_id (t.acItem) is not null
set @Vneseno = @@ROWCOUNT

while @Vneseno > 0
begin
  set @Nivo = @Nivo + 1
  set @Vneseno = 0
  insert #tmp (Tabela, Odvisna, Nivo)
         select distinct fk.rkeyid, fk.fkeyid, @Nivo
           from sysForeignKeys fk
           join #tmp t on t.Tabela = fk.fkeyid
                      and t.Nivo = @Nivo - 1
           where not exists (select *
                               from #tmp tt
                               where tt.Odvisna = fk.fkeyid
                                 and tt.Tabela = fk.rkeyid)

  set @Vneseno = @@ROWCOUNT
end

select object_name (Tabela) as acTableName, max(Nivo) as Nivo
  from #tmp
  group by Tabela
  order by 2 desc, 1



GO
/****** Object:  StoredProcedure [dbo].[pOT_ANGetColumns]    Script Date: 02/25/2009 19:11:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[pOT_ANGetColumns]
 @tablename varchar(50),
 @Type varchar(5) = 'ALL'
as
set nocount on
BEGIN
	declare @objid int
	select @objid = object_id(@tablename)
	if (@objid is null) or (OBJECTPROPERTY(@objid, N'IsTable') = 0)
	begin
		RAISERROR (15001, -1, -1, @tablename)
		return 1
	end

	set nocount on

   /* Do not store the computed text in this temp table, because one extra join causes big performance hit */
	/* First load stuff so we can blot off inappropriate info and massage as per @flags */
	DELETE FROM lOT_sphelpcols
	
		INSERT lOT_sphelpcols
		select 0 as col_flags, c.name, c.id, c.colid
		from dbo.syscolumns c
		where c.id = @objid and iscomputed=0
		order by c.colid


	/* Determine if the column is in the primary key */
	if (OBJECTPROPERTY(@objid, N'TableHasPrimaryKey') <> 0)
	begin
		declare @indid int
		select @indid = indid from dbo.sysindexes i where i.id = @objid and i.status & 0x0800 <> 0
		if (@indid is not null)
			update lOT_sphelpcols set col_flags = col_flags | 0x0004
			from lOT_sphelpcols c, dbo.sysindexkeys i
				where i.id = @objid and i.indid = @indid and i.colid = c.colid
	end

	if upper(@Type)='ALL'
		SELECT name FROM lOT_sphelpcols
	else
	if upper(@Type)='PK'
		SELECT name FROM lOT_sphelpcols WHERE col_flags=4
	if upper(@Type)='NOPK'
		SELECT name FROM lOT_sphelpcols WHERE col_flags<>4
	DELETE FROM lOT_sphelpcols
	RETURN 0

END









GO
/****** Object:  StoredProcedure [dbo].[pOT_ANGetOneExportTable]    Script Date: 02/25/2009 19:11:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[pOT_ANGetOneExportTable]
  @acTableName varchar(100)
as
set nocount on

set @acTableName=Rtrim(@acTableName)

declare @sql varchar(500)

set @sql='if exists (select * from tempdb.dbo.sysobjects where name='''+
	@acTableName+''') '+
    ' begin '+
    ' select * from tempdb.dbo.'+@acTableName+
	' end '+' else select '''+
	' Napaka: Tabela '+@acTableName+' ne obstaja!'''
exec (@sql)



GO
/****** Object:  StoredProcedure [dbo].[pOT_ANImportAllTables]    Script Date: 02/25/2009 19:11:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[pOT_ANImportAllTables]
	@cProfile char(3)
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE
		@idGUIDTransfer varchar(38),
		@Log varchar(8000),
		@ChrRet varchar(10),
		@Return_status int


	SET @ChrRet = char(13)
    SET @Log = 'Import started at : '+ cast(getdate() as varchar(50)) + ' Profile: ' + @cProfile +@ChrRet
	exec @Return_status = [dbo].[pOT_ANSetExchanges]	@cProfile ,'I', @idGUIDTransfer OUTPUT
    if @Return_status<>0
		RETURN @Return_status
	
	SET @Log = @Log + 'Cycle : '+ cast(@idGUIDTransfer as varchar(50))+@ChrRet
	IF not exists (SELECT * FROM tempdb.dbo.sysobjects WHERE NAME = '##TableOfAllChanges_A')
		return 5

	SELECT distinct al.acTableName , /*ie.acTYPE,*/
		CASE WHEN EXISTS (SELECT * FROM tOT_ImportExport ti
		                   WHERE ti.acTables=al.acTableName) THEN 1 ELSE 0 END as acMasterTable
		INTO #AllTables
		FROM ##TableOfAllChanges_A al
		
	SELECT DISTINCT
		CASE WHEN acOTOSType = 'D' THEN 2*anLevel
			 WHEN acOTOSType = 'I' THEN -2*anLevel
			 WHEN acOTOSType = 'U' THEN -1*anLevel
			 WHEN acOTOSType = 'K' THEN 1*anLevel
			 ELSE 0
		END as anSortOder,
		*
		INTO #AllRecords
		FROM ##TableOfAllChanges_A
		WHERE acMasterSlave in ('M','S')
	 ORDER BY	adTimeChg, 1

						
	DECLARE
		@nSortOrder int,
		@nLevel int,
		@cTableName varchar(50),
		@cTableNameSource varchar(50),
		@gId varchar(38),
 		@cType varchar(10),
		@dTimeChg datetime,
		@cMasterSlave char(1),
		@sql varchar(8000)

	exec sp_MSforeachtable @command1="ALTER TABLE ? DISABLE TRIGGER ALL"
	exec sp_MSforeachtable @command1="ALTER TABLE ? NOCHECK CONSTRAINT ALL "
	DECLARE @Ret int

	DECLARE AllRecords CURSOR LOCAL FAST_FORWARD FOR
		SELECT *
         FROM #AllRecords
	  ORDER BY	adTimeChg, 1
	OPEN AllRecords
	FETCH NEXT FROM AllRecords INTO @nSortOrder, @cTableName,@gId, @cType, @dTimeChg, @cMasterSlave, @nLevel
    WHILE (@@fetch_status = 0)
    BEGIN
			SET @Log = @Log+IsNull('   Table: '+ @cTableName + ' OTOSType: ' + @cType + ' OTOS Table: ' + @cTableNameSource + ' OTOS Record: '+cast(@idGUIDTransfer as varchar(50))+@ChrRet, ' no data!')
			IF (@cType='I') OR (@cType='U')
			BEGIN
				SET  @cTableNameSource='##'+@cTableName+'_A'
				EXEC @Ret=pot_ANImportInsertUpdate @gId, @cTableNameSource, @cTableName, @cType
			END
			ELSE IF @cType='D'
			BEGIN
				SET  @cTableNameSource='##'+@cTableName
				EXEC @Ret=pot_ANImportDelete @gId, @cTableNameSource, @cTableName
			END
			ELSE IF @cType='K'
			BEGIN
				IF substring(@cTableName, len(@cTableName)-1, 2)='_O'
				BEGIN
			      SET @cTableNameSource='##'+@cTableName
			      SET @cTableName=substring(@cTableName, 1, len(@CTableName)-2)
				END
				EXEC @Ret=pot_ANImportKeySIFRANTI @gId, @cTableNameSource, @cTableName
			END

		IF @Ret<>0
			SET @Log = @Log + isnull('   ERROR '+@cTableName+' '+isnull(@cType, 'NoType') +@ChrRet, ' no data!')
		FETCH NEXT FROM AllRecords INTO @nSortOrder, @cTableName,@gId, @cType, @dTimeChg, @cMasterSlave, @nLevel
    END
	CLOSE AllRecords
	DEALLOCATE AllRecords

	DECLARE UserTables CURSOR LOCAL FAST_FORWARD FOR
		SELECT distinct acTableName
		FROM ##TableOfAllChanges_A
		WHERE acMasterSlave in ('U')
    OPEN UserTables
	FETCH NEXT FROM UserTables INTO @cTableName
    WHILE (@@fetch_status = 0)
    BEGIN
		SET @Log = @Log + 'User table '+@cTableName+' '+@ChrRet
		SET @sql='IF (exists (SELECT * FROM tempdb.dbo.sysobjects WHERE NAME = ''##'+@cTableName+'_A'')) and '
		SET @sql=@sql+' (exists (SELECT * FROM dbo.sysobjects WHERE NAME = '''+@cTableName+''')) '
		SET @sql=@sql+' BEGIN'
		SET @sql=@sql+'   TRUNCATE TABLE '+@cTableName
		SET @sql=@sql+'   INSERT INTO '+@cTableName+' SELECT * FROM ##'+@cTableName+'_A'
		SET @sql=@sql+' END'
		EXEC (@sql)
		FETCH NEXT FROM UserTables INTO @cTableName
	END

	DROP TABLE #AllRecords
	DROP TABLE #AllTables

    SET @Log = @Log+'Import ended at : '+ cast(getdate() as varchar(50))
	UPDATE tOT_ANSettingsExchanges
		SET acCentralSuccess  = case when acImportExportType<>'C' then 1 else acCentralSuccess end,
			acLocationSuccess = case when acImportExportType='C' then 1 else acLocationSuccess END,
			acImportLog = @Log
	FROM tOT_ANSettingsExchanges se
	INNER JOIN tOT_ANSettingsProfiles sp ON se.acKeyProfile=sp.acKeyProfile
	WHERE acIdTransfer=@idGUIDTransfer

	
	exec sp_MSforeachtable @command1="ALTER TABLE ? ENABLE TRIGGER ALL"
	exec sp_MSforeachtable @command1="ALTER TABLE ?  CHECK CONSTRAINT ALL"

	RETURN 0

END



GO
/****** Object:  StoredProcedure [dbo].[pOT_ANImportCreateTempOTOS]    Script Date: 02/25/2009 19:11:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[pOT_ANImportCreateTempOTOS]
	@cTableName VarChar(255)
as
set nocount on

declare
   @cChangedTable   VarChar(255),
   @cKey            VarChar(8000),
   @cOldKey         VarChar(8000),
   @cUpdateKeys     VarChar(8000),
   @cFieldName      VarChar(255),
   @cFieldNames     VarChar(8000),
   @cNewFieldNames  VarChar(8000),
   @cNewFieldAdd    VarChar(8000),
   @cNewFieldIns    VarChar(8000),
   @cNewFieldDef    VarChar(8000),
   @cOldFieldNames  VarChar(8000),
   @cCheck          VarChar(8000),
   @cTempName       VarChar(255),
   @nCountNew       Integer,
   @nCountOld       Integer,
   @bDrop           Bit

set @cFieldName     = ''
set @cFieldNames    = ''
set @cNewFieldNames = ''
set @cNewFieldAdd   = ''
set @cNewFieldIns   = ''
set @cNewFieldDef   = ''
set @cCheck         = ''
set @cTableName     = RTrim(LTrim(@cTableName))
set @cChangedTable  = stuff(@cTableName, 1, 1, 'l')+'_O'
set @cKey           = ''
set @cOldKey        = ''
set @cUpdateKeys    = ''
set @nCountNew      = 0
set @bDrop          = 0

if Object_ID(@cTableName) is not Null begin
   set @cKey = (select top 1 acFieldSName from tPA_SysIndex where acTableName = @cTableName and (acIxPrimary = 'T' or acIxUnique = 'T') order by anSeqNo)
   if @cKey is null
      exec pPA_SysGetUniqueKey @cTableName, @cKey output

   set @cOldKey = @cKey

   create table #Key (acFieldName VarChar(255) null)
   exec pPA_ParseSemicolToTable '#Key' , 'acFieldName', @cKey

   if @cKey <> ''
   begin
      declare crKF cursor local fast_forward for select acFieldName from #Key
      open crKF
      fetch next from crKF into @cFieldName
      while (@@FETCH_STATUS = 0)
      begin
        if @cFieldNames <> ''
        begin
           set @cFieldNames    = @cFieldNames + ', ' + @cFieldName
           set @cNewFieldAdd   = @cNewFieldAdd + ', ' + @cFieldName + ' as New_' + @cFieldName
           set @cNewFieldNames = @cNewFieldNames + ', New_' + @cFieldName
           set @cNewFieldIns   = @cNewFieldIns + ', (select top 1 ' + @cFieldName + ' from inserted ins)'
           set @cNewFieldDef   = @cNewFieldDef + ', 0'
           set @cCheck         = @cCheck + ' and insCheck.' + @cFieldName + ' = delCheck.' + @cFieldName
        end
           else
        begin
          set @cFieldNames    = @cFieldName
          set @cNewFieldAdd   = @cFieldName + ' as New_' + @cFieldName
          set @cNewFieldNames = 'New_' + @cFieldName
          set @cNewFieldIns   = '(select top 1 ' + @cFieldName + ' from inserted ins)'
          set @cNewFieldDef   = '0'
          set @cCheck         = 'insCheck.' + @cFieldName + ' = delCheck.' + @cFieldName
        end
        set @nCountNew = @nCountNew + 2
        fetch next from crKF into @cFieldName
      end
      close crKF
      deallocate crKF

      if Object_ID(@cChangedTable) is Null
      begin
         exec ('select top 1 ''{538BED71-A4A5-4D61-9FD8-9D49203D5284}'' id, ''XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'''+
               ' acTableName, ' + @cFieldNames +   ', ' + @cNewFieldAdd + ', GetDate() as adTimeChg, cast(0 as int) as anUserChg, '' '' as acOtosType ' +
               'into [dbo].[' + @cChangedTable + '] ' +
               'from ' + @cTableName)
         exec ('truncate table ' + @cChangedTable)
      end
   end
  drop table #Key
end





GO
/****** Object:  StoredProcedure [dbo].[pOT_ANImportDelete]    Script Date: 02/25/2009 19:11:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[pOT_ANImportDelete]
  @id varchar(38),
  @acTableNameSource varchar(50),
  @acTableNameDestination varchar(50)
as
set nocount on

declare
    @Sql varchar(8000),
	@SqlOn varchar(3000),
    @cFieldName varchar(50)

DELETE FROM lOT_ListOfColumns

insert into lOT_ListOfColumns exec ('pOT_ANGetColumns '+@acTableNameDestination+',''PK''')

set @SqlOn=' '
declare keys cursor local fast_forward
for select acFieldName from lOT_ListOfColumns
open keys
fetch next from keys into @cFieldName
while (@@fetch_status = 0)
begin
	set @SqlOn=@SqlOn+' s.'+@cFieldName+'= d.'+@cFieldName +' and'
    fetch next from keys into @cFieldName
end
close keys
deallocate keys
if substring(@sqlOn,len(@sqlOn)-2,3)='and'
	set @sqlOn=substring(@sqlOn,1,len(@sqlOn)-3)

set @sql=' '
set @sql=@sql+' DELETE '+ @acTableNameDestination + ' FROM '+ @acTableNameDestination + ' d JOIN '+@acTableNameSource+' s ON '+@sqlOn+' WHERE s.id='''+cast(@id as varchar(38))+''''

exec (@sql)
if @@error<>0
	return 1
else
begin
    update tOT_AllRecordsImport set Imported='Y' where agId=@id
	return 0
end









GO
/****** Object:  StoredProcedure [dbo].[pOT_ANImportFinalise]    Script Date: 02/25/2009 19:11:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[pOT_ANImportFinalise]
  @cProfile char(3),
  @nSwitch int
as
begin
    DECLARE
		@idGUIDTransfer varchar(38),
		@Log varchar(8000),
		@ChrRet varchar(10),
		@Return_status int,	
		@cTableName varchar(50),		
		@sql varchar(8000),
		@acChoosed char(1)


if object_id('tOT_AllRecordsImport') is not NULL EXEC ('drop table tOT_AllRecordsImport')
	DECLARE UserTables CURSOR LOCAL FAST_FORWARD FOR
		SELECT distinct acTableName
		FROM lOT_TableOfAllChanges_A
		WHERE acMasterSlave in ('U')
    OPEN UserTables
	FETCH NEXT FROM UserTables INTO @cTableName
    WHILE (@@fetch_status = 0)
    BEGIN
		SET @Log = @Log + 'User table '+@cTableName+' '+@ChrRet
		SET @sql='IF (exists (SELECT * FROM tempdb.dbo.sysobjects WHERE NAME = ''l'+substring(@cTableName, 2, len(@cTableName)-1)+'_Q'')) and '
		SET @sql=@sql+' (exists (SELECT * FROM dbo.sysobjects WHERE NAME = '''+@cTableName+''')) '
		SET @sql=@sql+' BEGIN'
		SET @sql=@sql+'   TRUNCATE TABLE '+@cTableName
		SET @sql=@sql+'   INSERT INTO '+@cTableName+' SELECT * FROM l'+substring(@cTableName, 2, len(@cTableName)-1)+'_Q'
		SET @sql=@sql+' END'
		EXEC (@sql)
		FETCH NEXT FROM UserTables INTO @cTableName
	END
    CLOSE UserTables
    DEALLOCATE UserTables

    SET @Log = @Log+'Import ended at : '+ cast(getdate() as varchar(50))

    SET @idGUIDTransfer=(select top 1 acIDTransfer from tOT_ANSettingsExchanges WHERE acKeyProfile=@cProfile order by adDateCentralStart desc)

	UPDATE tOT_ANSettingsExchanges
		SET acCentralSuccess  = case when sp.acImportExportType<>'C' then 1 else se.acCentralSuccess end,
			acLocationSuccess = case when sp.acImportExportType='C' then 1 else acLocationSuccess END,
			acImportLog = @Log
	FROM tOT_ANSettingsExchanges se
	INNER JOIN tOT_ANSettingsProfiles sp ON se.acKeyProfile=sp.acKeyProfile
	WHERE se.acIdTransfer=@idGUIDTransfer
    
   DECLARE @TempTableName varchar(50) 
   DECLARE PoorTables CURSOR LOCAL FAST_FORWARD FOR
		SELECT distinct acTableName
		FROM lOT_TableOfAllChanges_A
		WHERE acMasterSlave not in ('U')
    OPEN PoorTables
	FETCH NEXT FROM PoorTables INTO @cTableName
    WHILE (@@fetch_status = 0)
    BEGIN
      SET @TempTableName='l'+substring(@cTableName, 2, len(@cTableName)-1)
      if object_id(@TempTableName) is not null exec pOT_ForeignKeyCorrection @TempTableName, @cTableName
	  FETCH NEXT FROM PoorTables INTO @cTableName
	END	
    CLOSE PoorTables
    DEALLOCATE PoorTables

  IF @nSwitch=0
  BEGIN 
	exec sp_MSforeachtable @command1="ALTER TABLE ? ENABLE TRIGGER ALL"
	exec sp_MSforeachtable @command1="ALTER TABLE ?  CHECK CONSTRAINT ALL"
  END
  IF @nSwitch=1
  BEGIN 
  	exec sp_MSforeachtable @command1="ALTER TABLE ? ENABLE TRIGGER ALL"
  END
  IF @nSwitch=2
  BEGIN 
	exec sp_MSforeachtable @command1="ALTER TABLE ? CHECK CONSTRAINT ALL "
  END
 
  RETURN 0

END









GO
/****** Object:  StoredProcedure [dbo].[pOT_ANImportInsertUpdate]    Script Date: 02/25/2009 19:11:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[pOT_ANImportInsertUpdate]
  @id varchar(38),
  @acTableNameSource varchar(50),
  @acTableNameDestination varchar(50),
  @cType varchar(10)

AS
set nocount on
BEGIN
	DECLARE @acChoosed char(1)
	SELECT @acChoosed=t.acChoosed FROM tOT_AllRecordsImport t WHERE t.agID=@id
	
	declare
		@Sql varchar(8000),
		@SqlInsert varchar(8000),
		@SqlSet varchar(8000),
		@SqlOn varchar(8000),
		@cFieldName varchar(8000)

    DELETE FROM lOT_ListOfColumns

	insert into lOT_ListOfColumns exec ('pOT_ANGetColumns '+@acTableNameDestination+',''PK''')

	set @SqlOn=' '
	declare keys cursor local fast_forward
	for select acFieldName from lOT_ListOfColumns
	open keys
	fetch next from keys into @cFieldName
	while (@@fetch_status = 0)
	begin
		set @SqlOn=@SqlOn+' s.'+@cFieldName+'=d.'+@cFieldName +' and'
		fetch next from keys into @cFieldName
	end
	close keys
	deallocate keys
	if substring(@sqlOn,len(@SqlOn)-2,3)='and'
		set @SqlOn=substring(@SqlOn,1,len(@SqlOn)-3)
	delete from lOT_ListOfColumns

	insert into lOT_ListOfColumns exec ('pOT_ANGetColumns '+@acTableNameDestination+',''ALL''')
	SET @sqlInsert=' '
	SET @sqlSet=' '
	declare keys cursor local fast_forward
	for select acFieldName from lOT_ListOfColumns
	open keys
	fetch next from keys into @cFieldName
	while (@@fetch_status = 0)
	begin
		set @sqlInsert=@sqlInsert+' s.'+@cFieldName+','
		set @sqlSet=@sqlSet+' '+@cFieldName+'=s.'+@cFieldName+','
		fetch next from keys into @cFieldName
	end
	close keys
	deallocate keys
	if substring(@sqlInsert,len(@sqlInsert),1)=','
		set @sqlInsert=substring(@sqlInsert,1,len(@sqlInsert)-1)
	if substring(@sqlSet,len(@sqlSet),1)=','
		set @sqlSet=substring(@sqlSet,1,len(@sqlSet)-1)
    
    DELETE FROM lOT_RecordCount
    
    SET @SQL=('INSERT lOT_RecordCount SELECT count(*) FROM '+@acTableNameDestination+' d JOIN '+@acTableNameSource+' s ON '+ @SqlOn +' WHERE s.id='''+@id+'''')
    EXEC (@SQL) 
    
    IF @acChoosed='1'
    BEGIN
	  IF (SELECT anCount FROM lOT_RecordCount)>0
  	    SET @SQL=' UPDATE '+ @acTableNameDestination + ' SET '+@sqlSet+' FROM '+ @acTableNameDestination + ' d JOIN '+@acTableNameSource+' s ON '+@sqlOn+' WHERE s.id='''+@id+''''
      else
        SET @SQL=' INSERT '+ @acTableNameDestination + ' SELECT '+@sqlInsert+' FROM '+ @acTableNameSource + ' s '+' WHERE s.id='''+@id+''''   	 
    END
    
    IF @acChoosed='2'
    BEGIN
	  IF (SELECT anCount FROM lOT_RecordCount)=0
	    set @SQL=' INSERT '+ @acTableNameDestination + ' SELECT '+@sqlInsert+' FROM '+ @acTableNameSource + ' s '+' WHERE s.id='''+@id+''''
    END    
	EXEC (@SQL)
    DELETE FROM lOT_RecordCount
    DELETE FROM lOT_ListOfColumns
    if @@error=0 update tOT_AllRecordsImport set Imported='Y' where agId=@id
	RETURN @@error
END









GO
/****** Object:  StoredProcedure [dbo].[pOT_ANImportKey]    Script Date: 02/25/2009 19:11:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[pOT_ANImportKey]
  @id varchar(38),
  @acTableNameSource varchar(50),
  @acTableNameDestination varchar(50)
as
set nocount on
declare
    @Sql varchar(8000),
    @SqlSet varchar(8000),
	@SqlOn varchar(3000),
	@SqlOnNew varchar(3000),
    @cFieldName varchar(50)

DELETE FROM lOT_ListOfColumns

insert into lOT_ListOfColumns exec ('pOT_ANGetColumns '+@acTableNameDestination+',''PK''')

set @SqlOn=' '
set @SqlOnNew=' '
SET @sqlSet=' '
declare keys cursor local fast_forward
for select acFieldName from lOT_ListOfColumns
open keys
fetch next from keys into @cFieldName
while (@@fetch_status = 0)
begin
	set @SqlOn=@SqlOn+' s.'+@cFieldName+'= d.'+@cFieldName +' and'
	set @SqlOnNew=@SqlOnNew+' s.New_'+@cFieldName+'= d.'+@cFieldName +' and'
	set @sqlSet=@sqlSet+' '+@cFieldName+'= s.New_'+@cFieldName+','
    fetch next from keys into @cFieldName
end
close keys
deallocate keys
if substring(@sqlOn,len(@SqlOn)-2,3)='and'
	set @SqlOn=substring(@SqlOn,1,len(@SqlOn)-3)
if substring(@sqlOnNew,len(@SqlOnNew)-2,3)='and'
	set @SqlOnNew=substring(@SqlOnNew,1,len(@SqlOnNew)-3)
if substring(@sqlSet,len(@sqlSet),1)=','
	set @sqlSet=substring(@sqlSet,1,len(@sqlSet)-1)

set @SQL='IF exists( SELECT * FROM ' + @acTableNameDestination + ' d JOIN  '+@acTableNameSource+' S ON '+@sqlOnNew+' WHERE s.id='''+@id+''')'
set @SQL=@sql+'   DELETE '+ @acTableNameDestination +' FROM '+ @acTableNameDestination + ' d JOIN '+@acTableNameSource+' S ON '+@sqlOn+' WHERE s.id='''+@id+''''
set @SQL=@sql+' ELSE  '
set @SQL=@sql+'   UPDATE '+ @acTableNameDestination + ' SET '+@sqlSet+' FROM '+ @acTableNameDestination + ' d JOIN '+@acTableNameSource+' S ON '+@sqlOn+' WHERE s.id='''+@id+''''

DELETE FROM lOT_ListOfColumns

exec (@sql)
if @@error<>0
	return 1
else
	return 0









GO
/****** Object:  StoredProcedure [dbo].[pOT_ANImportKeySIFRANTI]    Script Date: 02/25/2009 19:11:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[pOT_ANImportKeySIFRANTI]
  @id varchar(38),
  @acTableNameSource varchar(50),
  @acTableNameDestination varchar(50)
AS
set nocount on
declare
    @cTableName varchar(50),
    @cRefFieldName varchar(50),
	@nCons_object_id int,
	@nCons_object_id_old int,
    @Sql varchar(8000),
    @SqlSet varchar(8000),
	@SqlOn varchar(3000),
    @cFieldName varchar(50)

DELETE FROM lOT_ListOfColumnsSifranti

INSERT INTO lOT_ListOfColumnsSifranti
SELECT
	t.name tablename,
	r.name rerfieldname,
	c.name fieldname,
	k.constid
	FROM sysforeignkeys k
	JOIN sysobjects o ON o.id=k.constid
	JOIN sysobjects t ON t.id=k.fkeyid
	JOIN syscolumns c ON c.Id=k.fkeyid and k.fkey=c.colid
	JOIN syscolumns r ON r.Id=k.rkeyid and k.rkey=r.colid
    WHERE k.rkeyid=object_id(@acTableNameDestination)
	ORDER BY 1,4

set @SqlOn=' '
SET @sqlSet=' '
declare
    @cTableNameOld varchar(50)
declare keys cursor local fast_forward
for select * from lOT_ListOfColumnsSifranti
open keys
fetch next from keys into @cTableName, @cRefFieldName, @cFieldName, @nCons_Object_id
SET @nCons_object_id_old=@nCons_Object_id
SET @cTableNameOld=@cTableName
while (@@fetch_status = 0)
begin
	if @nCons_object_id<>@nCons_object_id_old
	begin
		if substring(@sqlOn,len(@SqlOn)-2,3)='and'
			set @SqlOn=substring(@SqlOn,1,len(@SqlOn)-3)
		if substring(@sqlSet,len(@sqlSet),1)=','
			set @sqlSet=substring(@sqlSet,1,len(@sqlSet)-1)
		--set @sql=     ' ALTER TABLE '+@cTableNameOld+' NOCHECK CONSTRAINT ALL '
		set @SQL=@SQL+' UPDATE '+ @cTableNameOld + ' SET '+@sqlSet+' FROM '+ @cTableNameOld + ' d JOIN '+@acTableNameSource+' s ON '+@sqlOn+' WHERE s.id='''+@id+''''
		--set @sql=@sql+' ALTER TABLE '+@cTableNameOld+' CHECK CONSTRAINT ALL '
		SET @nCons_object_id_old=@nCons_Object_id
		SET @cTableNameOld=@cTableName
		exec (@sql)
		set @SqlOn=' '
		SET @sqlSet=' '
		if @@error<>0 return 1
	end
	set @SqlOn=@SqlOn+' s.'+@cRefFieldName+'=d.'+@cFieldName +' and'
	set @sqlSet=@sqlSet+' '+@cFieldName+'= s.New_'+@cRefFieldName+','
	fetch next from keys into @cTableName, @cRefFieldName, @cFieldName, @nCons_Object_id
end
if substring(@sqlOn,len(@SqlOn)-2,3)='and'
	set @SqlOn=substring(@SqlOn,1,len(@SqlOn)-3)
if substring(@sqlSet,len(@sqlSet),1)=','
	set @sqlSet=substring(@sqlSet,1,len(@sqlSet)-1)
SET @Sql=' '
set @SQL=@SQL+' UPDATE '+ @cTableNameOld + ' SET '+@sqlSet+' FROM '+ @cTableNameOld + ' d JOIN '+@acTableNameSource+' s ON '+@sqlOn+' WHERE s.id='''+@id+''''
exec (@sql)
if @@error<>0 return 1
close keys
deallocate keys
DELETE from lOT_ListOfColumnsSifranti
set @acTableNameDestination=@acTableNameDestination
EXEC pot_ANImportKey @Id, @acTableNameSource, @acTableNameDestination
if @@error<>0
	return 1
else
begin
    update tOT_AllRecordsImport set Imported='Y' where agId=@id
	return 0
end









GO
/****** Object:  StoredProcedure [dbo].[pOT_ANImportPrepareTableList]    Script Date: 02/25/2009 19:11:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[pOT_ANImportPrepareTableList]
	@cProfile char(3),
	@nSwitch int
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE
		@idGUIDTransfer varchar(38),
		@Log varchar(8000),
		@ChrRet varchar(10),
		@Return_status int
    if object_id('tOT_AllRecordsImport') is not null exec('drop table tOT_AllRecordsImport')
	SET @ChrRet = char(13)
    SET @Log = 'Import started at : '+ cast(getdate() as varchar(50)) + ' Profile: ' + @cProfile +@ChrRet
	exec @Return_status = [dbo].[pOT_ANSetExchanges]	@cProfile ,'I', @idGUIDTransfer OUTPUT
    if @Return_status<>0
		RETURN @Return_status
	
	SET @Log = @Log + 'Cycle : '+ cast(@idGUIDTransfer as varchar(50))+@ChrRet
	IF not exists (SELECT * FROM sysobjects WHERE NAME = 'lOT_TableOfAllChanges_A')
		return 5

	SELECT DISTINCT
		CASE WHEN acOTOSType = 'D' THEN 2*anLevel
			 WHEN acOTOSType = 'I' THEN -2*anLevel
			 WHEN acOTOSType = 'U' THEN -1*anLevel
			 WHEN acOTOSType = 'K' THEN 1*anLevel
			 ELSE 0
		END as anSortOder,
		*,
        'F' Imported
		INTO tOT_AllRecordsImport
		FROM lOT_TableOfAllChanges_A
		WHERE acMasterSlave in ('M','S')
	 ORDER BY	adTimeChg, 1
	
  IF @nSwitch=0
  BEGIN 
  	exec sp_MSforeachtable @command1="ALTER TABLE ? DISABLE TRIGGER ALL"
	exec sp_MSforeachtable @command1="ALTER TABLE ? NOCHECK CONSTRAINT ALL "
  END
  IF @nSwitch=1
  BEGIN 
  	exec sp_MSforeachtable @command1="ALTER TABLE ? DISABLE TRIGGER ALL"
  END
  IF @nSwitch=2
  BEGIN 
	exec sp_MSforeachtable @command1="ALTER TABLE ? NOCHECK CONSTRAINT ALL "
  END

END









GO
/****** Object:  StoredProcedure [dbo].[pOT_ANManageProfiles]    Script Date: 02/25/2009 19:11:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[pOT_ANManageProfiles]
  @acKeyProfile char(3),
  @acType char(1),
  @acErrorMessage varchar(50) output
as
set nocount on
set @acErrorMessage='OK'

if @acType='I'
begin
  if exists(select * from tOT_ANSettingsProfiles where acKeyProfile=@acKeyProfile)
  begin
    set @acErrorMessage='Profil za to številko že obstaja!'
    return
  end
  if exists(select * from tOT_ANSettingsProfiles)
  begin
    declare
		@acKeyExist char(3),
		@acProfileName varchar(50)
    select	top 1
			@acKeyExist=acKeyProfile,
			@acProfileName='Copy of '+acProfileName
			from tOT_ANSettingsProfiles
			order by adDateProfileCreated desc
	insert into dbo.tOT_ANSettingsProfiles(acKeyProfile, acProfileName, acActive, acLoginServer, acLoginDataBase, acLoginUserName, acLoginPassword, acImportExportType, acUseEmailOrFTP, acNotExport, acTableTransfer, acOnlyThisProfile, adDateDelRecords, adDateStatCentral, adDateStatLocation, adDateInitialState, anTransferCentral, anTransferLocation, acFileLog, acFileWorkDir, acDirOrMedia, acPOPServer, anPOPPort, acPOPUseSSL, acSMTPServer, anSMTPPort, acSMTPUseSSL, anTimeOut, acLogConnectionErrors, acEmailAddress, acFromEmailAddress, acUserName, acPassword, acPasswordConfirm, acSendEmail, acEmailType, acEmailStatus1, acEmailStatus2, acEmailPreserveDomain, acEmailClearOldMess, acEmailReportMessErrors, adDateProfileCreated, acLog)
    select @acKeyProfile, @acProfileName+' '+@acKeyProfile,'N'
      ,acLoginServer ,acLoginDataBase,acLoginUserName
      ,acLoginPassword,acImportExportType,acUseEmailOrFTP
      ,acNotExport,acTableTransfer,acOnlyThisProfile
      ,adDateDelRecords,adDateStatCentral,adDateStatLocation
      ,adDateInitialState,anTransferCentral
      ,anTransferLocation,acFileLog,acFileWorkDir,acDirOrMedia
      ,acPOPServer,anPOPPort,acPOPUseSSL,acSMTPServer
      ,anSMTPPort,acSMTPUseSSL,anTimeOut,acLogConnectionErrors
      ,acEmailAddress,acFromEmailAddress,acUserName
      ,acPassword,acPasswordConfirm,acSendEmail,acEmailType
      ,acEmailStatus1,acEmailStatus2,acEmailPreserveDomain
      ,acEmailClearOldMess,acEmailReportMessErrors,adDateProfileCreated,acLog
	from tOT_ANSettingsProfiles
	where acKeyProfile=@acKeyExist

	insert into tOT_ANSettingsUserTables
           (acKeyProfile, acTableName, acTableChoosed)
	select
			@acKeyProfile, acTableName, acTableChoosed
			from tOT_ANSettingsUserTables
			where acKeyProfile=@acKeyExist
		

	insert into tOT_ANSettingsTables
			(acKeyProfile, acChoosed, anIdGuidOtos)
	select @acKeyProfile,  acChoosed, anIdGuidOtos
			from tOT_ANSettingsTables
			where acKeyProfile=@acKeyExist



  end else
  begin
	insert tOT_ANSettingsProfiles
		(acKeyProfile, acProfileName, acActive)
	values
		(@acKeyProfile, 'Nedefiniran profil '+@acKeyProfile, 'N')
	insert into tOT_ANSettingsUserTables
           (acKeyProfile, acTableName, acTableChoosed)
	select
		@acKeyProfile,
		t.table_name,
		0
	from information_schema.tables t
	where table_type = 'BASE TABLE'
		and not exists
		(select * from
			tpa_SysTables s
			where s.acTableName = t.table_name)
			and t.table_name not like '%_o'

	insert into tOT_ANSettingsTables
			(acKeyProfile, acChoosed, anIdGuidOtos)
		select @acKeyProfile, 0, anIdGuidOtos
		From tOT_ImportExport
  end

end

if @acType='D'
BEGIN
  DELETE FROM dbo.tOT_ANSettingsExchanges where acKeyProfile=@acKeyProfile
  delete from tOT_ANSettingsUserTables where acKeyProfile=@acKeyProfile
  delete from tOT_ANSettingsTables where acKeyProfile=@acKeyProfile
  delete from tOT_ANSettingsFileExport where acKeyProfile=@acKeyProfile
  delete from tOT_ANSettingsProfiles where acKeyProfile=@acKeyProfile
  DELETE FROM tOT_ANDocTypeChoosed WHERE acKeyProfile=@acKeyProfile
end

EXEC pOT_ANRefreshDocTypeChoosed @acKeyProfile, ''









GO
/****** Object:  StoredProcedure [dbo].[pOT_ANManageTempTables]    Script Date: 02/25/2009 19:11:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[pOT_ANManageTempTables] --pOT_ANManageTempTables 'C' exec pOT_ANManageTempTables 'D'
  @cWhatToDo char(1)-- C create, D delete
as

declare cur_TempTable cursor local fast_forward
for 
  select distinct acTableName
  FROM lOT_TemporaryTableList where acTableName<>'' 
  and acTableName not in (SELECT   OBJECT_NAME(OBJECT_ID) FROM SYS.IDENTITY_COLUMNS)

	--lOT_AllTablesExport, lOT_TempExceptionTables, lOT_ImpExpSet polni procedura pOT_ANPrepareAllTablesForExport

declare 
	@cTableName varchar(30),
	@cSQL varchar(8000)

open cur_TempTable
fetch next from cur_TempTable into @cTableName
while (@@fetch_status=0)
begin
  set @cSQL = 'select cast(null as varchar(38)) as id, cast(null as varchar(50)) as acTableName, cast(null as char(1)) as acOTOSType, * into ' + 
				stuff(@cTableName, 1, 1, 'l') + '_A from ' + @cTableName + ' where 1 = 2 ' 
  if exists(select * from sysobjects where name=@cTableName+'_O')
    set @cSQL = @cSQL + ' select cast(null as varchar(38)) as id, cast(null as varchar(50)) as acTableName, *' + 				
				' into ' + stuff(@cTableName, 1, 1, 'l') + '_O from ' + @cTableName + '_O where 1 = 2 '  
  
  --Če je ukaz Delete 
  if @cWhatToDo='D' 
  begin
    set @cSQL=  ' if exists(select * from sysobjects where name='''+stuff(@cTableName, 1, 1, 'l') + '_A'')'+
	            ' drop table '+stuff(@cTableName, 1, 1, 'l') + '_A' 
    print @cSQL
    exec (@cSQL)
    
    DECLARE @cSQL1 varchar(8000)
    set @cSQL1=  ' if exists(select * from sysobjects where name='''+stuff(@cTableName, 1, 1, 'l')+'_O'')'+
				' drop table '+stuff(@cTableName, 1, 1, 'l')+'_O' 
  
    print @cSQL1
    exec (@cSQL1)
    --Brišemo še lOT_ANSettingsExchanges da se lahko požene drugi izvoz...
    delete from lOT_ANSettingsExchanges
  end else
  BEGIN	
    exec (@cSQL)
    --Otos tabele ustvarjanje začasnih
    IF NOT EXISTS (select * from sysobjects where name=stuff(@cTableName, 1, 1, 'l')+'_O')
      EXEC pOT_ANImportCreateTempOTOS @cTableName
    ELSE 
  	BEGIN
  	  SET @cSQL='DELETE FROM '+stuff(@cTableName, 1, 1, 'l')+'_O'
  	  EXEC(@cSQL)
  	END  
  END
  	
  fetch next from cur_TempTable into @cTableName

end

close cur_TempTable
deallocate cur_TempTable

--Uporabniške tabele...
declare cur_UserTable cursor local fast_forward
  for select * from lOT_UserTableList
--Tabelo lOT_UserTableList polni procedura pOT_ANPrepareAllTablesForExport
DECLARE
  @UserTablesTimeFields char(1),
  @UserTablesKeyFields char(1)
open cur_UserTable
fetch next from cur_UserTable into @cTableName
while (@@fetch_status=0)
begin
  SET @UserTablesTimeFields='N'
  SET @UserTablesKeyFields='N' 
  DELETE FROM lOT_Columns1
  INSERT lOT_Columns1
  EXEC dbo.pOT_ANGetColumns @cTableName, 'NOPK'      
  IF EXISTS (SELECT * FROM lOT_Columns1 WHERE ColName='adTimeIns')
     AND EXISTS (SELECT * FROM lOT_Columns1 WHERE ColName='adTimeChg') SET @UserTablesTimeFields='D'
  DELETE FROM lOT_Columns1
  INSERT lOT_Columns1 EXEC dbo.pOT_ANGetColumns @cTableName, 'PK'      
  IF EXISTS (SELECT * FROM lOT_Columns1) SET @UserTablesKeyFields='D'
  --Če obstajata TimeFields in KeyFields potem se ustvarja drugačna tabela...
  IF (@UserTablesTimeFields='D') AND (@UserTablesKeyFields='D')  
    set @cSQL=' if not exists(select * from sysobjects where name='''+stuff(@cTableName, 1, 1, 'l')+'_Q'')'+
  	          ' SELECT  cast(null as varchar(38)) as id, cast(null as varchar(50)) as acTableName, cast(null as char(1)) as acOTOSType, * into '+stuff(@cTableName, 1, 1, 'l')+'_Q from '+@cTableName+' where 1=2 '
  ELSE
  	set @cSQL=' if not exists(select * from sysobjects where name='''+stuff(@cTableName, 1, 1, 'l')+'_Q'')'+
  	          ' SELECT * into '+stuff(@cTableName, 1, 1, 'l')+'_Q from '+@cTableName+' where 1=2 '  	        
  if @cWhatToDo='D' set @cSQL=' if exists(select * from sysobjects where name='''+stuff(@cTableName, 1, 1, 'l')+'_Q'')'+
							  ' DROP TABLE '+stuff(@cTableName, 1, 1, 'l')+'_Q'
  PRINT @cSQL
  exec (@cSQL)
  fetch next from cur_UserTable into @cTableName
end

close cur_UserTable
deallocate cur_UserTable











GO
/****** Object:  StoredProcedure [dbo].[pOT_ANManageTriggers]    Script Date: 02/25/2009 19:11:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[pOT_ANManageTriggers]
as
set nocount on
DECLARE
@cAllTableNames varchar(8000),
@cDocType varchar(8000)

DELETE FROM lOT_TablesTriggers

INSERT lOT_TablesTriggers
SELECT distinct *
		FROM
		(
  		 SELECT distinct ie.acTYPE,ie.anIdGuidOtos, ie.acTables, ie.acdocType
		 FROM tOT_ANSettingsTables st
		 JOIN tOT_ImportExport ie ON st.anIdGuidOtos = ie.anIdGuidOtos
		 WHERE st.acChoosed IN ('1', '2')
		) a

    DELETE FROM lOT_DocSet   
	DELETE FROM lOT_DocAll
	
	DECLARE
		@cTMPTablesDoc varchar(8000),
		@acRoot varchar(50)
	
	DECLARE DocTypeofKind CURSOR LOCAL FAST_FORWARD FOR
    SELECT DISTINCT acTables, t.acRoot FROM tPA_SysDocTypeOfKind t
    JOIN tOT_ANDocTypeChoosed ton ON ton.acSetOf = t.acSetOf AND ton.acType = t.acType
    WHERE ton.acChoosed IN ('1', '2')

	OPEN DocTypeofKind
	FETCH NEXT FROM DocTypeofKind INTO @cTMPTablesDoc, @acRoot
	WHILE (@@fetch_status = 0)
    BEGIN
		EXEC pPA_ParseSemiColToTable 'lOT_DocSet', 'acTableName', @cTMPTablesDoc
		INSERT lOT_DocAll SELECT acTableName FROM lOT_DocSet
		DELETE FROM lOT_DocSet
		IF NOT EXISTS (SELECT * FROM lOT_DocAll WHERE acTableName=@acRoot) INSERT lOT_DocAll VALUES (@acRoot)
		FETCH NEXT FROM DocTypeofKind INTO @cTMPTablesDoc, @acRoot
	END
    CLOSE DocTypeofKind
    DEALLOCATE DocTypeofKind

    DELETE FROM lOT_DocSet
    DELETE FROM lOT_ImpExpAll 

	DECLARE
		@cTMPTableName varchar(100),
		@cTMPTables varchar(8000)
	
	DECLARE ImportExportSet CURSOR LOCAL FAST_FORWARD FOR
    SELECT distinct acTableName, acTables FROM tOT_ImportExportSet
	OPEN ImportExportSet
	FETCH NEXT FROM ImportExportSet INTO @cTMPTableName, @cTMPTables
	WHILE (@@fetch_status = 0)
    BEGIN
		EXEC pPA_ParseSemiColToTable 'lOT_DocSet', 'acTableName', @cTMPTables
		INSERT lOT_ImpExpAll SELECT @cTMPTableName, acTableName FROM lOT_DocSet
		DELETE FROM lOT_DocSet
		FETCH NEXT FROM ImportExportSet INTO @cTMPTableName, @cTMPTables
	END
    CLOSE ImportExportSet
    DEALLOCATE ImportExportSet
    DELETE FROM lOT_DocSet
    DELETE FROM lOT_ANFK
		INSERT lOT_ANFK SELECT DISTINCT a.acTableName FROM
		(
		SELECT t.acTables AS acTableName
		FROM lOT_TablesTriggers atn
		join lOT_ImpExpAll t on t.acTableName=atn.acTables
		UNION
		SELECT atn.acTables AS acTableName
		FROM lOT_TablesTriggers atn
		UNION
		SELECT d.acTableName AS acTableName
		FROM lOT_DocAll d
		)  a

declare @TableName varchar(50)

declare TabeleSet cursor LOCAL FAST_FORWARD for
SELECT DISTINCT acTableName FROM lOT_ANFK

open TabeleSet
fetch next from TabeleSet into @TableName
while (@@fetch_status=0)
begin
  if not exists (select * from SysObjects where name='l'+substring(@TableName, 2, len(@TableName+'_O')))
    exec pOT_TableChangesLog @TableName
  fetch next from TabeleSet into @TableName
end
close TabeleSet
deallocate TabeleSet

declare TabeleReset cursor LOCAL FAST_FORWARD for
SELECT DISTINCT name FROM sysobjects so
WHERE type = 'U'
AND NOT EXISTS (select * from lOT_ANFK where acTableName=so.name)
AND EXISTS
(SELECT DISTINCT name
 FROM sysobjects
 LEFT OUTER JOIN lOT_ANFK ON acTableName<>name
 WHERE type = 'U' AND
 name = so.name+'_O'
)
open TabeleReset
fetch next from TabeleReset into @TableName
while (@@fetch_status=0)
begin
  exec pOT_TableChangesDropTrig @TableName
  exec ('drop table '+@TableName+'_O')
  fetch next from TabeleReset into @TableName
end
close TabeleReset
deallocate TabeleReset

DELETE from lOT_ANFK









GO
/****** Object:  StoredProcedure [dbo].[pOT_ANPrepareAllDocsForExport]    Script Date: 02/25/2009 19:11:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[pOT_ANPrepareAllDocsForExport]
@acProfile char(3)
AS
set nocount on
DECLARE	
	@acSetOf char(1),
	@acType char(1),
	@acDocType char(4),
	@dDateOfLastReplicationFROM smalldatetime,
    @dDateOfLastReplicationTo smalldatetime
	
  SELECT @dDateOfLastReplicationFROM=adDateCentralStart, @dDateOfLastReplicationTO=adDateCentralFinish
		FROM tOT_ANSettingsExchanges
		WHERE acIdTransfer=(SELECT TOP 1 acIdTransfer FROM 
							tOT_ANSettingsExchanges 
							WHERE acKeyProfile=@acProfile 
		                    ORDER BY adDateCentralStart DESC)

  DECLARE AllDocTables CURSOR LOCAL FAST_FORWARD FOR
  SELECT DISTINCT
    tatc.acSetOf,
    tatc.acType,
    tatc.acDocType,
    tatc.acChoosed
  FROM tOT_ANDocTypeChoosed tatc
  INNER JOIN tPA_SysDocTypeOfKind tpa ON tpa.acSetOf = tatc.acSetOf AND tpa.acType = tatc.acType
  WHERE
	acKeyProfile=@acProfile AND
	tatc.acChoosed IN ('1', '2')
  
  DECLARE 
  	@cChoosed char(1)
  	
  OPEN AllDocTables
  FETCH NEXT FROM AllDocTables INTO @acSetOf, @acType, @acDocType, @cChoosed
  WHILE (@@fetch_status = 0)
  BEGIN
  	EXEC pOT_ANExportOneDocType @acProfile, @acSetOf, @acType, @acDocType, @dDateOfLastReplicationFROM, @dDateOfLastReplicationTo, @cChoosed
  	FETCH NEXT FROM AllDocTables INTO @acSetOf, @acType, @acDocType, @cChoosed
  END

  CLOSE AllDocTables
  DEALLOCATE AllDocTables









GO
/****** Object:  StoredProcedure [dbo].[pOT_ANPrepareAllTablesForExport]    Script Date: 02/25/2009 19:11:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[pOT_ANPrepareAllTablesForExport] 
	@cProfile char(3)
AS

BEGIN
	-- S tem preprečimo hkratni zagon več exportov...
	IF EXISTS(SELECT * FROM lOT_ANSettingsExchanges) return 9
	
	SET NOCOUNT ON;
	DECLARE
		@idGUIDTransfer varchar(38),
		@Log varchar(8000),
		@ChrRet varchar(10),
		@Return_status int
    

	DELETE FROM dbo.tOT_ANSettingsExchanges WHERE acCentralSuccess='0' AND dbo.tOT_ANSettingsExchanges.acLocationSuccess='0'
	SET @ChrRet = char(13)
    SET @Log = 'Export started at : '+ cast(getdate() as varchar(50)) + ' Profile: ' + @cProfile +@ChrRet
	
	exec @Return_status = [dbo].[pOT_ANSetExchanges]	@cProfile ,'E', @idGUIDTransfer OUTPUT
	PRINT @Return_status
	if @Return_status<>0
	  return @Return_status	
	
    if not exists(select * from	dbo.tOT_ANSettingsUserTables where acKeyProfile=@cProfile AND dbo.tOT_ANSettingsUserTables.acTableChoosed in (1,2))
       AND NOT EXISTS (SELECT * FROM dbo.tOT_ANSettingsTables WHERE acKeyProfile=@cProfile AND acChoosed in ('1', '2'))
       AND NOT EXISTS (SELECT * FROM tOT_ANDocTypeChoosed tatc WHERE tatc.acKeyProfile=@cProfile AND tatc.acChoosed in ('1','2'))
	BEGIN
	  DELETE FROM lOT_ANSettingsExchangesINI
	  IF (SELECT acImportExportType FROM tOT_ANSettingsProfiles
		  WHERE acKeyProfile=@cProfile)='C'
		  UPDATE tOT_ANSettingsExchanges SET acCentralSuccess=1 WHERE acIdTransfer=@idGUIDTransfer
			ELSE
		  UPDATE tOT_ANSettingsExchanges SET acLocationSuccess=1 WHERE acIdTransfer=@idGUIDTransfer
			EXEC('insert lOT_ANSettingsExchangesINI SELECT * FROM tOT_ANSettingsExchanges WHERE acIdTransfer='''+@idGUIDTransfer+'''')
	  return 8
	END

	SET @Log = @Log + 'Cycle : '+ isnull(cast(@idGUIDTransfer as varchar(50)), ' ')+@ChrRet
	
    DELETE FROM lOT_TableOfAllChanges_A

	DECLARE
		@cAllTableNames varchar(8000),
		@cDocType varchar(8000)
	
    DELETE FROM lOT_AllTablesChoosed
    
    INSERT lOT_AllTablesChoosed
    SELECT distinct ie.acTYPE,ie.anIdGuidOtos, acTables, acdocType, st.acChoosed
    FROM tOT_ANSettingsTables st
	JOIN tOT_ImportExport ie ON st.anIdGuidOtos = ie.anIdGuidOtos
	WHERE st.acKeyProfile=@cProfile and st.acChoosed in ('1', '2') AND len(isnull(acTables, ''))<>''

    delete from lOT_TemporaryTableList
    insert lOT_TemporaryTableList select acTables from lOT_AllTablesChoosed
    --where acTables not in (SELECT   OBJECT_NAME(OBJECT_ID) FROM SYS.IDENTITY_COLUMNS)

    DELETE FROM lOT_ImpExpSet

    DELETE FROM lOT_ImpExpAll
    
	DECLARE
		@cTMPTableName varchar(100),
		@cTMPTables varchar(8000)
	
	DECLARE ImportExportSet CURSOR LOCAL FAST_FORWARD FOR
    SELECT distinct acTableName, acTables FROM tOT_ImportExportSet
	OPEN ImportExportSet
	FETCH NEXT FROM ImportExportSet INTO @cTMPTableName, @cTMPTables
	WHILE (@@fetch_status = 0)
    BEGIN
		EXEC pPA_ParseSemiColToTable 'lOT_ImpExpSet', 'acTableName', @cTMPTables
		INSERT lOT_ImpExpAll SELECT @cTMPTableName, acTableName FROM lOT_ImpExpSet
        INSERT lOT_TemporaryTableList SELECT acTableName FROM lOT_ImpExpSet
		DELETE FROM lOT_ImpExpSet
		FETCH NEXT FROM ImportExportSet INTO @cTMPTableName, @cTMPTables
	END
    CLOSE ImportExportSet
    DEALLOCATE ImportExportSet
    
    DELETE FROM lOT_ImpExpSet
    DELETE FROM lOT_DocSet
    DELETE FROM lOT_DocAllChoosed
    
	DECLARE
		@cTMPTablesDoc varchar(8000),
		@cChoosed char(1),
		@acRoot varchar(30)
	
	DECLARE DocTypeOfKind CURSOR LOCAL FAST_FORWARD FOR
    SELECT DISTINCT t.acTables, ton.acChoosed, t.acRoot FROM tPA_SysDocTypeOfKind t
    JOIN tOT_ANDocTypeChoosed ton ON ton.acSetOf = t.acSetOf AND ton.acType = t.acType AND ton.acKeyProfile=@cProfile
    WHERE ton.acChoosed in ('1', '2')
	OPEN DocTypeOfKind
	FETCH NEXT FROM DocTypeOfKind INTO @cTMPTablesDoc, @cChoosed, @acRoot
	WHILE (@@fetch_status = 0)
    BEGIN
		EXEC pPA_ParseSemiColToTable 'lOT_DocSet', 'acTableName', @cTMPTablesDoc
		INSERT lOT_DocAllChoosed SELECT acTableName, @cChoosed FROM lOT_DocSet
        INSERT lOT_TemporaryTableList SELECT acTableName FROM lOT_DocSet
        
        if not exists(select * from lOT_TemporaryTableList where acTableName=@acRoot)
           INSERT lOT_TemporaryTableList values (@acRoot)
           
        if not exists(select * from lOT_DocAllChoosed where acTableName=@acRoot)           
           INSERT lOT_DocAllChoosed values (@acRoot, 1)
           
		DELETE FROM lOT_DocSet
		FETCH NEXT FROM DocTypeOfKind INTO @cTMPTablesDoc, @cChoosed, @acRoot
	END
    CLOSE DocTypeOfKind
    DEALLOCATE DocTypeOfKind
    
    DELETE FROM lOT_DocSet
    DELETE FROM lOT_ANFK_1 --#tmptmp_ANfk
    
		INSERT lOT_ANFK_1
		SELECT DISTINCT a.acTableName, a.acChoosed FROM
		(
		SELECT t.acTables AS acTableName, atn.acChoosed
		FROM lOT_AllTablesChoosed atn
		join lOT_ImpExpAll t on t.acTableName=atn.acTables
		UNION
		SELECT atn.acTables AS acTableName, acChoosed
		FROM lOT_AllTablesChoosed atn
		UNION
		SELECT d.acTableName AS acTableName, acChoosed 
		FROM lOT_DocAllChoosed d
		)  a

	DELETE FROM lOT_ANFK_2 --#tmp_ANfk
	
	INSERT lOT_ANFK_2
	SELECT acTableName, min(acChoosed) acChoosed
	FROM lOT_ANFK_1
	GROUP BY acTableName
    
    DELETE FROM lOT_ANFK_1
    
	DELETE FROM lOT_AllTablesExport --#AllTables
	INSERT lOT_AllTablesExport
	SELECT distinct
			ie.acTYPE,
			t.acTableName,
			acMasterTable=(SELECT CASE WHEN EXISTS (SELECT * FROM tOT_ImportExport WHERE acTables=t.acTableName) THEN '1' ELSE '0' END),
			1 AS anLevel,
			t.acChoosed
	FROM lOT_ANFK_2  t
	LEFT OUTER JOIN tOT_ImportExport ie ON ie.acTABLES=t.acTableName

	DELETE from lOT_AllTablesChoosed	
	DELETE FROM lOT_ANFK_2

    --delete from lOT_AllTablesExport
    --where acTableName in (SELECT   RTrim(OBJECT_NAME(OBJECT_ID)) FROM SYS.IDENTITY_COLUMNS)

	DECLARE @nNoOfTables int
	DELETE FROM lOT_TempExceptionTables --#TempExceptionTables	
    INSERT lOT_TempExceptionTables
        select distinct b.name AS acTableName from sysobjects b
		inner join syscolumns c on b.id=c.id
		where exists (select * from lOT_AllTablesExport where acTableName=b.name )
			  and (not exists (select * from syscolumns where b.id=id and upper(name)='ADTIMEINS') or
			  not exists (select * from syscolumns where b.id=id and upper(name)='ADTIMECHG'))
	
	SELECT @nNoOfTables=count(*) FROM lOT_TempExceptionTables	
	IF isnull(@nNoOfTables,0)>0
	BEGIN
		DECLARE @TableExceptionName varchar(50),
				@ExceptionTablesList varchar(5000)
		DECLARE ExceptionTables CURSOR  for
		  SELECT * FROM lOT_TempExceptionTables
		OPEN ExceptionTables
		FETCH NEXT FROM ExceptionTables INTO @TableExceptionName
		WHILE (@@fetch_status = 0)
		BEGIN
		  SET @ExceptionTablesList=@ExceptionTablesList+@TableExceptionName+', '	
		  FETCH NEXT FROM ExceptionTables INTO @TableExceptionName
		END
		CLOSE ExceptionTables
		DEALLOCATE ExceptionTables
		SET @Log = @Log+'INFORMATON: Choosen tables, does not contain fields ADTIMEINS and ADTIMECHG! Table list: '+isnull(@ExceptionTablesList, 'No such a tables!')
	END
	
	DECLARE
 		@cType varchar(10),
		@cMasterTableName varchar(50),
		@cTableName varchar(50),
		@cMasterSlave char(1),
		@nLevel int,
		@dDateOfLastReplicationTo datetime,
		@dDateOfLastReplicationFrom datetime,
		@gIdGuidOtos uniqueidentifier,
		@nCount int

	SELECT @dDateOfLastReplicationFROM=adDateCentralStart, @dDateOfLastReplicationTO=adDateCentralFinish
		FROM tOT_ANSettingsExchanges
		WHERE acIdTransfer=@idGUIDTransfer
    
    DECLARE @cFinalChoosed char(1)
    -----------------------------------------------------------------------------------------------------------
    --Pripravimo še seznam uporabniških tabel pred klicem procedure za ustvarjanje temp tabel
    delete from lOT_UserTableList
    insert lOT_UserTableList 
		SELECT acTableName FROM tOT_ANSettingsUserTables WHERE 
        acTableChoosed IN ('1', '2') and acKeyProfile=@cProfile 
        --acTableName not in (SELECT   OBJECT_NAME(OBJECT_ID) FROM SYS.IDENTITY_COLUMNS)

	DELETE FROM lOT_AllTablesExport WHERE acTableName='tHE_BCPackage'
	--Ustvarjamo začasne tabele...
    exec pOT_ANManageTempTables 'C'
	-----------------------------------------------------------------------------------------------------------
	DECLARE AllTables CURSOR LOCAL FAST_FORWARD FOR
		SELECT acType, rtrim(ltrim(acTableName)), case when acMasterTable='1' then 'M' else 'S' end as acMasterSlave, anLevel, acChoosed
        FROM lOT_AllTablesExport als  
		WHERE rtrim(ltrim(acTableName)) NOT IN (SELECT acTableName FROM lOT_TempExceptionTables) 
		ORDER BY 4 desc, 2, 3
	OPEN AllTables
	FETCH NEXT FROM AllTables INTO @cType, @cTableName, @cMasterSlave, @nLevel, @cFinalChoosed
    WHILE (@@fetch_status = 0)
    BEGIN
      IF @cMasterSlave='S'
      BEGIN
		SET @cMasterTableName=(SELECT TOP 1 t.acTableName
		FROM lOT_ImpExpAll t
		WHERE t.acTables=@cTableName)
      END
      ELSE SET @cMasterTableName = @cTableName
      IF EXISTS(SELECT * FROM sysobjects s WHERE s.name=@cTableName)
	  begin
        print isnull(@cMasterTableName, 'X')+', '+isnull(@cTableName, 'X')
        ----------------------------------------------------------------------------------------
        ----------------------------------------------------------------------------------------
        exec pOT_ANPrepareTableForExport @cType, @cMasterTableName, @cTableName, @cMasterSlave, 
										   @nLevel, @cFinalChoosed, 
										   @dDateOfLastReplicationFrom, @dDateOfLastReplicationTo
        ----------------------------------------------------------------------------------------										   
         ----------------------------------------------------------------------------------------
      end

		SELECT @nCount=count(*) FROM lOT_TableOfAllChanges_A t WHERE t.acTableName =@cTableName
		SET @Log = @Log+'   Table: '+ isnull(@cTableName, ' ') + ' Records exported: ' +isnull(cast(@nCount as varchar(100)), ' ')+ @ChrRet


		FETCH NEXT FROM AllTables INTO @cType, @cTableName, @cMasterSlave, @nLevel, @cFinalChoosed
    END
	CLOSE AllTables
	DEALLOCATE AllTables
    print 'TEST'
    DELETE FROM lOT_AllTablesSifranti
    INSERT lOT_AllTablesSifranti SELECT * FROM lOT_AllTablesExport
       
    --Izvoz dokumentov...
    EXEC pOT_ANPrepareAllDocsForExport @cProfile

	DECLARE
		@UserTableChoosed char(1),
		@UserTableSQL varchar(6000),
		@UserTablesTimeFields char(1),
		@UserTablesKeyFields char(1)
		
    --Export uporabniških tabel...
	DECLARE UserTables CURSOR LOCAL FAST_FORWARD FOR
		SELECT acTableName, acTableChoosed
		FROM tOT_ANSettingsUserTables WHERE acTableChoosed IN ('1', '2') and acKeyProfile=@cProfile
	OPEN UserTables
	FETCH NEXT FROM UserTables INTO @cTableName, @UserTableChoosed
    WHILE (@@fetch_status = 0)      
    BEGIN
    	SET @UserTablesTimeFields='N'
    	SET @UserTablesKeyFields='N' 
    	--CREATE TABLE #TmpColumns1 (ColName varchar(50))
    	DELETE FROM lOT_Columns1
        INSERT lOT_Columns1
        EXEC dbo.pOT_ANGetColumns @cTableName, 'NOPK'      
        IF EXISTS (SELECT * FROM lOT_Columns1 WHERE ColName='adTimeIns')
           AND EXISTS (SELECT * FROM lOT_Columns1 WHERE ColName='adTimeChg') SET @UserTablesTimeFields='D'
        DELETE FROM lOT_Columns1
        --CREATE TABLE #TmpColumns2 (ColName varchar(50))
        INSERT lOT_Columns1
        EXEC dbo.pOT_ANGetColumns @cTableName, 'PK'      
        IF EXISTS (SELECT * FROM lOT_Columns1) SET @UserTablesKeyFields='D'
        DELETE FROM lOT_Columns1 
        IF (@UserTablesTimeFields='D') AND (@UserTablesKeyFields='D')
        BEGIN
			SET @Log = @Log + 'User table '+isnull(@cTableName, 'No table')+' '+isnull(@cType, 'No type') +@ChrRet
			SET @UserTableSQL='delete from l'+substring(@cTableName, 2, len(@cTableName)-1)+'_Q'
			EXEC (@UserTableSQL)
			SET @UserTableSQL=
			' INSERT l'+substring(@cTableName, 2, len(@cTableName)-1)+'_Q'+
			' SELECT newid() as Id, '''+@cTableName+''' as acTableName, ''I'' as acOTOSType,'+
			      ' *  FROM '+@cTableName+
			     ' WHERE '+
               ' adTimeIns between '+
					' convert(datetime,'''+convert(varchar(50),@dDateOfLastReplicationFROM,112)+  ' ' + convert(varchar(50),@dDateOfLastReplicationFROM,114) + ''') and '+
					' convert(datetime,'''+convert(varchar(50),@dDateOfLastReplicationTO,112)+  ' ' + convert(varchar(50),@dDateOfLastReplicationTO,114) + ''')'+
               '  or adTimeChg  between '+
					' convert(datetime,'''+convert(varchar(50),@dDateOfLastReplicationFROM,112)+  ' ' + convert(varchar(50),@dDateOfLastReplicationFROM,114) + ''') and '+
					' convert(datetime,'''+convert(varchar(50),@dDateOfLastReplicationTO,112)+  ' ' + convert(varchar(50),@dDateOfLastReplicationTO,114) + ''')'
			EXEC (@UserTableSQL)
			
  			SET @UserTableSQL =' INSERT INTO lOT_TableOfAllChanges_A '+
                ' SELECT acTableName, Id, acOTOSType, adTimeIns as a, ' +
				'''M'', 1, '+isnull(@UserTableChoosed, '0')+
				' FROM l'+substring(@cTableName, 2, len(@cTableName)-1)+'_Q '
	        EXEC (@UserTableSQL)					       	
        END ELSE
        BEGIN        
			SET @Log = @Log + 'User table '+isnull(@cTableName, 'No table')+' '+isnull(@cType, 'No type') +@ChrRet
			SET @UserTableSQL='IF exists (SELECT * FROM sysobjects WHERE NAME = ''l'+substring(@cTableName, 2, len(@cTableName)-1)+
			'_Q'') delete from l'+substring(@cTableName, 2, len(@cTableName)-1)+'_Q'	
			EXEC (@UserTableSQL)
			SET @UserTableSQL='INSERT l'+substring(@cTableName, 2, len(@cTableName)-1)+'_Q SELECT * FROM '+@cTableName
			EXEC (@UserTableSQL)
			DELETE FROM lOT_RecordCount
			SET @UserTableSQL='INSERT lOT_RecordCount SELECT count(*) from l'+substring(@cTableName, 2, len(@cTableName)-1)+'_Q'
			EXEC (@UserTableSQL)
			IF (SELECT anCount FROM lOT_RecordCount)>0
			  INSERT INTO lOT_TableOfAllChanges_A (acTableName, acMasterSlave, acChoosed) SELECT @cTableName, 'Q', @UserTableChoosed
			DELETE FROM lOT_RecordCount
        END
		FETCH NEXT FROM UserTables INTO @cTableName, @UserTableChoosed
	END
	CLOSE UserTables
	DEALLOCATE UserTables

	INSERT lOT_TemporaryTableList
		SELECT acTableName
		FROM tOT_ANSettingsUserTables WHERE acTableChoosed IN ('1', '2') and acKeyProfile=@cProfile
		AND NOT EXISTS(SELECT * FROM dbo.lOT_TemporaryTableList WHERE acTableName IN
		(SELECT acTableName FROM tOT_ANSettingsUserTables WHERE acTableChoosed IN ('1', '2') and acKeyProfile=@cProfile))
	
    if not exists (select * from lOT_TableOfAllChanges_A)
	BEGIN
	  --IF exists (SELECT * FROM tempdb.dbo.sysobjects WHERE NAME = '##tOT_ANSettingsExchangesINI')
	  DELETE FROM lOT_ANSettingsExchangesINI
	  IF (SELECT acImportExportType FROM tOT_ANSettingsProfiles
		  WHERE acKeyProfile=@cProfile)='C'
		  UPDATE tOT_ANSettingsExchanges SET acCentralSuccess=1 WHERE acIdTransfer=@idGUIDTransfer
			ELSE
		  UPDATE tOT_ANSettingsExchanges SET acLocationSuccess=1 WHERE acIdTransfer=@idGUIDTransfer
			  EXEC('insert lOT_ANSettingsExchangesINI SELECT * FROM tOT_ANSettingsExchanges WHERE acIdTransfer='''+@idGUIDTransfer+'''')
	  return 8	  
	END

	DELETE FROM lOT_TempTableExportList

	INSERT lOT_TempTableExportList SELECT 'lOT_ANSettingsExchanges'
	INSERT lOT_TempTableExportList SELECT 'lOT_TemporaryTableList'
	INSERT lOT_TempTableExportList SELECT 'lOT_UserTableList'
	
	INSERT lOT_TempTableExportList	SELECT name FROM sysobjects WHERE name LIKE 'l%'
			AND name NOT IN (SELECT * FROM lOT_TempTableExportList)
			AND substring(name, len(name)-1, 2) IN ('_O', '_A')
    INSERT lOT_TempTableExportList
		SELECT acTableName
		FROM tOT_ANSettingsUserTables WHERE acTableChoosed IN ('1', '2') and acKeyProfile=@cProfile
		AND NOT EXISTS(SELECT * FROM dbo.lOT_TempTableExportList WHERE acTableName IN
		(SELECT acTableName FROM tOT_ANSettingsUserTables WHERE acTableChoosed IN ('1', '2') and acKeyProfile=@cProfile))
				
	DELETE FROM lOT_TempTableExportList WHERE acTableName='tHE_BCPackage'
	
	DELETE FROM lOT_ANSettingsExchanges
	INSERT lOT_ANSettingsExchanges
	SELECT * FROM tOT_ANSettingsExchanges
	WHERE acIdTransfer=@idGUIDTransfer

    SET @Log = @Log+'Export ended at : '+ cast(getdate() as varchar(50))
	UPDATE tOT_ANSettingsExchanges
		SET acCentralSuccess  = case when acImportExportType='C' then '1' else acCentralSuccess end,
			acLocationSuccess = case when acImportExportType<>'C' then '1' else acLocationSuccess END,
			acExportLog = @Log
	FROM tOT_ANSettingsExchanges se
	INNER JOIN tOT_ANSettingsProfiles sp ON se.acKeyProfile=sp.acKeyProfile
	WHERE acIdTransfer=@idGUIDTransfer

	--Odstranimo tabele ki vsebujejo IDENTITY...
    --DELETE FROM lOT_TempTableExportList where acTableName in
    --(SELECT   OBJECT_NAME(OBJECT_ID) FROM SYS.IDENTITY_COLUMNS)
	

	DELETE FROM lOT_TempExceptionTables
	return 0
END












GO
/****** Object:  StoredProcedure [dbo].[pOT_ANPrepareImport]    Script Date: 02/25/2009 19:11:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[pOT_ANPrepareImport]
as
set nocount on

DELETE from lOT_TemporaryTableList
DELETE from lOT_TableOfAllChanges_A
DELETE FROM lOT_ANSettingsExchanges

--
--create table ##TemporaryTableList (acTableName varchar(100))
--create table ##TableOfAllChanges_A(
--		acTableName varchar(50),
--		agId varchar(38),
--		acOTOSType varchar(1),
--		adTimeChg datetime,
--		acMasterSlave char(1),
--		anLevel int,
--        acChoosed char(1))
--select * into ##tOT_ANSettingsExchanges from tOT_ANSettingsExchanges where 1=2









GO
/****** Object:  StoredProcedure [dbo].[pOT_ANPrepareTableForExport]    Script Date: 02/25/2009 19:11:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[pOT_ANPrepareTableForExport]
	@cType varchar(10),
	@cMasterTableName varchar(50),
	@cTableName varchar(50),
	@cMasterSlave char(1),
	@nLevel int,
	@cFinalChoosed char(1),
	@dDateOfLastReplicationFrom datetime,
	@dDateOfLastReplicationTo datetime
AS
set nocount on
BEGIN
	SET NOCOUNT ON;
    print ' pOT_ANPrepareTableForExport '+@cMasterTableName+' '+@cTableName
	DECLARE
		@cTableMustBeFiltered char(1)

	SET @cTableMustBeFiltered = '0'

	DECLARE
	 @cSQL varchar(2000),
	 @cWhere varchar(2000)
	
	set @cSQL='delete from l'+substring(@cTableName, 2, len(@cTableName)-1)+'_A'
    exec (@cSQL)
	SET @cSQL = ' insert l'+substring(@cTableName, 2, len(@cTableName)-1)+'_A'+
				' SELECT  newid() as Id, '+''''+@cTableName+''' as acTableName, '+
               ' CASE WHEN t.adTimeIns between ' +
					' convert(datetime,'''+convert(varchar(50),@dDateOfLastReplicationFROM,112)+  ' ' + convert(varchar(50),@dDateOfLastReplicationFROM,114) + ''') and '+
					' convert(datetime,'''+convert(varchar(50),@dDateOfLastReplicationTO,112)+  ' ' + convert(varchar(50),@dDateOfLastReplicationTO,114) + ''')'+
                         ' THEN ''I'' ELSE ''U'' END as acOTOSType,' +
               ' t.* FROM '+@cTableName + ' t '+
				isnull(CASE WHEN @cMasterSlave='S'
					THEN	CASE WHEN @cTableMustBeFiltered='1'
								THEN  ' INNER JOIN '+ @cMasterTableName + ' t1 ON t1.acKey=t.acKey'
							END					
				END, ' ')+
               ' WHERE '+
               ' t.adTimeIns between '+
					' convert(datetime,'''+convert(varchar(50),@dDateOfLastReplicationFROM,112)+  ' ' + convert(varchar(50),@dDateOfLastReplicationFROM,114) + ''') and '+
					' convert(datetime,'''+convert(varchar(50),@dDateOfLastReplicationTO,112)+  ' ' + convert(varchar(50),@dDateOfLastReplicationTO,114) + ''')'+
               '  or t.adTimeChg  between '+
					' convert(datetime,'''+convert(varchar(50),@dDateOfLastReplicationFROM,112)+  ' ' + convert(varchar(50),@dDateOfLastReplicationFROM,114) + ''') and '+
					' convert(datetime,'''+convert(varchar(50),@dDateOfLastReplicationTO,112)+  ' ' + convert(varchar(50),@dDateOfLastReplicationTO,114) + ''')'
    EXEC (@cSQL)

	SET @cSQL = ' INSERT INTO lOT_TableOfAllChanges_A '+
                ' SELECT acTableName, Id, acOTOSType, CASE WHEN acOTOSType=''I'' THEN adTimeIns ELSE adTimeChg END as a, ' +
				''''+@cMasterSlave+''',' + cast(@nLevel as varchar(1)) +','+isnull(@cFinalChoosed, '0')+
				' FROM l'+substring(@cTableName, 2, len(@cTableName)-1)+'_A'
	EXEC (@cSQL)

	--SET @cTableName=@cTableName+'_O'	
    SET @cSQL='delete from l'+substring(@cTableName, 2, len(@cTableName)-1)+'_O'

    if object_id(@cTableName+'_O') is not null
    BEGIN
		EXEC (@cSQL)

		SET @cSQL =' INSERT l'+substring(@cTableName, 2, len(@cTableName)-1)+'_O'+ 
				   ' SELECT  newid() as Id, '+''''+@cTableName+''' as acTableName, '+
				   ' t.* FROM '+@cTableName +'_O t '+
					CASE WHEN @cMasterSlave='S'
						THEN	CASE WHEN @cTableMustBeFiltered='1'
									THEN  ' INNER JOIN '+ @cMasterTableName + ' t1 ON t1.acKey=t.acKey'
									ELSE  ' '
								END
						ELSE	CASE WHEN @cTableMustBeFiltered='1'
									THEN  ' INNER JOIN '+ @cMasterTableName + ' t1 ON t1.acKey=t.acKey'
									ELSE  ' '
								END
					END +
				   ' WHERE t.adTimeChg between '+
						' convert(datetime,'''+convert(varchar(50),@dDateOfLastReplicationFROM,112)+  ' ' + convert(varchar(50),@dDateOfLastReplicationFROM,114) + ''') and '+
						' convert(datetime,'''+convert(varchar(50),@dDateOfLastReplicationTO,112)+  ' ' + convert(varchar(50),@dDateOfLastReplicationTO,114) + ''')'

		EXEC (@cSQL)

		SET @cSQL = ' INSERT INTO lOT_TableOfAllChanges_A '+
					' SELECT acTableName, Id, acOTOSType, adTimeChg, ' +
					''''+@cMasterSlave+''',' + cast(@nLevel as varchar(1)) +','+isnull(@cFinalChoosed, '0')+
					' FROM l'+substring(@cTableName, 2, len(@cTableName)-1)+'_O'
		EXEC (@cSQL)
    END
END











GO
/****** Object:  StoredProcedure [dbo].[pOT_ANRefreshDocTypeChoosed]    Script Date: 02/25/2009 19:11:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[pOT_ANRefreshDocTypeChoosed]
@acProfile char(3),
@acVrsta char(1)
AS
set nocount on

INSERT INTO tOT_ANDocTypeChoosed (acSetOf, acType, acChoosed, acKeyProfile, acDocType)
SELECT
	tk.acSetOf,
	tk.acType,
	'0',
	@acProfile,
	tsdt.acDocType
FROM tPA_SysDocTypeOfKind tk
INNER JOIN tPA_SetDocType tsdt ON tsdt.acSetOf = tk.acSetOf AND tsdt.acType = tk.acType
WHERE NOT EXISTS
(SELECT * FROM tOT_ANDocTypeChoosed t
 INNER JOIN tPA_SysDocTypeOfKind s ON s.acSetOf=tk.acSetOf AND s.acType=tk.acType
 INNER JOIN tPA_SetDocType tps ON tps.acDocType=t.acDocType AND t.acKeyProfile=@acProfile)

DELETE tOT_ANDocTypeChoosed
FROM tOT_ANDocTypeChoosed tc
WHERE NOT EXISTS
(SELECT * FROM tOT_ANDocTypeChoosed t
 INNER JOIN tPA_SysDocTypeOfKind s ON s.acSetOf=tc.acSetOf AND s.acType=tc.acType
 INNER JOIN tPA_SetDocType tps ON tps.acDocType=tc.acDocType AND t.acKeyProfile=@acProfile)
 AND tc.acKeyProfile=@acProfile

DECLARE @acChoosed char(1)

IF @acVrsta='A' SET @acChoosed='1' ELSE SET @acChoosed='0'

IF @acVrsta <> '' UPDATE tOT_ANDocTypeChoosed SET acChoosed=@acChoosed WHERE acKeyProfile=@acProfile









GO
/****** Object:  StoredProcedure [dbo].[pOT_ANSetExchanges]    Script Date: 02/25/2009 19:11:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE procedure [dbo].[pOT_ANSetExchanges]
	@acKeyProfile char(3),
	@acType char(1),
	@idGUIDTransfer varchar(38) OUTPUT
as

declare
	@acVersion varchar(15),
	@anBuildNo int,
	@acCentralLocation char(1),
	@acExportImportFirst char(1),
	@adDateCentralStart datetime,
	@acIdTransfer char(38),
	@acImportExportOnly char(1),
	@adDateImpExpStart datetime,
	@adDateImpExpEnd datetime,
    @acVersionCen varchar(15), 
    @anBuildNoCen int
    
SELECT @acImportExportOnly=CASE WHEN tap.acActive='O' THEN 'D' ELSE '' END,
		@adDateImpExpStart=tap.adDateStatCentral,
		@adDateImpExpEnd=tap.adDateStatLocation
	   FROM tOT_ANSettingsProfiles tap WHERE tap.acKeyProfile=@acKeyProfile

IF @acImportExportOnly='D' DELETE FROM tOT_ANSettingsExchanges WHERE acKeyProfile=@acKeyProfile

if not exists (select * from tOT_ANSettingsExchanges where acKeyProfile=@acKeyProfile)
	set @acExportImportFirst='1' else set @acExportImportFirst='0'

select @acVersion=acVersion, @anBuildNo=anBuildNo from TPA_SysParamSys

if exists (select * from tOT_ANSettingsProfiles where	
			acKeyProfile=@acKeyProfile and acImportExportType='C')
	set @acCentralLocation='1' else set @acCentralLocation='0'

if (@acCentralLocation='1') and (@acType='E')
begin
	if exists (select * from  tOT_ANSettingsExchanges where acKeyProfile=@acKeyProfile and acCentralSuccess='1' and acLocationSuccess='0')
		RETURN 1
  	if @acExportImportFirst='1'
	BEGIN
      select	@adDateCentralStart=adDateInitialState from tOT_ANSettingsProfiles where acKeyProfile=@acKeyProfile		
      IF @acImportExportOnly='D' SET @adDateCentralStart=@adDateImpExpStart     
	END	
	else
		select top 1 @adDateCentralStart=adDateCentralFinish
		 from tOT_ANSettingsExchanges
		 where acCentralSuccess='1' and acLocationSuccess='1' AND acKeyProfile=@acKeyProfile
		 order by adDateTimeIns desc

	SET @idGUIDTransfer=cast(newid() AS varchar(38))
	
        insert into tOT_ANSettingsExchanges
        (acKeyProfile,adDateCentralStart,adDateCentralFinish,
		 adDateLocationStart,adDateLocationFinish,acCentralSuccess,
         acLocationSuccess,acIdTransfer, acVersion, anBuildNo,adDateTimeIns)
		values
        (@acKeyProfile, @adDateCentralStart, CASE WHEN @acImportExportOnly='D' THEN @adDateImpExpEnd ELSE getdate() end,
		 null, null, '0',
		 '0', @idGUIDTransfer, @acVersion, @anBuildNo, getdate()
        )
end

if (@acCentralLocation='0') and (@acType='I')
begin	
	select top 1 @acVersionCen=acVersion , @anBuildNoCen=anBuildNo from lOT_ANSettingsExchanges
	if not (@acVersion=@acVersionCen and  @anBuildNo=@anBuildNoCen)
		return 2
	if exists (select * from  tOT_ANSettingsExchanges where acKeyProfile=@acKeyProfile and acCentralSuccess='1' and acLocationSuccess='0')
		RETURN 3

	select	top 1 @acIdTransfer=acIdTransfer from lOT_ANSettingsExchanges
    IF NOT EXISTS (SELECT * FROM tOT_ANSettingsExchanges WHERE acIdTransfer=@acIdTransfer)
	BEGIN
	    insert into tOT_ANSettingsExchanges
			(acKeyProfile,adDateCentralStart,adDateCentralFinish,
			 adDateLocationStart,adDateLocationFinish,acCentralSuccess,
			 acLocationSuccess,acIdTransfer, acVersion, anBuildNo,adDateTimeIns)
		select	top 1 acKeyProfile,adDateCentralStart, adDateCentralFinish,
				null,null, '0',
				'0', acIdTransfer, acVersion, anBuildNo, getdate()
				from lOT_ANSettingsExchanges
	end
	ELSE
		RETURN 6
	SET @idGUIDTransfer= (select top 1 acIdTransfer FROM lOT_ANSettingsExchanges)
end

if (@acCentralLocation='0') and (@acType='E')
begin
	if not exists (select * from  tOT_ANSettingsExchanges where acKeyProfile=@acKeyProfile and acCentralSuccess='1' and acLocationSuccess='0')
		RETURN 3
	SET @idGUIDTransfer=(SELECT TOP 1 acIdTransfer FROM tOT_ANSettingsExchanges where acKeyProfile=@acKeyProfile and acCentralSuccess='1' and acLocationSuccess='0')
end

if (@acCentralLocation='1') and (@acType='I') AND (@acImportExportOnly<>'D')
begin
	declare @acVersionLok varchar(15), @anBuildNoLok int
	select top 1 @acVersionCen=acVersion , @anBuildNoCen=anBuildNo from lOT_ANSettingsExchanges
	if not (@acVersion=@acVersionLok and  @anBuildNo=@anBuildNoLok)
		return 2
	if not exists (select * from  tOT_ANSettingsExchanges where acKeyProfile=@acKeyProfile and acCentralSuccess='1' and acLocationSuccess='0')
		RETURN 3

	SET @idGUIDTransfer=(SELECT TOP 1 acIdTransfer FROM tOT_ANSettingsExchanges where acKeyProfile=@acKeyProfile and acCentralSuccess='1' and acLocationSuccess='0')

end

if (@acCentralLocation='1') and (@acType='I') AND (@acImportExportOnly='D')
begin
	
	select top 1 @acVersionCen=acVersion , @anBuildNoCen=anBuildNo from lOT_ANSettingsExchanges
	if not (@acVersion=@acVersionCen and  @anBuildNo=@anBuildNoCen)
		return 2
	if exists (select * from  tOT_ANSettingsExchanges where acKeyProfile=@acKeyProfile and acCentralSuccess='1' and acLocationSuccess='0')
		RETURN 3

	select	top 1 @acIdTransfer=acIdTransfer from lOT_ANSettingsExchanges
    IF NOT EXISTS (SELECT * FROM tOT_ANSettingsExchanges WHERE acIdTransfer=@acIdTransfer)
	BEGIN
	    insert into tOT_ANSettingsExchanges
			(acKeyProfile,adDateCentralStart,adDateCentralFinish,
			 adDateLocationStart,adDateLocationFinish,acCentralSuccess,
			 acLocationSuccess,acIdTransfer, acVersion, anBuildNo,adDateTimeIns)
		select	top 1 acKeyProfile,adDateCentralStart, adDateCentralFinish,
				null,null, '0',
				'0', acIdTransfer, acVersion, anBuildNo, getdate()
				from lOT_ANSettingsExchanges
	end
	ELSE
		RETURN 6
	SET @idGUIDTransfer= (select top 1 acIdTransfer FROM lOT_ANSettingsExchanges)
end


RETURN 0









GO
/****** Object:  StoredProcedure [dbo].[pOT_ANSetTreeImportExport]    Script Date: 02/25/2009 19:11:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[pOT_ANSetTreeImportExport]
@acKeyProfile char(3),
@agIdGuidOtos int,
@acWorkType char(1),
@acDocType varchar(10),
@acSelected char(1),
@acTableName varchar(50)
AS
SET nocount ON

--CREATE TABLE #TempKandidati
--(
--    id int
--)
DELETE FROM lOT_Kandidati

INSERT lOT_Kandidati
SELECT @agIdGuidOtos
UNION 
	
SELECT anIdGuidOtos
FROM   tOT_ImportExport
WHERE  anIdGuidOtosMaster = @agIdGuidOtos 
UNION
	
SELECT anIdGuidOtos
FROM   tOT_ImportExport
WHERE  anIdGuidOtosMaster IN (SELECT anIdGuidOtos
                              FROM   tOT_ImportExport
                              WHERE  anIdGuidOtosMaster = @agIdGuidOtos)
UNION
	
SELECT anIdGuidOtos
FROM   tOT_ImportExport
WHERE  anIdGuidOtosMaster IN
(SELECT anIdGuidOtos
 FROM   tOT_ImportExport
 WHERE  anIdGuidOtosMaster = @agIdGuidOtos
 UNION
 SELECT anIdGuidOtos
 FROM   tOT_ImportExport
 WHERE  anIdGuidOtosMaster IN
 (SELECT anIdGuidOtos
  FROM   tOT_ImportExport
  WHERE  anIdGuidOtosMaster = @agIdGuidOtos))
UNION
    
SELECT anIdGuidOtos
FROM   tOT_ImportExport
WHERE  anIdGuidOtosMaster IN
(SELECT anIdGuidOtos
 FROM   tOT_ImportExport
 WHERE  anIdGuidOtosMaster IN
 (SELECT anIdGuidOtos
  FROM   tOT_ImportExport
  WHERE  anIdGuidOtosMaster = @agIdGuidOtos
  UNION
  SELECT anIdGuidOtos
  FROM   tOT_ImportExport
  WHERE  anIdGuidOtosMaster IN
  (SELECT anIdGuidOtos
   FROM   tOT_ImportExport
   WHERE  anIdGuidOtosMaster = @agIdGuidOtos)))


IF @acWorkType='N'
BEGIN
  UPDATE tOT_ANSettingsTables
  SET    acChoosed = '0'
  WHERE  acKeyProfile = @acKeyProfile
  
  UPDATE tOT_ANDocTypeChoosed
  SET    acChoosed = '0'
  WHERE  acKeyProfile = @acKeyProfile
END 

IF @acWorkType='A'
BEGIN
  UPDATE tOT_ANSettingsTables
  SET    acChoosed = '1'
  WHERE  acKeyProfile = @acKeyProfile
  
  UPDATE tOT_ANDocTypeChoosed
  SET    acChoosed = '1'
  WHERE  acKeyProfile = @acKeyProfile
END 


IF @acDocType='Dokumenti'
BEGIN
	IF @acWorkType='S'
	BEGIN
		UPDATE tOT_ANDocTypeChoosed
		SET    acChoosed = @acSelected
		WHERE  acKeyProfile = @acKeyProfile
	END
	IF @acWorkType='D'
	BEGIN  
		UPDATE tOT_ANDocTypeChoosed
		SET    acChoosed = '0'
		WHERE  acKeyProfile = @acKeyProfile
	END

    DELETE FROM lOT_Kandidati
	return
END

IF (
           SELECT tie.acPARENT
           FROM   tOT_ImportExport tie
           WHERE  tie.anIdGuidOtos = @agIdGuidOtos
       )='Skupina VD'
BEGIN
    IF @acWorkType='S'
        UPDATE tOT_ANDocTypeChoosed
        SET    acChoosed = @acSelected
        FROM   tOT_ANDocTypeChoosed t
        WHERE  t.acDocType = @acDocType
          AND  t.acKeyProfile = @acKeyProfile
    
    IF @acWorkType='D'
        UPDATE tOT_ANDocTypeChoosed
        SET    acChoosed = '0'
        FROM   tOT_ANDocTypeChoosed t
        WHERE  t.acDocType = @acDocType
          AND  t.acKeyProfile = @acKeyProfile
END
ELSE 
BEGIN
    IF @acWorkType='S'
        UPDATE tOT_ANSettingsTables
        SET    acChoosed = @acSelected
        FROM   tOT_ANSettingsTables t
        INNER JOIN lOT_Kandidati tk
          ON   tk.id = t.anIdGuidOtos
        WHERE  t.acKeyProfile = @acKeyProfile
    
    IF @acWorkType='D'
        UPDATE tOT_ANSettingsTables
        SET    acChoosed = '0'
        FROM   tOT_ANSettingsTables t
        INNER JOIN lOT_Kandidati tk
          ON   tk.id = t.anIdGuidOtos
        WHERE  t.acKeyProfile = @acKeyProfile
END