106 lines
4.7 KiB
SQL
106 lines
4.7 KiB
SQL
ALTER VIEW "EMOBILITY"."V_CHARGEDATA_EMP_EOPERATE" ( "ID", "CALLID", "CDRID", "EVSEID", "CONTRACTID", "RFID", "INSTANCE", "TOKENTYPE", "CONSUMEDENERGY",
|
|
"OPERATORID", "OPERATORNAME", "OPERATORSUBMANDANT", "FREECHARGE", "CHARGEPOINTTYPE", "METERID", "MAXSOCKETPOWER", "ADDRESS", "ZIP", "CITY", "COUNTRY", "PROVIDERID",
|
|
"PROVIDERNAME", "CHARGINGSTARTTIME", "CHARGINGSTOPTIME", "PARKINGSTARTTIME", "PARKINGSTOPTIME", "METERSTART", "METERSTOP", "TARIFF", "CURRENCY", "VAT",
|
|
"BILLINGVALUEBRUTTO", "BILLINGVALUENETTO", "PARKINGTIMEPRICEBRUTTO", "PARKINGTIMEPRICENETTO", "ADITIONALPRICEBRUTTO", "ADITIONALPRICENETTO", "KWHNETPRICE",
|
|
"MINUTENETPRICE", "SESSIONNETPRICE", "DURATION", "EICHRECHTTYPE", "MILEAGE", "SUBSCRIPTION_ID", "CREATE_TS", "UPDATE_TS", "STATUS", "BILLING", "SYSTYPE", "VIEWVERSION"
|
|
) AS
|
|
select
|
|
'EOP' || "BDR_ID",
|
|
"BDR_ID",
|
|
"BDR_ID",
|
|
"CHARGEPOINT_ID",
|
|
"EMAID",
|
|
UPPER("UNIQUE_ID"),
|
|
UPPER("UNIQUE_ID"),
|
|
"CHARGE_DATA_AUTHORIZATION_TYPE",
|
|
"CHARGE_DATA_CONSUMPTION_KWH" * 1000,
|
|
"CHARGEPOINT_OPERATOR_ID",
|
|
"CHARGEPOINT_OPERATOR_NAME",
|
|
'OPERATORSUBMANDANT',
|
|
'false',
|
|
"CHARGEPOINT_TYPE",
|
|
"CHARGEPOINT_METER_ID",
|
|
0,
|
|
"CHARGEPOINT_ADDRESS_STREET",
|
|
"CHARGEPOINT_ADDRESS_POSTALCODE",
|
|
"CHARGEPOINT_ADDRESS_CITY",
|
|
"CHARGEPOINT_ADDRESS_COUNTRY",
|
|
"EMOBILITY_PROVIDER_ID",
|
|
"EMOBILITY_PROVIDER_NAME",
|
|
"CHARGE_DATA_BEGIN_UTC",
|
|
"CHARGE_DATA_END_UTC",
|
|
"CHARGE_DATA_BEGIN_UTC",
|
|
"CHARGE_DATA_END_UTC",
|
|
0,
|
|
0,
|
|
'TARIFF',
|
|
CASE
|
|
WHEN "PRICING_ITEMS_1_TYPE" = 'CUS' THEN "PRICING_ITEMS_1_CURRENCY"
|
|
WHEN "PRICING_ITEMS_2_TYPE" = 'CUS' THEN "PRICING_ITEMS_2_CURRENCY"
|
|
WHEN "PRICING_ITEMS_3_TYPE" = 'CUS' THEN "PRICING_ITEMS_3_CURRENCY"
|
|
WHEN "PRICING_ITEMS_4_TYPE" = 'CUS' THEN "PRICING_ITEMS_4_CURRENCY"
|
|
ELSE NULL
|
|
END,
|
|
0,
|
|
0,
|
|
CASE
|
|
WHEN "PRICING_ITEMS_1_TYPE" = 'CUS' THEN "PRICING_ITEMS_1_NET_AMOUNT"
|
|
WHEN "PRICING_ITEMS_2_TYPE" = 'CUS' THEN "PRICING_ITEMS_2_NET_AMOUNT"
|
|
WHEN "PRICING_ITEMS_3_TYPE" = 'CUS' THEN "PRICING_ITEMS_3_NET_AMOUNT"
|
|
WHEN "PRICING_ITEMS_4_TYPE" = 'CUS' THEN "PRICING_ITEMS_4_NET_AMOUNT"
|
|
ELSE NULL
|
|
END,
|
|
0,
|
|
0,
|
|
0,
|
|
0,
|
|
CASE
|
|
WHEN "PRICING_ITEMS_1_TYPE" = 'CUS' AND "PRICING_ITEMS_1_UNIT" = 'KWH' THEN "PRICING_ITEMS_1_UNIT_PRICE"
|
|
WHEN "PRICING_ITEMS_2_TYPE" = 'CUS' AND "PRICING_ITEMS_2_UNIT" = 'KWH' THEN "PRICING_ITEMS_2_UNIT_PRICE"
|
|
WHEN "PRICING_ITEMS_3_TYPE" = 'CUS' AND "PRICING_ITEMS_3_UNIT" = 'KWH' THEN "PRICING_ITEMS_3_UNIT_PRICE"
|
|
WHEN "PRICING_ITEMS_4_TYPE" = 'CUS' AND "PRICING_ITEMS_4_UNIT" = 'KWH' THEN "PRICING_ITEMS_4_UNIT_PRICE"
|
|
ELSE NULL
|
|
END,
|
|
CASE
|
|
WHEN "PRICING_ITEMS_1_TYPE" = 'CUS' AND "PRICING_ITEMS_1_UNIT" = 'MIN' THEN "PRICING_ITEMS_1_UNIT_PRICE"
|
|
WHEN "PRICING_ITEMS_2_TYPE" = 'CUS' AND "PRICING_ITEMS_2_UNIT" = 'MIN' THEN "PRICING_ITEMS_2_UNIT_PRICE"
|
|
WHEN "PRICING_ITEMS_3_TYPE" = 'CUS' AND "PRICING_ITEMS_3_UNIT" = 'MIN' THEN "PRICING_ITEMS_3_UNIT_PRICE"
|
|
WHEN "PRICING_ITEMS_4_TYPE" = 'CUS' AND "PRICING_ITEMS_4_UNIT" = 'MIN' THEN "PRICING_ITEMS_4_UNIT_PRICE"
|
|
ELSE NULL
|
|
END,
|
|
CASE
|
|
WHEN "PRICING_ITEMS_1_TYPE" = 'CUS' AND "PRICING_ITEMS_1_UNIT" = 'ST' THEN "PRICING_ITEMS_1_UNIT_PRICE"
|
|
WHEN "PRICING_ITEMS_2_TYPE" = 'CUS' AND "PRICING_ITEMS_2_UNIT" = 'ST' THEN "PRICING_ITEMS_2_UNIT_PRICE"
|
|
WHEN "PRICING_ITEMS_3_TYPE" = 'CUS' AND "PRICING_ITEMS_3_UNIT" = 'ST' THEN "PRICING_ITEMS_3_UNIT_PRICE"
|
|
WHEN "PRICING_ITEMS_4_TYPE" = 'CUS' AND "PRICING_ITEMS_4_UNIT" = 'ST' THEN "PRICING_ITEMS_4_UNIT_PRICE"
|
|
ELSE NULL
|
|
END,
|
|
CASE
|
|
WHEN "PRICING_ITEMS_1_TYPE" = 'CUS' AND "PRICING_ITEMS_1_UNIT" = 'MIN' THEN "PRICING_ITEMS_1_QUANTITY"
|
|
WHEN "PRICING_ITEMS_2_TYPE" = 'CUS' AND "PRICING_ITEMS_2_UNIT" = 'MIN' THEN "PRICING_ITEMS_2_QUANTITY"
|
|
WHEN "PRICING_ITEMS_3_TYPE" = 'CUS' AND "PRICING_ITEMS_3_UNIT" = 'MIN' THEN "PRICING_ITEMS_3_QUANTITY"
|
|
WHEN "PRICING_ITEMS_4_TYPE" = 'CUS' AND "PRICING_ITEMS_4_UNIT" = 'MIN' THEN "PRICING_ITEMS_4_QUANTITY"
|
|
ELSE 0
|
|
END,
|
|
"CHARGE_DATA_EKA_STATUS",
|
|
0,
|
|
"SUBSCRIPTION_ID",
|
|
"CREATE_TS",
|
|
"UPDATE_TS",
|
|
'','Y',
|
|
'EOPERATE',
|
|
'V 1.4/28.09.2020'
|
|
from "EOPERATE"."CHARGEDATA"
|
|
where
|
|
(
|
|
"PRICING_ITEMS_1_TYPE" = 'CUS' AND ("PRICING_ITEMS_1_QUANTITY" > 2 OR "PRICING_ITEMS_1_UNIT" != 'MIN')
|
|
OR
|
|
"PRICING_ITEMS_2_TYPE" = 'CUS' AND ("PRICING_ITEMS_2_QUANTITY" > 2 OR "PRICING_ITEMS_2_UNIT" != 'MIN')
|
|
OR
|
|
"PRICING_ITEMS_3_TYPE" = 'CUS' AND ("PRICING_ITEMS_3_QUANTITY" > 2 OR "PRICING_ITEMS_3_UNIT" != 'MIN')
|
|
OR
|
|
"PRICING_ITEMS_4_TYPE" = 'CUS' AND ("PRICING_ITEMS_4_QUANTITY" > 2 OR "PRICING_ITEMS_4_UNIT" != 'MIN')
|
|
)
|
|
and "CHARGE_DATA_CONSUMPTION_KWH" > 0
|
|
and "SUBSCRIPTION_ID" <> 'SU20200729002'
|
|
--and "PLUGTYPE" IS NOT NULL |