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

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

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

:full
  call sql_query.bat "TRUNCATE TABLE [OPTIMA].[import].[ORDER_LINE]"
rem Nur in Quelle: PAYER_PERCENTAGE;REDUCTION_FACTOR_1;WORKSHOP_TEAM;TAX_CODE_1;TAX_C_U;KIT_MISC_DISC_PER;CLAIM_CODE_IND;PERSON_CODE;BACK_ORDER_TEXT_LINE;LOCATION;KIT_PRICE_DIFF;TIME_CODE_1;CCC_10_CHILD_2;MISC_4;DISCOUNT_CODE;END_DATE;KIT_GROUP;MISC_3;QTY_SOLD_NEGATIVE;WORK_DATE;POLICY_ORDER_LINE;W_MAIN_REPAIR_NO;REPAIR_LOCATION_CODE;SALES_PRICE_INCL;RESULTING_LINE;SALES_UNIT_ALT;ORIG_PROD_GROUP;CCC_CATEGORY;KIT_PARTS_DISC_PER;LINE_REFERENCE;DISCOUNT_TABLE;LOCATION_3;TIME_CODE_2;TYPE_OL;CLAIM_TROUBLE_CD_OLD;DISCOUNT_PERC_2;KIT_TEXT;LINE_TEXTLINE_1;EXCLUDED_FROM_GAIN_CALC;PRE_PLANNED_ORDER;EXTERNAL_STOCK;PURCH_ORDER_STATUS;USED_TIME_TYPE_1;COST_PRICE_TYPE_2;FLAT_SEARCH_2;TRANSACT_DATE;LINE_TEXTLINE_2;MENU_TYPE;LINE_TEXTLINE_3;CCC;INV_TIME_COST;LINE_GROUP_CODE;MISC_6;DEDUCTIBLE_VAT;CAMPAIGN_CODE;KIT_ONE_LINEPRICE;DEDUCTABLE;FLAT_SEARCH_7;INTERNAL_TRANSFER;ACCUMULATED_MENU_CHANGES;INV_TOTAL_NUMBER;FLAT_SEARCH_8;SPLIT_AM_SUB1;QUANTITY_DISC_CODE;ORIG_ORDER_NO;STOCK;MISC_2;KIT_FIXED_PRICE_CD;BACK_REG_CODE;BATTERY_EXCHANGE;DISCOUNT_PERC;KIT_FIXED_PRICE;DECIMAL_INDICATOR;EXTERNAL_PICK_IN_PROGRESS;LOCATION_2;CLAIM_RETURN_CODE;LINE_COMMENT;TEXT_200;AUTOMATIC_CREATED;START_TIME;STDPRICE_2;N_MAIN_PART_NO;STATISTIC_CODE;REPEAT_REPAIR;DISCOUNT_ALLOWANCE;KIT_ONE_LINE;MISC_1;USED_TIME_PAY_REPAIR;MARKETING_RESPONSE_CODE;KIT_PRINT_SPLIT;WORKSHOP_MODEL;SALES_UNIT_STD;REDUCTION_POS;CCC_2;SPLIT_TYPE;REPAIR_CODE_ACCU;REPAIR_GROUP_TYPE;ORIG_COST_PRICE;EXTERNAL_ORIGIN;TRANSACTION_CODE_2;QTY_OWNERSHIP;CCC_10_CHILD_1;MISC_5;ORIG_LINE_NO;BUYERS_REFERENCE;R_DELIVERY_DATE;SPECIAL_PRICE;CUSTOMER_COMPLAINT;DELIVERY_DATE;RESULTING_LINE_1;CCC_10_CHILD_3;CASH_ACCOUNT;ORDER_LINETYPE_2;SPEC_PURCH_PRICE;RESULTING_LINE_2;KIT_CODE;SPLIT_AM_SUB2;KEY_PROD_CODE;REPAIR_POS;PURCH_ORDER_NUMBER;LINE_CODE;FLAT_SEARCH_5;PROGRAM;FACTOR_CODE;KEY_MAKE_CD;ORDER_LINETYPE_4;ORDER_LINETYPE_3;REQUISITION_NUMBER;ORIG_PROD_CODE;LINE_MISC_ADDS;DEPOSIT_TYPE;ADD_COST_CODE;BATTERY_SERIAL_NO;TXT_OL;BACK_ORDER_CD;EXCHANGE_PART;CLAIM_NUMBER;REPAIR_CODE_2;CONV_FLAG;VAT_RATE_CODE;STOCK_SHOP_LIST;WORKSHOP_AREA;JOB_CODE_DESCR;OIL;INSPECTION_2;MARKETING_CAMPAIGN_ID;FLAT_SEARCH_9;PRICE_A_G;PICKING_LIST_NO;PART_GROUP;BACK_ORDER;TIME_CODE;SPLIT_CODE;WORK_TYPE_CODE;TAX_DRAWN;ORDER_QUANTITY_ALT_UNIT;REDUCTION_CODE_2;STATE_CODE;ORIG_CREATION_CODE;COST_PRICE_WORK;GROSS_DISCOUNT;END_TIME;LINE_STATUS;SPECIAL_ORDER_ITEM;KIT_WORK_DISC_PER;HANDLER;CCC_10_PARENT;INCOMPLETE_LINE_2;SORT_LINE_NUMBER;SUPPLIER_CODE;PRINT_INVOICE;PROMOTION_DISCOUNT_PCT;PROMOTION_NUMBER;ALTERNATIVE_PRICE;PICKED;LINE_WORKSHOP;KIT_CATALOG_CODE;FLAT_SEARCH_4;JOB_CODE;KIT_DELETE;FLAT_SEARCH_6;REQUESTED_QUANTITY;RE_PURCHASE_PRICE;AMOUNT_OL;SPLIT_CODE_2;LINE_MISC_ADDS_PER;CLAIM_TROUBLE_CD;CREATE_MSC;PURCH_ORDER_SUPPLIER_CODE;RATE_PRICE_CODE;INSPECTION_1;CREATE_AFTER_RC;SPLIT_AM_SUB3;ORDER_LINE_TRANSM;FIXED_PRICE_REPAIR;CONSIGNMENT_CODE;WUST_CODE;TIME_RATE;ECA_CONTRACT_CODE;CLASS_RULE;FLAT_SEARCH_3;COST_PRICE_TYPE_1;SMALL_ACCESSORIES;COSTPRICE_TAX;CCC_10_CHILD_5;SPLIT_AM_MAIN;ECA_SERVICE_CODE;ORIG_MAKE_CD;EXCHANGE_RETURN_CODE;RECOMMENDED_PRICE;CASE_NO;STATUS;REPLACE_CODE;SPECIAL_LOCK_NO;PURCH_LINE_NUMBER;SALES_PRICE;CCC_10_CHILD_4;USED_TIME_TYPE_2;FUNCTION_CODE;LINE_TEXT_CODE;REQUISITION_NUM;INCOMPLETE_LINE
  call bcp_queryout.bat "ORDER_LINE_1" "SELECT T1.[ORDER_NUMBER], T1.[LINE_NUMBER], T1.[ORDER_LINETYPE], T1.[REDUCTION_CODE], T1.[REDUCTION_AMOUNT], T1.[MECHANIC_CODE], T1.[SALESMAN], T1.[DISCOUNT], T1.[STDPRICE], T1.[LINES_NET_VALUE], T1.[PROD_CODE], T1.[MAKE_CD], T1.[PRODUCT_GROUP], T1.[PROD_NAME], T1.[ORDER_QUANTITY], T1.[DELIVERY_QUANTITY], T1.[LINE_COSTS], T1.[REPAIR_CODE], T1.[REPAIR_GROUP], T1.[REPAIR_NAME], T1.[USED_TIME], T1.[EST_TIME], T1.[INV_TIME], T1.[USED_TIME_INT], T1.[EST_TIME_INT], T1.[INV_TIME_INT], T1.[MAKE_TIME_UNIT], T1.[timestamp], T1.[UNIQUE_IDENT], '1' as [Client_DB] FROM [deop01].[dbo].[ORDER_LINE] T1 INNER JOIN [deop01].[dbo].[ORDER_HEADER] T2 ON T1.[ORDER_NUMBER] = T2.[ORDER_NUMBER] WHERE (T2.[INVOICE_DATE] >= '2020-01-01T00:00:00' OR T2.[INVOICE_DATE] = '01.01.1800') ORDER BY T1.[timestamp] "
  call bcp_in.bat "ORDER_LINE_1" "[import].[ORDER_LINE]" "OPTIMA"
