C11/System/OPTIMA/SQL/batch/UNIT_CLASSIFICAT.bat

@echo off
call "C:\GlobalCube\Tasks\scripts\config2.bat"
rem ==UNIT_CLASSIFICAT==
del C:\GlobalCube\System\OPTIMA\SQL\logs\UNIT_CLASSIFICAT*.* /Q /F >nul 2>nul

if not "%1"=="" goto :increment

:full
  call sql_query.bat "TRUNCATE TABLE [OPTIMA2].[import].[UNIT_CLASSIFICAT]"
  call bcp_queryout.bat "UNIT_CLASSIFICAT_1" "SELECT T1.[UNIT_NUMBER], T1.[BATCH_NUMBER_S], T1.[ACCTNG_PERIOD], T1.[LINE_NO], T1.[ACCT_NR], T1.[DEPARTMENT], T1.[BOOK_KEEPING_CODE], T1.[TRANSFER_CODE], T1.[TRANSACTION_DATE], T1.[TRANSACTION_CODE], T1.[TRANSACTION_AMOUNT], T1.[DOCUMENT_NR], T1.[TRANSFER_DIFFTAX], T1.[TRF_TAXBASIS_SALES], T1.[TRF_TAXBASIS_PURCH], T1.[BOOK_NO], T1.[PURCH_INVNO], T1.[ORIGINAL_BATCH_NO], T1.[CONV_FLAG], T1.[timestamp], T1.[UNIQUE_IDENT], '1' as [Client_DB] FROM [deop01].[dbo].[UNIT_CLASSIFICAT] T1 INNER JOIN [deop01].[dbo].[UNIT_FILE] T2 ON T1.[UNIT_NUMBER] = T2.[UNIT_NUMBER] INNER JOIN [deop01].[dbo].[VEHICLE] T3 ON T2.[BASIS_NUMBER] = T3.[BASIS_NUMBER]  WHERE T3.[LAST_INVOICE_DTE] >= '2018-01-01T00:00:00' ORDER BY T1.[timestamp] "
  call bcp_in.bat "UNIT_CLASSIFICAT_1" "[import].[UNIT_CLASSIFICAT]" "OPTIMA2"
  call bcp_queryout.bat "UNIT_CLASSIFICAT_2" "SELECT T1.[UNIT_NUMBER], T1.[BATCH_NUMBER_S], T1.[ACCTNG_PERIOD], T1.[LINE_NO], T1.[ACCT_NR], T1.[DEPARTMENT], T1.[BOOK_KEEPING_CODE], T1.[TRANSFER_CODE], T1.[TRANSACTION_DATE], T1.[TRANSACTION_CODE], T1.[TRANSACTION_AMOUNT], T1.[DOCUMENT_NR], T1.[TRANSFER_DIFFTAX], T1.[TRF_TAXBASIS_SALES], T1.[TRF_TAXBASIS_PURCH], T1.[BOOK_NO], T1.[PURCH_INVNO], T1.[ORIGINAL_BATCH_NO], T1.[CONV_FLAG], T1.[timestamp], T1.[UNIQUE_IDENT], '2' as [Client_DB] FROM [deop02].[dbo].[UNIT_CLASSIFICAT] T1 INNER JOIN [deop02].[dbo].[UNIT_FILE] T2 ON T1.[UNIT_NUMBER] = T2.[UNIT_NUMBER] INNER JOIN [deop02].[dbo].[VEHICLE] T3 ON T2.[BASIS_NUMBER] = T3.[BASIS_NUMBER]  WHERE T3.[LAST_INVOICE_DTE] >= '2018-01-01T00:00:00' ORDER BY T1.[timestamp] "
  call bcp_in.bat "UNIT_CLASSIFICAT_2" "[import].[UNIT_CLASSIFICAT]" "OPTIMA2"
  goto :cleanup

