TODO
show parking_log_id in Backend
Passwort ändern durch Admin



Vier Varianten sind standardmäßig im CAR-READER System vorhanden:

Kommunikation über eine SQL-Datenbank
Kommunikation über Dateien
Kommunikation über eine TCP/IP-Socketverbindung
Serielle Kommunikation über RS232
Es stehen folgende Funktionen zur Verfügung:
*Bereitstellen des gelesenen Kennzeichens
*Das CAR-READER Programm sendet das aktuell gelesene Kennzeichen an das Fremdsystem zur weiteren Verarbeitung.
*Überprüfung des Kennzeichens
*Das CAR-READER Programm sendet das aktuell gelesene Kennzeichen an das Fremdsystem zur Kontrolle und erwartet einen Rückgabewert für die Reaktion.
*Ein-Ausfahrt Buchung
*Das CAR-READER Programm sendet das aktuell gelesene Kennzeichen an das Fremdsystem zur Buchung, wenn das Fahrzeug ein- oder ausgefahren ist.
*Schrankenöffnung
*Festlegung, ob eine Schranke nach Überprüfung des Kennzeichens durch das Fremdsystem geöffnet werden soll.



Fragen:
1 Shop mehre locations? Oder pro location ein Login?
Ein Shop = Eine Location


DB usefull queries

delete "duplicated" entries

delete from logs where logs_id in (
select l2.logs_id
--select l1.logs_id, l2.logs_id, l1.entry, l2.entry,l1.licence_plate ,extract('epoch' from l2.entry) - extract('epoch' from l1.entry)
from logs l1, logs l2
where l1.licence_plate = l2.licence_plate
and l1.entry != l2.entry
and l1.lane_in_id = l2.lane_in_id
and extract('epoch' from l2.entry) - extract('epoch' from l1.entry) < 300
and extract('epoch' from l2.entry) - extract('epoch' from l1.entry) > 0
-- and l1.parking_lot_id = 65435 and l2.parking_lot_id = 65435
and l1.licence_plate != '?'
order by l1.logs_id, l2.logs_id
)


delete from event_log where event_log_id in (
select l2.event_log_id
from event_log l1, event_log l2
where l1.licence_plate = l2.licence_plate
and l1.stamp != l2.stamp
and l1.lane_id = l2.lane_id
and extract('epoch' from l2.stamp) - extract('epoch' from l1.stamp) < 100
and extract('epoch' from l2.stamp) - extract('epoch' from l1.stamp) > 0
-- and l1.parking_lot_id = 65434 and l2.parking_lot_id = 65434
and l1.licence_plate != '?'
order by l1.event_log_id, l2.event_log_id
)


DB

create table users
(
user_id int primary key,
street varchar,
zipcode varchar,
city varchar,
email varchar,
user_name varchar,
password varchar,
contact_person varchar
);

create table time_units
(
time_unit_id serial primary key,
name varchar,
m_seconds bigint
);

create table time_profile
(
time_profile_id serial Primary Key,
name varchar,
time_unit int references time_units(time_unit_id),
values varchar
);

create table bonus_log
(
bonus_log_id serial primary key,
license_plate varchar,
planned_start bigint,
planned_end bigint,
bonus bigint,
shop_id int references users(user_id)
);

create table status_code
(
status_code_id serial primary key,
name varchar,
description varchar
);

create table logs
(
logs_id serial primary key,
entry bigint,
exit bigint,
license_plate varchar,
shop_id int references users(user_id),
status int references status_code(status_code_id)
);

CREATE TABLE claim
(
claim_id serial primary key,
claim_name character varying,
claim_description character varying

);

CREATE TABLE role
(
role_id serial primary key,
role_description character varying,
role_name character varying
);


CREATE TABLE main_user_role
(
user_id integer references users,
role_id integer references role
);

CREATE TABLE public.role_claims
(
role_id int references role,
claim_id int references claim,
write boolean
);

DB_UPDATES

27.02.19
UPDATE claim
SET claim_name='CLIENT'
WHERE claim_id = 1;