rem Nur in Quelle: PAYER_PERCENTAGE;REDUCTION_FACTOR_1;WORKSHOP_TEAM;TAX_CODE_1;TAX_C_U;KIT_MISC_DISC_PER;CLAIM_CODE_IND;PERSON_CODE;BACK_ORDER_TEXT_LINE;LOCATION;KIT_PRICE_DIFF;TIME_CODE_1;CCC_10_CHILD_2;MISC_4;DISCOUNT_CODE;END_DATE;KIT_GROUP;MISC_3;QTY_SOLD_NEGATIVE;WORK_DATE;POLICY_ORDER_LINE;W_MAIN_REPAIR_NO;REPAIR_LOCATION_CODE;SALES_PRICE_INCL;RESULTING_LINE;SALES_UNIT_ALT;ORIG_PROD_GROUP;CCC_CATEGORY;KIT_PARTS_DISC_PER;LINE_REFERENCE;DISCOUNT_TABLE;LOCATION_3;TIME_CODE_2;TYPE_OL;CLAIM_TROUBLE_CD_OLD;DISCOUNT_PERC_2;KIT_TEXT;LINE_TEXTLINE_1;EXCLUDED_FROM_GAIN_CALC;PRE_PLANNED_ORDER;EXTERNAL_STOCK;PURCH_ORDER_STATUS;USED_TIME_TYPE_1;COST_PRICE_TYPE_2;FLAT_SEARCH_2;TRANSACT_DATE;LINE_TEXTLINE_2;MENU_TYPE;LINE_TEXTLINE_3;CCC;INV_TIME_COST;LINE_GROUP_CODE;MISC_6;DEDUCTIBLE_VAT;CAMPAIGN_CODE;KIT_ONE_LINEPRICE;DEDUCTABLE;FLAT_SEARCH_7;INTERNAL_TRANSFER;ACCUMULATED_MENU_CHANGES;INV_TOTAL_NUMBER;FLAT_SEARCH_8;SPLIT_AM_SUB1;QUANTITY_DISC_CODE;ORIG_ORDER_NO;STOCK;MISC_2;KIT_FIXED_PRICE_CD;BACK_REG_CODE;BATTERY_EXCHANGE;DISCOUNT_PERC;KIT_FIXED_PRICE;DECIMAL_INDICATOR;EXTERNAL_PICK_IN_PROGRESS;LOCATION_2;CLAIM_RETURN_CODE;LINE_COMMENT;TEXT_200;AUTOMATIC_CREATED;START_TIME;STDPRICE_2;N_MAIN_PART_NO;STATISTIC_CODE;REPEAT_REPAIR;DISCOUNT_ALLOWANCE;KIT_ONE_LINE;MISC_1;USED_TIME_PAY_REPAIR;MARKETING_RESPONSE_CODE;KIT_PRINT_SPLIT;WORKSHOP_MODEL;SALES_UNIT_STD;REDUCTION_POS;CCC_2;SPLIT_TYPE;REPAIR_CODE_ACCU;REPAIR_GROUP_TYPE;ORIG_COST_PRICE;EXTERNAL_ORIGIN;TRANSACTION_CODE_2;QTY_OWNERSHIP;CCC_10_CHILD_1;MISC_5;ORIG_LINE_NO;BUYERS_REFERENCE;R_DELIVERY_DATE;SPECIAL_PRICE;CUSTOMER_COMPLAINT;DELIVERY_DATE;RESULTING_LINE_1;CCC_10_CHILD_3;CASH_ACCOUNT;ORDER_LINETYPE_2;SPEC_PURCH_PRICE;RESULTING_LINE_2;KIT_CODE;SPLIT_AM_SUB2;KEY_PROD_CODE;REPAIR_POS;PURCH_ORDER_NUMBER;LINE_CODE;FLAT_SEARCH_5;PROGRAM;FACTOR_CODE;KEY_MAKE_CD;ORDER_LINETYPE_4;ORDER_LINETYPE_3;REQUISITION_NUMBER;ORIG_PROD_CODE;LINE_MISC_ADDS;DEPOSIT_TYPE;ADD_COST_CODE;BATTERY_SERIAL_NO;TXT_OL;BACK_ORDER_CD;EXCHANGE_PART;CLAIM_NUMBER;REPAIR_CODE_2;CONV_FLAG;VAT_RATE_CODE;STOCK_SHOP_LIST;WORKSHOP_AREA;JOB_CODE_DESCR;OIL;INSPECTION_2;MARKETING_CAMPAIGN_ID;FLAT_SEARCH_9;PRICE_A_G;PICKING_LIST_NO;PART_GROUP;BACK_ORDER;TIME_CODE;SPLIT_CODE;WORK_TYPE_CODE;TAX_DRAWN;ORDER_QUANTITY_ALT_UNIT;REDUCTION_CODE_2;STATE_CODE;ORIG_CREATION_CODE;COST_PRICE_WORK;GROSS_DISCOUNT;END_TIME;LINE_STATUS;SPECIAL_ORDER_ITEM;KIT_WORK_DISC_PER;HANDLER;CCC_10_PARENT;INCOMPLETE_LINE_2;SORT_LINE_NUMBER;SUPPLIER_CODE;PRINT_INVOICE;PROMOTION_DISCOUNT_PCT;PROMOTION_NUMBER;ALTERNATIVE_PRICE;PICKED;LINE_WORKSHOP;KIT_CATALOG_CODE;FLAT_SEARCH_4;JOB_CODE;KIT_DELETE;FLAT_SEARCH_6;REQUESTED_QUANTITY;RE_PURCHASE_PRICE;AMOUNT_OL;SPLIT_CODE_2;LINE_MISC_ADDS_PER;CLAIM_TROUBLE_CD;CREATE_MSC;PURCH_ORDER_SUPPLIER_CODE;RATE_PRICE_CODE;INSPECTION_1;CREATE_AFTER_RC;SPLIT_AM_SUB3;ORDER_LINE_TRANSM;FIXED_PRICE_REPAIR;CONSIGNMENT_CODE;WUST_CODE;TIME_RATE;ECA_CONTRACT_CODE;CLASS_RULE;FLAT_SEARCH_3;COST_PRICE_TYPE_1;SMALL_ACCESSORIES;COSTPRICE_TAX;CCC_10_CHILD_5;SPLIT_AM_MAIN;ECA_SERVICE_CODE;ORIG_MAKE_CD;EXCHANGE_RETURN_CODE;RECOMMENDED_PRICE;CASE_NO;STATUS;REPLACE_CODE;SPECIAL_LOCK_NO;PURCH_LINE_NUMBER;SALES_PRICE;CCC_10_CHILD_4;USED_TIME_TYPE_2;FUNCTION_CODE;LINE_TEXT_CODE;REQUISITION_NUM;INCOMPLETE_LINE
  call bcp_queryout.bat "ORDER_LINE_2" "SELECT T1.[ORDER_NUMBER], T1.[LINE_NUMBER], T1.[ORDER_LINETYPE], T1.[REDUCTION_CODE], T1.[REDUCTION_AMOUNT], T1.[MECHANIC_CODE], T1.[SALESMAN], T1.[DISCOUNT], T1.[STDPRICE], T1.[LINES_NET_VALUE], T1.[PROD_CODE], T1.[MAKE_CD], T1.[PRODUCT_GROUP], T1.[PROD_NAME], T1.[ORDER_QUANTITY], T1.[DELIVERY_QUANTITY], T1.[LINE_COSTS], T1.[REPAIR_CODE], T1.[REPAIR_GROUP], T1.[REPAIR_NAME], T1.[USED_TIME], T1.[EST_TIME], T1.[INV_TIME], T1.[USED_TIME_INT], T1.[EST_TIME_INT], T1.[INV_TIME_INT], T1.[MAKE_TIME_UNIT], T1.[timestamp], T1.[UNIQUE_IDENT], '2' as [Client_DB] FROM [deop02].[dbo].[ORDER_LINE] T1 INNER JOIN [deop02].[dbo].[ORDER_HEADER] T2 ON T1.[ORDER_NUMBER] = T2.[ORDER_NUMBER] WHERE (T2.[INVOICE_DATE] >= '2020-01-01T00:00:00' OR T2.[INVOICE_DATE] = '01.01.1800') ORDER BY T1.[timestamp] "
  call bcp_in.bat "ORDER_LINE_2" "[import].[ORDER_LINE]" "OPTIMA"
  goto :cleanup

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

  call sql_timestamp.bat "ORDER_LINE_1" "[OPTIMA].[import].[ORDER_LINE]" "1"
  call bcp_queryout.bat "ORDER_LINE_1" "SELECT T1.[ORDER_NUMBER], T1.[LINE_NUMBER], T1.[ORDER_LINETYPE], T1.[REDUCTION_CODE], T1.[REDUCTION_AMOUNT], T1.[MECHANIC_CODE], T1.[SALESMAN], T1.[DISCOUNT], T1.[STDPRICE], T1.[LINES_NET_VALUE], T1.[PROD_CODE], T1.[MAKE_CD], T1.[PRODUCT_GROUP], T1.[PROD_NAME], T1.[ORDER_QUANTITY], T1.[DELIVERY_QUANTITY], T1.[LINE_COSTS], T1.[REPAIR_CODE], T1.[REPAIR_GROUP], T1.[REPAIR_NAME], T1.[USED_TIME], T1.[EST_TIME], T1.[INV_TIME], T1.[USED_TIME_INT], T1.[EST_TIME_INT], T1.[INV_TIME_INT], T1.[MAKE_TIME_UNIT], T1.[timestamp], T1.[UNIQUE_IDENT], '1' as [Client_DB] FROM [deop01].[dbo].[ORDER_LINE] T1 INNER JOIN [deop01].[dbo].[ORDER_HEADER] T2 ON T1.[ORDER_NUMBER] = T2.[ORDER_NUMBER] WHERE T1.[timestamp] > convert(binary(8), '%TS%', 1) AND (T2.[INVOICE_DATE] >= '2020-01-01T00:00:00' OR T2.[INVOICE_DATE] = '01.01.1800') ORDER BY T1.[timestamp] "
  call bcp_in.bat "ORDER_LINE_1" "[temp].[ORDER_LINE]" "OPTIMAX"

  call sql_timestamp.bat "ORDER_LINE_2" "[OPTIMA].[import].[ORDER_LINE]" "2"
  call bcp_queryout.bat "ORDER_LINE_2" "SELECT T1.[ORDER_NUMBER], T1.[LINE_NUMBER], T1.[ORDER_LINETYPE], T1.[REDUCTION_CODE], T1.[REDUCTION_AMOUNT], T1.[MECHANIC_CODE], T1.[SALESMAN], T1.[DISCOUNT], T1.[STDPRICE], T1.[LINES_NET_VALUE], T1.[PROD_CODE], T1.[MAKE_CD], T1.[PRODUCT_GROUP], T1.[PROD_NAME], T1.[ORDER_QUANTITY], T1.[DELIVERY_QUANTITY], T1.[LINE_COSTS], T1.[REPAIR_CODE], T1.[REPAIR_GROUP], T1.[REPAIR_NAME], T1.[USED_TIME], T1.[EST_TIME], T1.[INV_TIME], T1.[USED_TIME_INT], T1.[EST_TIME_INT], T1.[INV_TIME_INT], T1.[MAKE_TIME_UNIT], T1.[timestamp], T1.[UNIQUE_IDENT], '2' as [Client_DB] FROM [deop02].[dbo].[ORDER_LINE] T1 INNER JOIN [deop02].[dbo].[ORDER_HEADER] T2 ON T1.[ORDER_NUMBER] = T2.[ORDER_NUMBER] WHERE T1.[timestamp] > convert(binary(8), '%TS%', 1) AND (T2.[INVOICE_DATE] >= '2020-01-01T00:00:00' OR T2.[INVOICE_DATE] = '01.01.1800') ORDER BY T1.[timestamp] "
  call bcp_in.bat "ORDER_LINE_2" "[temp].[ORDER_LINE]" "OPTIMAX"

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

:cleanup
  call delete.bat "C:\GlobalCube\System\OPTIMA\SQL\temp\ORDER_LINE_1.csv"
  call delete.bat "C:\GlobalCube\System\OPTIMA\SQL\temp\ORDER_LINE_2.csv"