:increment
  call sql_query.bat "TRUNCATE TABLE [OPTIMAX].[temp].[UNIT_CLASSIFICAT]"

  call sql_timestamp.bat "UNIT_CLASSIFICAT_1" "[OPTIMA2].[import].[UNIT_CLASSIFICAT]" "1"
  call bcp_queryout.bat "UNIT_CLASSIFICAT_1" "SELECT T1.[UNIT_NUMBER], T1.[BATCH_NUMBER_S], T1.[ACCTNG_PERIOD], T1.[LINE_NO], T1.[ACCT_NR], T1.[DEPARTMENT], T1.[BOOK_KEEPING_CODE], T1.[TRANSFER_CODE], T1.[TRANSACTION_DATE], T1.[TRANSACTION_CODE], T1.[TRANSACTION_AMOUNT], T1.[DOCUMENT_NR], T1.[TRANSFER_DIFFTAX], T1.[TRF_TAXBASIS_SALES], T1.[TRF_TAXBASIS_PURCH], T1.[BOOK_NO], T1.[PURCH_INVNO], T1.[ORIGINAL_BATCH_NO], T1.[CONV_FLAG], T1.[timestamp], T1.[UNIQUE_IDENT], '1' as [Client_DB] FROM [deop01].[dbo].[UNIT_CLASSIFICAT] T1 INNER JOIN [deop01].[dbo].[UNIT_FILE] T2 ON T1.[UNIT_NUMBER] = T2.[UNIT_NUMBER] INNER JOIN [deop01].[dbo].[VEHICLE] T3 ON T2.[BASIS_NUMBER] = T3.[BASIS_NUMBER]  WHERE T1.[timestamp] > convert(binary(8), '%TS%', 1) AND T3.[LAST_INVOICE_DTE] >= '2018-01-01T00:00:00' ORDER BY T1.[timestamp] "
  call bcp_in.bat "UNIT_CLASSIFICAT_1" "[temp].[UNIT_CLASSIFICAT]" "OPTIMAX"

  call sql_timestamp.bat "UNIT_CLASSIFICAT_2" "[OPTIMA2].[import].[UNIT_CLASSIFICAT]" "2"
  call bcp_queryout.bat "UNIT_CLASSIFICAT_2" "SELECT T1.[UNIT_NUMBER], T1.[BATCH_NUMBER_S], T1.[ACCTNG_PERIOD], T1.[LINE_NO], T1.[ACCT_NR], T1.[DEPARTMENT], T1.[BOOK_KEEPING_CODE], T1.[TRANSFER_CODE], T1.[TRANSACTION_DATE], T1.[TRANSACTION_CODE], T1.[TRANSACTION_AMOUNT], T1.[DOCUMENT_NR], T1.[TRANSFER_DIFFTAX], T1.[TRF_TAXBASIS_SALES], T1.[TRF_TAXBASIS_PURCH], T1.[BOOK_NO], T1.[PURCH_INVNO], T1.[ORIGINAL_BATCH_NO], T1.[CONV_FLAG], T1.[timestamp], T1.[UNIQUE_IDENT], '2' as [Client_DB] FROM [deop02].[dbo].[UNIT_CLASSIFICAT] T1 INNER JOIN [deop02].[dbo].[UNIT_FILE] T2 ON T1.[UNIT_NUMBER] = T2.[UNIT_NUMBER] INNER JOIN [deop02].[dbo].[VEHICLE] T3 ON T2.[BASIS_NUMBER] = T3.[BASIS_NUMBER]  WHERE T1.[timestamp] > convert(binary(8), '%TS%', 1) AND T3.[LAST_INVOICE_DTE] >= '2018-01-01T00:00:00' ORDER BY T1.[timestamp] "
  call bcp_in.bat "UNIT_CLASSIFICAT_2" "[temp].[UNIT_CLASSIFICAT]" "OPTIMAX"

  call sql_query.bat "DELETE T1 FROM [OPTIMA2].[import].[UNIT_CLASSIFICAT] T1 INNER JOIN [OPTIMAX].[temp].[UNIT_CLASSIFICAT] T2 ON T1.[ACCTNG_PERIOD] = T2.[ACCTNG_PERIOD] AND T1.[BATCH_NUMBER_S] = T2.[BATCH_NUMBER_S] AND T1.[CLIENT_DB] = T2.[CLIENT_DB] AND T1.[LINE_NO] = T2.[LINE_NO] AND T1.[UNIT_NUMBER] = T2.[UNIT_NUMBER]"
  call sql_query.bat "INSERT INTO [OPTIMA2].[import].[UNIT_CLASSIFICAT] with (TABLOCK) SELECT * FROM [OPTIMAX].[temp].[UNIT_CLASSIFICAT] T1"

:cleanup
  call delete.bat "C:\GlobalCube\System\OPTIMA\SQL\temp\UNIT_CLASSIFICAT_1.csv"
  call delete.bat "C:\GlobalCube\System\OPTIMA\SQL\temp\UNIT_CLASSIFICAT_2.csv"