UPDATE claim
WHERE claim_id = 2;

UPDATE claim
SET claim_name='TICKETING'
WHERE claim_id = 3;

alter table app_user add constraint app_user_pkey primary key (user_id);
alter table main_user_role add constraint main_user_role_pkey primary key (user_id, role_id);
alter table role_claims add constraint role_claims_pkey primary key (role_id, claim_id);
alter table shop_user add constraint user_id_pkey primary key (user_id);



CREATE SEQUENCE main_user_user_id_seq;

ALTER TABLE main_user ALTER COLUMN user_id SET DEFAULT nextval('main_user_user_id_seq');

ALTER SEQUENCE main_user_user_id_seq OWNED BY main_user.user_id;

SELECT setval('main_user_user_id_seq', 23456781);

alter table time_profile rename column time_unit to time_unit_old;
alter table time_profile add column time_unit bigint;


alter table bonus_log add column selected int;

alter table shop_user add column time_profile references time_profile;
alter table bonus_log add column customer_name varchar;


07.03.19
create table shop_profile
(
shop_id int references shop_user,
time_profile_id int references time_profile,
active boolean,
primary key(shop_id, time_profile_id)
);

alter table time_profile add column custom_time time;
alter table time_profile add column custom_date time;


11.03.19

create table reader_location
(
location_id serial primary key,
api_key varchar,
ip_address varchar
);


create table location_shop
(
shop_id int references shop_user,
location_id int references reader_location ,
primary key (shop_id, location_id)
);

create table active_cars
(
active_car_id serial primary key,
start bigint NOT NULL,
location_id int references reader_location,
actualization bigint,
licence_plate varchar
);

14.03.2019

alter table reader_location rename to parking_lot;
alter table location_shop rename to parking_lot_shop;

alter table parking_lot_shop rename column location_id to parking_lot_id;
alter table parking_lot rename column location_id to parking_lot_id;

alter table parking_lot add column name varchar;
alter table parking_lot add column street character varying;
alter table parking_lot add column zipcode character varying;
alter table parking_lot add column city character varying;


alter table time_profile drop column custom_time;
alter table time_profile drop column custom_date;

alter table time_profile add column custom_time boolean;
alter table time_profile add column custom_date boolean;

alter table bonus_log add column time_profile_id int references time_profile;

alter table active_cars rename column location_id to parking_lot_id;
alter table shop_user add column parking_lot_id int references parking_lot;

alter table bonus_log add column send bigint;
alter table bonus_log add column is_sent boolean;

15.03.2019
alter table bonus_log add column sent bigint;

19.03.2019

alter table logs rename column license_plate to licence_plate;

alter table logs add constraint uq_logs UNIQUE (entry, licence_plate);

alter table parking_lot rename column name to parking_lot_name;
alter table parking_lot rename column street to parking_lot_street;
alter table parking_lot rename column zipcode to parking_lot_zipcode;
alter table parking_lot rename column city to parking_lot_city;

21.03.2019

alter table main_user add column last_login bigint;
alter table bonus_log alter column is_sent set default false;


27.03.19

alter table shop_user add column iban varchar;
alter table shop_user add column bic varchar;

28.03.19

alter table shop_user add column bank_name varchar;
alter table shop_user add column bank_iid varchar;
alter table shop_user add column bank_zipcode varchar;
alter table shop_user add column bank_city varchar;
alter table shop_user add column bank_branch varchar;


01.07.19
alter table logs add column zone_id int ;
alter table logs add column camera_id int ;
alter table logs add column lane_id int ;
alter table logs add column filename_in_lpr varchar ;
alter table logs add column filename_out_lpr varchar ;
alter table logs add column filename_in_cont varchar ;
alter table logs add column filename_out_cont varchar ;

-- 03.07.19

create table event_log
(
event_log_id serial PRIMARY KEY,
stamp timestamp without time zone,
licence_plate character varying,
parking_lot_id integer,
zone_id integer,
lane_id integer,
event_type varchar
)

