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

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

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

:full
  call sql_query.bat "TRUNCATE TABLE [OPTIMA].[import].[VEHICLE_EXTENSION]"
rem Nur in Quelle: PTVL_VEHICLE_LINE;INSURANCE_CHECK;VEH_LINE_CODE;WARRANTY_START_DATE;WARRANTY_CODE;CENTRAL_STATUS_IMP;INV_NUMBER_KWB;WERS_ENTITY;PRODUCTION_ORIGIN;TYPE_VARIANT_VERSION;INSURANCE_CHECK_DATE;NAME_PLATE_ID;TIMINGBELT_CHANGE_MILEAGE;TIMINGBELT_CHANGE_DATE;LEASING_FINANCING_NUMBER;COURTESY_CATEGORY;TRANSMISSION_DATE_GME;NAME_PLATE_LABLE;VEH_LINE_DESCRIPTION
  call bcp_queryout.bat "VEHICLE_EXTENSION_1" "SELECT T1.[BASIS_NUMBER], T1.[EXTERNAL_ID], T1.[MOT_TEST], T1.[YELLOW_GREY], T1.[REPLACE_MILEAGE], T1.[LAST_ASU_DATE], T1.[TRIM_TYPE_CODE], T1.[CLASSIFICATION_CD], T1.[FLEET_PRICE_LEVEL], T1.[DETAIL_REG_DATE], T1.[TRADE_IN_MAKE], T1.[TRADE_IN_MILEAGE], T1.[TRADE_IN_COLOUR], T1.[CONV_FLAG], T1.[timestamp], T1.[UNIQUE_IDENT], T1.[ADDR_2_DELIVERY], T1.[CO2_EMISSION], T1.[EURO_EMISSION_STD], T1.[GASSYSTEM], T1.[KILOWATT], T1.[LOGIN_DEALER], T1.[LOGIN_RESV_DEALER], T1.[MAIL_ADDR_DELIVERY], T1.[MODEL_TEXT_2], T1.[NAME_DELIVERY], T1.[NOX_EXHAUST], T1.[PACKAGE_TEXT], T1.[PARTICLE_FILTER], T1.[PICK_SELF], T1.[ZIPCODE_DELIVERY], T1.[COC_NUMBER], T1.[DAM_NUMBER], T1.[DAMAGE_DESCRIPTION], T1.[DAMAGE_PRICE], T1.[GEAR_BOX_TYPE], T1.[LARGER_DAMAGE], T1.[MODEL], T1.[MODEL_VARIANT], T1.[PROD_DATE], T1.[SPECIAL_USE], T1.[WEIGHT], T1.[ADVICE_RECIPIENT], T1.[AIR_CONDITION], T1.[DAT_EURO_CODE], T1.[DRIVER], T1.[DSP_EXPIRY_DATE], T1.[DSP_MAKE_CD], T1.[DSP_NO_OF_SERVICES_USED], T1.[DSP_PROD_CODE], T1.[DSP_REST_AMOUNT], T1.[DSP_START_AMOUNT], T1.[GAS_SYSTEM_INSTALLED], T1.[HANDLER_SYSTEM], T1.[INSURANCE_COMPANY_CHANGED], T1.[LEAS_FINANCING_DOWN_PAY], T1.[LEAS_FINANCING_FINAL_PAY], T1.[LEAS_FINANCING_MONTH_PAY], T1.[LEASING_FINANCING_CONTRACT], T1.[LEASING_FINANCING_EXPIRE], T1.[LEASING_FINANCING_NAME], T1.[LEASING_FINANCING_PERIOD], T1.[LEASING_FINANCING_SALESMAN], T1.[LEASING_FINANCING_START], T1.[MAN_CALL_FOR_SERVICE], T1.[MISC_1_DD], T1.[MISC_2_DD], T1.[MISC_3_DD], T1.[MISC_4_DD], T1.[MISC_5_DD], T1.[MOBILE_SERVICE_EXPIRY_DATE], T1.[MOBILE_SERVICE_TYPE], T1.[NEXT_AIR_CONDITIONING_DATE], T1.[SERVICE_GUARANTEE], T1.[TYRES], T1.[TYRES_LOCATION], T1.[BATTERY_FINANCING], T1.[ELECTRICITY_CONSUMPTION], T1.[ELECTRICITY_EMISSION], T1.[GAS_CONSUMPTION], T1.[GAS_EMISSION], T1.[LOWEST_EMISSION_FUEL], T1.[MODEL_IDENTIFIER], T1.[OPTION_IDENTIFIER], T1.[PIN_CODE], T1.[PIN_IDENTIFIER], T1.[PIN_STATUS], T1.[SUPPORTED_FUEL], T1.[TYPE_APPROVAL_IDENTIFIER], T1.[VEHICLE_DESCRIPTION], T1.[TDK_CALL_DATE], T1.[DATE_ORDER_RECEIVED], T1.[EQUIPMENT_YES_NO], T1.[EMISSION_CODE], T1.[EMISSION_TEXT], T1.[ENGINE_CODE], T1.[ENGINE_TEXT], T1.[OWN_INSURANCE_SHARE], T1.[TRANSMISSION_CODE], T1.[TRANSMISSION_TEXT], T1.[TSV_VERSION], T1.[OWNER_CODE_2], '1' as [Client_DB] FROM [deop01].[dbo].[VEHICLE_EXTENSION] T1  WHERE 1 = 1 ORDER BY T1.[timestamp] "
  call bcp_in.bat "VEHICLE_EXTENSION_1" "[import].[VEHICLE_EXTENSION]" "OPTIMA"
