269 lines
4.1 KiB
SQL
269 lines
4.1 KiB
SQL
--V1.1 08.01.2020
|
|
create schema EMOBILITY;
|
|
|
|
ALTER VIEW "EMOBILITY"."V_CHARGEDATA_EMP" (
|
|
--Version:1.0.4 10.01.2020
|
|
"ID",
|
|
"CALLID",
|
|
"CDRID",
|
|
"EVSEID",
|
|
"CONTRACTID",
|
|
"RFID",
|
|
"INSTANCE",
|
|
"TOKENTYPE",
|
|
"CONSUMEDENERGY",
|
|
"OPERATORID",
|
|
"OPERATORNAME",
|
|
"OPERATORSUBMANDANT",
|
|
"FREECHARGE",
|
|
"CHARGEPOINTTYPE",
|
|
"MAXSOCKETPOWER",
|
|
"ADDRESS",
|
|
"ZIP",
|
|
"CITY",
|
|
"COUNTRY",
|
|
"PROVIDERID",
|
|
"PROVIDERNAME",
|
|
"CHARGINGSTARTTIME",
|
|
"CHARGINGSTOPTIME",
|
|
"PARKINGSTARTTIME",
|
|
"PARKINGSTOPTIME",
|
|
"METERSTART",
|
|
"METERSTOP",
|
|
"TARIFF",
|
|
"CURRENCY",
|
|
"VAT",
|
|
"BILLINGVALUEBRUTTO",
|
|
"BILLINGVALUENETTO",
|
|
"PARKINGTIMEPRICEBRUTTO",
|
|
"PARKINGTIMEPRICENETTO",
|
|
"ADITIONALPRICEBRUTTO",
|
|
"ADITIONALPRICENETTO",
|
|
"CREATE_TS",
|
|
"UPDATE_TS",
|
|
"STATUS",
|
|
"BILLING",
|
|
"SYSTYPE" ) AS select
|
|
"UUID",
|
|
"UUID",
|
|
"UUID",
|
|
"FULLEVSEID",
|
|
"CUSTOMERID",
|
|
UPPER("RFID"),
|
|
UPPER("RFID"),
|
|
'RFID',
|
|
IFNULL("WATTHOUR",
|
|
0),
|
|
'OPERATORID',
|
|
'OPERATORNAME',
|
|
'true',
|
|
CASE "FREECHARGING" WHEN 'yes'
|
|
THEN 'true' WHEN 'no'
|
|
then 'false'
|
|
ELSE ''
|
|
END as FREECHARGE,
|
|
"PLUGTYPE",
|
|
0,
|
|
--'MAXSOCKETPOWER',
|
|
"LOCATION_ADDRESS",
|
|
'',
|
|
--LOCATION ZIP
|
|
"LOCATION_CITY",
|
|
'DE',
|
|
'PROVIDERID',
|
|
'PROVIDERNAME',
|
|
|
|
left("STARTTIME",
|
|
19),
|
|
|
|
left("STOPTIME",
|
|
19),
|
|
|
|
left("STARTTIME",
|
|
19),
|
|
|
|
left("STOPTIME",
|
|
19),
|
|
IFNULL( "METERSTART",
|
|
0),
|
|
IFNULL( "METERSTOP",
|
|
0),
|
|
'TARIFF',
|
|
"CURRENCY",
|
|
0,
|
|
--'VAT',
|
|
0,
|
|
--'BILLINGVALUEBRUTTO',
|
|
0,
|
|
--'BILLINGVALUENETTO',
|
|
0,
|
|
--'PARKINGTIMEPRICEBRUTTO',
|
|
0,
|
|
--'PARKINGTIMEPRICENETTO',
|
|
0,
|
|
--'ADITIONALPRICEBRUTTO',
|
|
0,
|
|
--'ADITIONALPRICENETTO',
|
|
--'B2CPRICING',
|
|
--'ASSETOWNERPRICING',
|
|
--'CPOPRICING',
|
|
/* "STATIONUUID",
|
|
"CUSTOMERID",
|
|
"STARTTIME",
|
|
"STOPTIME",
|
|
"DURATION",
|
|
"WATTHOUR",
|
|
"AMOUNT",
|
|
"CURRENCY",
|
|
"TRANSACTIONTYPE",
|
|
"CREATED",
|
|
"RFID",
|
|
"STATION",
|
|
"CUSTOMERGROUPID",
|
|
"CUSTOMERGROUPNAME",
|
|
"ORGANIZATIONID",
|
|
"PAYMENTTYPE",
|
|
"EVSEID",
|
|
"PLUGTYPE",
|
|
"EMPSHARE",
|
|
"SIGNEDMETERVALUEBEGIN",
|
|
"SIGNEDMETERVALUEEND",
|
|
"OPERATORPRICE",
|
|
"OPERATORCURRENCY",
|
|
"OPERATORID",
|
|
"FREECHARGING",
|
|
"EMPTARIFFID",
|
|
"EMPTARIFFNAME",
|
|
"FULLEVSEID",
|
|
"METERSTART",
|
|
"METERSTOP",
|
|
"NOTES",
|
|
"MILEAGE",
|
|
*/ --"JSONRAW",
|
|
"CREATE_TS",
|
|
"UPDATE_TS",
|
|
"STATUS",
|
|
"BILLING",
|
|
'VIRTAPORTAL'
|
|
from "VIRTAPORTAL"."EMP_TRANSACTIONS"
|
|
where "TRANSACTIONTYPE" = 'charge'
|
|
and "WATTHOUR" > 0
|
|
and "DURATION" > 2
|
|
and "PLUGTYPE" IS NOT NULL;
|
|
|
|
|
|
|
|
|
|
|
|
|
|
ALTER VIEW "EMOBILITY"."V_CHARGEDATA_CPO" (
|
|
--V1.0.0 10.01.2020
|
|
"ID",
|
|
"CALLID",
|
|
"CDRID",
|
|
"EVSEID",
|
|
"CONTRACTID",
|
|
"RFID",
|
|
"INSTANCE",
|
|
"TOKENTYPE",
|
|
"CONSUMEDENERGY",
|
|
"OPERATORID",
|
|
|
|
"OPERATORNAME",
|
|
"OPERATORSUBMANDANT",
|
|
"FREECHARGE",
|
|
"CHARGEPOINTTYPE",
|
|
"MAXSOCKETPOWER",
|
|
"ADDRESS",
|
|
"ZIP",
|
|
"CITY",
|
|
"COUNTRY",
|
|
"PROVIDERID",
|
|
|
|
"PROVIDERNAME",
|
|
"CHARGINGSTARTTIME",
|
|
"CHARGINGSTOPTIME",
|
|
"PARKINGSTARTTIME",
|
|
"PARKINGSTOPTIME",
|
|
"METERSTART",
|
|
"METERSTOP",
|
|
"TARIFF",
|
|
"CURRENCY",
|
|
"VAT",
|
|
|
|
"BILLINGVALUEBRUTTO",
|
|
"BILLINGVALUENETTO",
|
|
"PARKINGTIMEPRICEBRUTTO",
|
|
"PARKINGTIMEPRICENETTO",
|
|
"ADITIONALPRICEBRUTTO",
|
|
"ADITIONALPRICENETTO",
|
|
|
|
|
|
"CREATE_TS",
|
|
"UPDATE_TS",
|
|
"STATUS",
|
|
"BILLING",
|
|
"SYSTYPE" ) AS select
|
|
|
|
"CDRID",
|
|
"UUID",
|
|
"CDRID",
|
|
"FULLEVSEID",
|
|
"EMPSELLERID",
|
|
UPPER("RFID"),
|
|
UPPER("RFID"),
|
|
'RFID',
|
|
IFNULL("WATTHOUR", 0),
|
|
'EMPSELLERID',
|
|
|
|
'EMPSELLERID',
|
|
'true',
|
|
CASE "FREECHARGING" WHEN 'yes'
|
|
THEN 'true' WHEN 'no'
|
|
then 'false'
|
|
ELSE ''
|
|
END as FREECHARGE,
|
|
"PLUGTYPE",
|
|
0,
|
|
--'MAXSOCKETPOWER',
|
|
"LOCATION_ADDRESS",
|
|
'',
|
|
--LOCATION ZIP
|
|
"LOCATION_CITY",
|
|
'DE',
|
|
'EMPSELLERID',
|
|
|
|
|
|
'STRING',
|
|
left("STARTTIME", 19),
|
|
left("STOPTIME", 19),
|
|
left("STARTTIME", 19),
|
|
left("STOPTIME", 19),
|
|
IFNULL( "METERSTART", 0),
|
|
IFNULL( "METERSTOP", 0),
|
|
'TARIFF',
|
|
'CURRENCY',
|
|
'VAT',
|
|
|
|
--'BILLINGVALUEBRUTTO',
|
|
0,
|
|
--'BILLINGVALUEBRUTTO',
|
|
0,
|
|
--'BILLINGVALUENETTO',
|
|
0,
|
|
--'PARKINGTIMEPRICEBRUTTO',
|
|
0,
|
|
--'PARKINGTIMEPRICENETTO',
|
|
0,
|
|
--'ADITIONALPRICEBRUTTO',
|
|
0,
|
|
|
|
|
|
"CREATE_TS",
|
|
"UPDATE_TS",
|
|
"STATUS",
|
|
"BILLING",
|
|
'VIRTAPORTAL'
|
|
from "VIRTAPORTAL"."CPO_CHARGES"
|
|
--where "TRANSACTIONTYPE" = 'charge'
|
|
where "PLUGTYPE" IS NOT NULL; |