alter table logs add column camera_in_id int;
alter table logs add column camera_out_id int;
alter table logs add column received timestamp without time zone;

alter table event_log add column scan_ok boolean;
alter table event_log add column ingoing boolean;
alter table logs add column received timestamp without time zone;

alter table parking_lot add column timezone varchar;

alter table event_log add column filename_lpr varchar;
alter table event_log add column filename_cont varchar;


-- 04.07.19
*
alter table bonus_log add column state_changed TIMESTAMP WITHOUT time zone;
alter table bonus_log add column state int;

-- 09.07.19

alter table logs add column lane_in_id int;
alter table logs add column lane_out_id int;
alter table logs drop column lane_id;

-- 17.07.19

alter table bonus_log add column booked TIMEstamp without time zone;
alter table bonus_log add column booked_complete boolean;
alter table bonus_log add column booked_until Timestamp without time zone;

create table tariff
(
tariff_id serial primary key,
times varchar,
night_from int,
night_to int,
night_val numeric
);


create table csv_import
(
csv_import_id serial Primary key,
imported TIMEstamp without time zone,
shop_id int references shop_user,
parking_lot_id int references parking_lot,
starts TIMEstamp without time zone,
ends TIMEstamp without time zone,
entries int
);

alter table shop_user add column tariff_id int references tariff;

-- 18.07.19

alter table bonus_log drop column planned_start;
alter table bonus_log drop column planned_end;

alter table bonus_log add column planned_start timestamp without time zone;
alter table bonus_log add column planned_end timestamp without time zone;

alter table bonus_log add column timezone varchar;

alter table tariff drop column times;

alter table tariff add column values_norm varchar;
alter table tariff add column values_sunday varchar;
alter table tariff add column values_holiday varchar;


CREATE TABLE holiday
(
holiday_id serial PRIMARY KEY,
name character varying,
datum date NOT NULL
);

CREATE TABLE holiday_parking_lot
(
holiday_parking_lot_id serial NOT NULL PRIMARY KEY,
holiday integer references holiday,
parking_lot_id integer references parking_lot
);

-- 19.07.19

create table filelog
(
filelog_id serial PRIMARY KEY,
filename varchar,
shop_id int references shop_user,
file_type int ,
created TIMESTAMP WITHOUT TIME ZONE
);


alter table event_log add column camera_id int;
alter table event_log add colum received timestamp without time zone;
alter table logs add column state int;


-- 22.07.19

alter table bonus_log add column booked_hours int;
alter table filelog add column start_date Timestamp without time zone;
alter table filelog add column end_date Timestamp without time zone;

alter table filelog add column created_by int references main_user;
alter table filelog add column created_on timestamp without time zone;
alter table filelog drop column created;

-- 23.07.19

alter table tariff add column tariff_name varchar;
alter table csv_import drop column imported;
alter table csv_import add column created_on timestamp without time zone;
alter table csv_import add column created_by int references main_user;

INSERT into time_profile (name, options, custom_time, custom_date, active)
VALUES ('EXCELIMPORT','[]',true,true, false);

-- 24.07.19

alter table csv_import add column col_name int;
alter table csv_import add column col_licence_plate int;
alter table csv_import add column filename varchar;
alter table csv_import add column skip_header boolean DEFAULT false;

-- 25.07.19

create table log_sync
(
log_sync_id serial PRIMARY KEY,
parking_lot_id int references parking_lot,
stamp TIMESTAMP WITHOUT TIME ZONE,
data_json varchar
);

update parking_lot SET timezone = 'Europe/Berlin'

-- 26.07.19



create table lane
(
lane_id serial primary key,
car_reader_lane_id int,
in_zone int references zone,
out_zone int references zone,
active boolean default true,
parking_lot_id int references parking_lot
);

create table zone
(
zone_id serial PRIMARY KEY,
car_reader_zone_id int,
zone_name varchar,
parking_lot_id int references parking_lot
);

alter table logs add column zone_in_id int;
alter table logs add column zone_out_id int;

-- 11.02.20

alter table bonus_log alter column booked_complete set default false;