rem Nur in Quelle: PTVL_VEHICLE_LINE;INSURANCE_CHECK;VEH_LINE_CODE;WARRANTY_START_DATE;WARRANTY_CODE;CENTRAL_STATUS_IMP;INV_NUMBER_KWB;WERS_ENTITY;PRODUCTION_ORIGIN;TYPE_VARIANT_VERSION;INSURANCE_CHECK_DATE;NAME_PLATE_ID;TIMINGBELT_CHANGE_MILEAGE;TIMINGBELT_CHANGE_DATE;LEASING_FINANCING_NUMBER;COURTESY_CATEGORY;TRANSMISSION_DATE_GME;NAME_PLATE_LABLE;VEH_LINE_DESCRIPTION
  call bcp_queryout.bat "VEHICLE_EXTENSION_2" "SELECT T1.[BASIS_NUMBER], T1.[EXTERNAL_ID], T1.[MOT_TEST], T1.[YELLOW_GREY], T1.[REPLACE_MILEAGE], T1.[LAST_ASU_DATE], T1.[TRIM_TYPE_CODE], T1.[CLASSIFICATION_CD], T1.[FLEET_PRICE_LEVEL], T1.[DETAIL_REG_DATE], T1.[TRADE_IN_MAKE], T1.[TRADE_IN_MILEAGE], T1.[TRADE_IN_COLOUR], T1.[CONV_FLAG], T1.[timestamp], T1.[UNIQUE_IDENT], T1.[ADDR_2_DELIVERY], T1.[CO2_EMISSION], T1.[EURO_EMISSION_STD], T1.[GASSYSTEM], T1.[KILOWATT], T1.[LOGIN_DEALER], T1.[LOGIN_RESV_DEALER], T1.[MAIL_ADDR_DELIVERY], T1.[MODEL_TEXT_2], T1.[NAME_DELIVERY], T1.[NOX_EXHAUST], T1.[PACKAGE_TEXT], T1.[PARTICLE_FILTER], T1.[PICK_SELF], T1.[ZIPCODE_DELIVERY], T1.[COC_NUMBER], T1.[DAM_NUMBER], T1.[DAMAGE_DESCRIPTION], T1.[DAMAGE_PRICE], T1.[GEAR_BOX_TYPE], T1.[LARGER_DAMAGE], T1.[MODEL], T1.[MODEL_VARIANT], T1.[PROD_DATE], T1.[SPECIAL_USE], T1.[WEIGHT], T1.[ADVICE_RECIPIENT], T1.[AIR_CONDITION], T1.[DAT_EURO_CODE], T1.[DRIVER], T1.[DSP_EXPIRY_DATE], T1.[DSP_MAKE_CD], T1.[DSP_NO_OF_SERVICES_USED], T1.[DSP_PROD_CODE], T1.[DSP_REST_AMOUNT], T1.[DSP_START_AMOUNT], T1.[GAS_SYSTEM_INSTALLED], T1.[HANDLER_SYSTEM], T1.[INSURANCE_COMPANY_CHANGED], T1.[LEAS_FINANCING_DOWN_PAY], T1.[LEAS_FINANCING_FINAL_PAY], T1.[LEAS_FINANCING_MONTH_PAY], T1.[LEASING_FINANCING_CONTRACT], T1.[LEASING_FINANCING_EXPIRE], T1.[LEASING_FINANCING_NAME], T1.[LEASING_FINANCING_PERIOD], T1.[LEASING_FINANCING_SALESMAN], T1.[LEASING_FINANCING_START], T1.[MAN_CALL_FOR_SERVICE], T1.[MISC_1_DD], T1.[MISC_2_DD], T1.[MISC_3_DD], T1.[MISC_4_DD], T1.[MISC_5_DD], T1.[MOBILE_SERVICE_EXPIRY_DATE], T1.[MOBILE_SERVICE_TYPE], T1.[NEXT_AIR_CONDITIONING_DATE], T1.[SERVICE_GUARANTEE], T1.[TYRES], T1.[TYRES_LOCATION], T1.[BATTERY_FINANCING], T1.[ELECTRICITY_CONSUMPTION], T1.[ELECTRICITY_EMISSION], T1.[GAS_CONSUMPTION], T1.[GAS_EMISSION], T1.[LOWEST_EMISSION_FUEL], T1.[MODEL_IDENTIFIER], T1.[OPTION_IDENTIFIER], T1.[PIN_CODE], T1.[PIN_IDENTIFIER], T1.[PIN_STATUS], T1.[SUPPORTED_FUEL], T1.[TYPE_APPROVAL_IDENTIFIER], T1.[VEHICLE_DESCRIPTION], T1.[TDK_CALL_DATE], T1.[DATE_ORDER_RECEIVED], T1.[EQUIPMENT_YES_NO], T1.[EMISSION_CODE], T1.[EMISSION_TEXT], T1.[ENGINE_CODE], T1.[ENGINE_TEXT], T1.[OWN_INSURANCE_SHARE], T1.[TRANSMISSION_CODE], T1.[TRANSMISSION_TEXT], T1.[TSV_VERSION], T1.[OWNER_CODE_2], '2' as [Client_DB] FROM [deop02].[dbo].[VEHICLE_EXTENSION] T1  WHERE 1 = 1 ORDER BY T1.[timestamp] "
  call bcp_in.bat "VEHICLE_EXTENSION_2" "[import].[VEHICLE_EXTENSION]" "OPTIMA"
  goto :cleanup

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

  call sql_timestamp.bat "VEHICLE_EXTENSION_1" "[OPTIMA].[import].[VEHICLE_EXTENSION]" "1"
  call bcp_queryout.bat "VEHICLE_EXTENSION_1" "SELECT T1.[BASIS_NUMBER], T1.[EXTERNAL_ID], T1.[MOT_TEST], T1.[YELLOW_GREY], T1.[REPLACE_MILEAGE], T1.[LAST_ASU_DATE], T1.[TRIM_TYPE_CODE], T1.[CLASSIFICATION_CD], T1.[FLEET_PRICE_LEVEL], T1.[DETAIL_REG_DATE], T1.[TRADE_IN_MAKE], T1.[TRADE_IN_MILEAGE], T1.[TRADE_IN_COLOUR], T1.[CONV_FLAG], T1.[timestamp], T1.[UNIQUE_IDENT], T1.[ADDR_2_DELIVERY], T1.[CO2_EMISSION], T1.[EURO_EMISSION_STD], T1.[GASSYSTEM], T1.[KILOWATT], T1.[LOGIN_DEALER], T1.[LOGIN_RESV_DEALER], T1.[MAIL_ADDR_DELIVERY], T1.[MODEL_TEXT_2], T1.[NAME_DELIVERY], T1.[NOX_EXHAUST], T1.[PACKAGE_TEXT], T1.[PARTICLE_FILTER], T1.[PICK_SELF], T1.[ZIPCODE_DELIVERY], T1.[COC_NUMBER], T1.[DAM_NUMBER], T1.[DAMAGE_DESCRIPTION], T1.[DAMAGE_PRICE], T1.[GEAR_BOX_TYPE], T1.[LARGER_DAMAGE], T1.[MODEL], T1.[MODEL_VARIANT], T1.[PROD_DATE], T1.[SPECIAL_USE], T1.[WEIGHT], T1.[ADVICE_RECIPIENT], T1.[AIR_CONDITION], T1.[DAT_EURO_CODE], T1.[DRIVER], T1.[DSP_EXPIRY_DATE], T1.[DSP_MAKE_CD], T1.[DSP_NO_OF_SERVICES_USED], T1.[DSP_PROD_CODE], T1.[DSP_REST_AMOUNT], T1.[DSP_START_AMOUNT], T1.[GAS_SYSTEM_INSTALLED], T1.[HANDLER_SYSTEM], T1.[INSURANCE_COMPANY_CHANGED], T1.[LEAS_FINANCING_DOWN_PAY], T1.[LEAS_FINANCING_FINAL_PAY], T1.[LEAS_FINANCING_MONTH_PAY], T1.[LEASING_FINANCING_CONTRACT], T1.[LEASING_FINANCING_EXPIRE], T1.[LEASING_FINANCING_NAME], T1.[LEASING_FINANCING_PERIOD], T1.[LEASING_FINANCING_SALESMAN], T1.[LEASING_FINANCING_START], T1.[MAN_CALL_FOR_SERVICE], T1.[MISC_1_DD], T1.[MISC_2_DD], T1.[MISC_3_DD], T1.[MISC_4_DD], T1.[MISC_5_DD], T1.[MOBILE_SERVICE_EXPIRY_DATE], T1.[MOBILE_SERVICE_TYPE], T1.[NEXT_AIR_CONDITIONING_DATE], T1.[SERVICE_GUARANTEE], T1.[TYRES], T1.[TYRES_LOCATION], T1.[BATTERY_FINANCING], T1.[ELECTRICITY_CONSUMPTION], T1.[ELECTRICITY_EMISSION], T1.[GAS_CONSUMPTION], T1.[GAS_EMISSION], T1.[LOWEST_EMISSION_FUEL], T1.[MODEL_IDENTIFIER], T1.[OPTION_IDENTIFIER], T1.[PIN_CODE], T1.[PIN_IDENTIFIER], T1.[PIN_STATUS], T1.[SUPPORTED_FUEL], T1.[TYPE_APPROVAL_IDENTIFIER], T1.[VEHICLE_DESCRIPTION], T1.[TDK_CALL_DATE], T1.[DATE_ORDER_RECEIVED], T1.[EQUIPMENT_YES_NO], T1.[EMISSION_CODE], T1.[EMISSION_TEXT], T1.[ENGINE_CODE], T1.[ENGINE_TEXT], T1.[OWN_INSURANCE_SHARE], T1.[TRANSMISSION_CODE], T1.[TRANSMISSION_TEXT], T1.[TSV_VERSION], T1.[OWNER_CODE_2], '1' as [Client_DB] FROM [deop01].[dbo].[VEHICLE_EXTENSION] T1  WHERE T1.[timestamp] > convert(binary(8), '%TS%', 1) AND 1 = 1 ORDER BY T1.[timestamp] "
  call bcp_in.bat "VEHICLE_EXTENSION_1" "[temp].[VEHICLE_EXTENSION]" "OPTIMAX"

  call sql_timestamp.bat "VEHICLE_EXTENSION_2" "[OPTIMA].[import].[VEHICLE_EXTENSION]" "2"
  call bcp_queryout.bat "VEHICLE_EXTENSION_2" "SELECT T1.[BASIS_NUMBER], T1.[EXTERNAL_ID], T1.[MOT_TEST], T1.[YELLOW_GREY], T1.[REPLACE_MILEAGE], T1.[LAST_ASU_DATE], T1.[TRIM_TYPE_CODE], T1.[CLASSIFICATION_CD], T1.[FLEET_PRICE_LEVEL], T1.[DETAIL_REG_DATE], T1.[TRADE_IN_MAKE], T1.[TRADE_IN_MILEAGE], T1.[TRADE_IN_COLOUR], T1.[CONV_FLAG], T1.[timestamp], T1.[UNIQUE_IDENT], T1.[ADDR_2_DELIVERY], T1.[CO2_EMISSION], T1.[EURO_EMISSION_STD], T1.[GASSYSTEM], T1.[KILOWATT], T1.[LOGIN_DEALER], T1.[LOGIN_RESV_DEALER], T1.[MAIL_ADDR_DELIVERY], T1.[MODEL_TEXT_2], T1.[NAME_DELIVERY], T1.[NOX_EXHAUST], T1.[PACKAGE_TEXT], T1.[PARTICLE_FILTER], T1.[PICK_SELF], T1.[ZIPCODE_DELIVERY], T1.[COC_NUMBER], T1.[DAM_NUMBER], T1.[DAMAGE_DESCRIPTION], T1.[DAMAGE_PRICE], T1.[GEAR_BOX_TYPE], T1.[LARGER_DAMAGE], T1.[MODEL], T1.[MODEL_VARIANT], T1.[PROD_DATE], T1.[SPECIAL_USE], T1.[WEIGHT], T1.[ADVICE_RECIPIENT], T1.[AIR_CONDITION], T1.[DAT_EURO_CODE], T1.[DRIVER], T1.[DSP_EXPIRY_DATE], T1.[DSP_MAKE_CD], T1.[DSP_NO_OF_SERVICES_USED], T1.[DSP_PROD_CODE], T1.[DSP_REST_AMOUNT], T1.[DSP_START_AMOUNT], T1.[GAS_SYSTEM_INSTALLED], T1.[HANDLER_SYSTEM], T1.[INSURANCE_COMPANY_CHANGED], T1.[LEAS_FINANCING_DOWN_PAY], T1.[LEAS_FINANCING_FINAL_PAY], T1.[LEAS_FINANCING_MONTH_PAY], T1.[LEASING_FINANCING_CONTRACT], T1.[LEASING_FINANCING_EXPIRE], T1.[LEASING_FINANCING_NAME], T1.[LEASING_FINANCING_PERIOD], T1.[LEASING_FINANCING_SALESMAN], T1.[LEASING_FINANCING_START], T1.[MAN_CALL_FOR_SERVICE], T1.[MISC_1_DD], T1.[MISC_2_DD], T1.[MISC_3_DD], T1.[MISC_4_DD], T1.[MISC_5_DD], T1.[MOBILE_SERVICE_EXPIRY_DATE], T1.[MOBILE_SERVICE_TYPE], T1.[NEXT_AIR_CONDITIONING_DATE], T1.[SERVICE_GUARANTEE], T1.[TYRES], T1.[TYRES_LOCATION], T1.[BATTERY_FINANCING], T1.[ELECTRICITY_CONSUMPTION], T1.[ELECTRICITY_EMISSION], T1.[GAS_CONSUMPTION], T1.[GAS_EMISSION], T1.[LOWEST_EMISSION_FUEL], T1.[MODEL_IDENTIFIER], T1.[OPTION_IDENTIFIER], T1.[PIN_CODE], T1.[PIN_IDENTIFIER], T1.[PIN_STATUS], T1.[SUPPORTED_FUEL], T1.[TYPE_APPROVAL_IDENTIFIER], T1.[VEHICLE_DESCRIPTION], T1.[TDK_CALL_DATE], T1.[DATE_ORDER_RECEIVED], T1.[EQUIPMENT_YES_NO], T1.[EMISSION_CODE], T1.[EMISSION_TEXT], T1.[ENGINE_CODE], T1.[ENGINE_TEXT], T1.[OWN_INSURANCE_SHARE], T1.[TRANSMISSION_CODE], T1.[TRANSMISSION_TEXT], T1.[TSV_VERSION], T1.[OWNER_CODE_2], '2' as [Client_DB] FROM [deop02].[dbo].[VEHICLE_EXTENSION] T1  WHERE T1.[timestamp] > convert(binary(8), '%TS%', 1) AND 1 = 1 ORDER BY T1.[timestamp] "
  call bcp_in.bat "VEHICLE_EXTENSION_2" "[temp].[VEHICLE_EXTENSION]" "OPTIMAX"

  call sql_query.bat "DELETE T1 FROM [OPTIMA].[import].[VEHICLE_EXTENSION] T1 INNER JOIN [OPTIMAX].[temp].[VEHICLE_EXTENSION] T2 ON T1.[BASIS_NUMBER] = T2.[BASIS_NUMBER] AND T1.[CLIENT_DB] = T2.[CLIENT_DB]"
  call sql_query.bat "INSERT INTO [OPTIMA].[import].[VEHICLE_EXTENSION] with (TABLOCK) SELECT * FROM [OPTIMAX].[temp].[VEHICLE_EXTENSION] T1"

:cleanup
  call delete.bat "C:\GlobalCube\System\OPTIMA\SQL\temp\VEHICLE_EXTENSION_1.csv"
  call delete.bat "C:\GlobalCube\System\OPTIMA\SQL\temp\VEHICLE_EXTENSION_2.csv"