216 lines
3.6 KiB
SQL
216 lines
3.6 KiB
SQL
DROP VIEW "EMOBILITY"."V_CHARGEDATA_EMP";
|
|
CREATE VIEW "EMOBILITY"."V_CHARGEDATA_EMP" ( "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
|
|
"UUID",
|
|
"UUID",
|
|
"UUID",
|
|
"FULLEVSEID",
|
|
TO_NVARCHAR("CUSTOMERID"),
|
|
UPPER("RFID"),
|
|
UPPER("RFID"),
|
|
'RFID',
|
|
IFNULL("WATTHOUR",
|
|
0),
|
|
CPOOPERATORID,
|
|
'OPERATORNAME',
|
|
'true',
|
|
CASE "FREECHARGING" WHEN 'yes'
|
|
THEN 'true' WHEN 'no'
|
|
then 'false'
|
|
ELSE ''
|
|
END as FREECHARGE,
|
|
"PLUGTYPE",
|
|
'METERID',
|
|
0,
|
|
"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',
|
|
"KWHGROSSPRICE" / '1.16',
|
|
"MINUTEGROSSPRICE" / '1.16',
|
|
"SESSIONGROSSPRICE" / '1.16',
|
|
"DURATION" ,
|
|
"EICHRECHTTYPE",
|
|
"MILEAGE",
|
|
'',
|
|
--'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',
|
|
'V 1.6/25.09.2020'
|
|
from "VIRTAPORTAL"."EMP_TRANSACTIONS"
|
|
where "TRANSACTIONTYPE" = 'charge'
|
|
and "WATTHOUR" > 0
|
|
and "DURATION" > 2)
|
|
UNION ALL (SELECT
|
|
"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"
|
|
from "EMOBILITY"."V_CHARGEDATA_EMP_EOPERATE")) WITH READ ONLY |