--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;