· 6 years ago · Jun 07, 2019, 07:02 AM
1Moodle
2◄ Poprzednia aktywnośćNastępna aktywność ►
3Jesteś tutaj
4PE :: PWSZ Tarnów / ► POC / ► Zadania / ► Segmentacja
5
6Widoczne grupy
7piątek, 7 czerwiec 2019, 07:52
8Wklej zawartość skryptu z programu Matlab.
9
10CREATE SCHEMA bgz;
11CREATE TABLE bgz.klienci(
12id_klienta BIGINT NOT NULL,
13nazwisko CHARACTER VARYING(50) NOT NULL,
14imie CHARACTER VARYING(25) NOT NULL,
15srednie_koszty MONEY);
16
17CREATE TABLE bgz.rachunki_bankowe(
18nrb CHARACTER (25) NOT NULL,
19id_klienta BIGINT NOT NULL,
20rodzaj_rachunku CHARACTER VARYING(30) NOT NULL
21CONSTRAINT nrb1 CHECK(nrb NOT LIKE '%[^0-9]%')
22);
23
24
25CREATE TABLE bgz.transakcje(
26id_transakcji BIGINT NOT NULL,
27nrb CHARACTER (25) NOT NULL,
28data_czas_transakcji Timestamp with time zone NOT NULL,
29kwota MONEY NOT NULL,
30rodzaj_operacji CHARACTER VARYING(50) NOT NULL
31CONSTRAINT rodzaj CHECK(rodzaj_operacji LIKE 'wpłata'
32OR rodzaj_operacji LIKE 'wypłata'
33OR rodzaj_operacji LIKE 'przelew przychodzący'
34OR rodzaj_operacji LIKE 'przelew wychodzący'
35OR rodzaj_operacji LIKE 'prowizja')
36);
37
38ALTER TABLE bgz.klienci
39ADD CONSTRAINT klienci_pk PRIMARY KEY (id_klienta);
40
41ALTER TABLE bgz.rachunki_bankowe
42ADD CONSTRAINT rachunki_b FOREIGN KEY (id_klienta) REFERENCES bgz.klienci(id_klienta);
43
44ALTER TABLE bgz.rachunki_bankowe
45ADD CONSTRAINT pk_rachunki PRIMARY KEY(nrb);
46
47ALTER TABLE bgz.transakcje
48ADD CONSTRAINT transakcje_fk FOREIGN KEY (nrb) REFERENCES bgz.rachunki_bankowe(nrb);
49
50
51ALTER TABLE bgz.transakcje
52ADD CONSTRAINT pk_transakcje PRIMARY KEY(id_transakcji);
53
54
55INSERT INTO bgz.klienci (id_klienta,nazwisko,imie, srednie_koszty)
56VALUES (1,'Poziomka','Anna',NULL),
57(2,'Malina','Rozalina',-3.33),
58(3,'Truskawka','Wojciech',NULL),
59(4,'Wiśnia','Sylwia',NULL);
60
61
62INSERT INTO bgz.rachunki_bankowe (id_klienta,nrb,rodzaj_rachunku)
63VALUES (1,'33333333888888880','rozliczeniowo-oszczęd'),
64(2,'99999999222222222111','oszczędnościowy'),
65(3,'111111111222222222333','rozliczeniowo-oszczęd'),
66(4,'44444555555559','rozliczeniowo-oszczęd');
67
68
69INSERT INTO bgz.transakcje(id_transakcji,nrb,data_czas_transakcji,kwota,rodzaj_operacji)
70VALUES(1,'99999999222222222111','2019-05-01 14:13',6000.00,'wpłata'),
71(2,'111111111222222222333','2019-05-05 14:13' ,5500.00,'przelew przychodzący'),
72(3,'99999999222222222111','2019-05-06 14:13',-1000.00,'wypłata'),
73(4,'111111111222222222333','2019-05-08 14:13',-10.00,'prowizja');
74
75CREATE OR REPLACE FUNCTION bgz.obliczProwizje(id BIGINT, data_poczatkowa DATE, data_koncowa DATE) RETURNS NUMERIC(4,2 ) AS $OBLICZPROWIZJE$
76DECLARE
77prowizja NUMERIC(4,2);
78BEGIN
79IF(data_poczatkowa > data_koncowa) THEN
80RAISE NOTICE 'Zła data';
81RETURN NULL;
82ELSEIF (id NOT IN (SELECT id_klienta FROM bgz.klienci)) OR (id NOT IN (SELECT id_klienta FROM bgz.rachunki_bankowe)) THEN
83RAISE NOTICE 'Klient nie istnieje';
84RETURN NULL;
85ELSEIF (id NOT IN (SELECT id_klienta FROM bgz.rachunki_bankowe INNER JOIN bgz.transakcje USING(nrb) WHERE data_czas_transakcji BETWEEN data_poczatkowa AND data_koncowa)) THEN
86prowizja := 0.00;
87RETURN prowizja;
88ELSE
89SELECT SUM(kwota) INTO prowizja
90FROM bgz.rachunki_bankowe rb INNER JOIN bgz.transakcje tr USING(nrb)
91WHERE rb.id_klienta = id AND rodzaj_operacji ILIKE 'prowizja' AND data_czas_transakcji BETWEEN data_poczatkowa AND data_koncowa;
92RETURN prowizja;
93END IF;
94END;
95$OBLICZPROWIZJE$ LANGUAGE 'plpgsql';
96--
97-- SELECT bgz.obliczProwizje(3,'2019-05-01','2019-05-31');
98
99
100CREATE OR REPLACE FUNCTION bgz.wyzwalacz()
101RETURNS TRIGGER AS $$
102
103DECLARE wynik integer;
104DECLARE dataBiezaca date;
105DECLARE dataDo date;
106DECLARE numerNrb character(25);
107DECLARE idKlienta bigint;
108
109BEGIN
110 numerNrb := NEW.nrb;
111 IF (numerNrb IS NULL)
112 THEN
113 numerNrb := OLD.nrb;
114 END IF;
115 idKlienta := (SELECT r.id_klienta FROM bgz.rachunki_bankowe r WHERE r.nrb = numerNrb);
116 dataBiezaca := current_date;
117 dataDo := dataBiezaca - interval '3 month';
118 wynik := bgz.obliczprowizje(1, dataDo, dataBiezaca);
119
120 IF(wynik <= 0.00 OR wynik IS NULL)
121 THEN
122 UPDATE bgz.klienci
123 SET srednie_koszty = wynik
124 WHERE id_klienta = idKlienta;
125 RETURN NEW;
126 ELSE
127 RAISE NOTICE 'Nieprawidłowa wartość średniej prowizji';
128 RETURN NULL;
129 END IF;
130
131END;
132$$LANGUAGE 'plpgsql';
133
134DROP TRIGGER IF EXISTS transakcje_trigger ON bgz.transakcje;
135CREATE TRIGGER transakcje_trigger BEFORE INSERT OR UPDATE OR DELETE
136ON bgz.transakcje
137FOR EACH ROW EXECUTE PROCEDURE bgz.wyzwalacz();
138
139INSERT INTO bgz.transakcje(id_transakcji,nrb,data_czas_transakcji,kwota,rodzaj_operacji)
140VALUES (5,'44444555555559','2019-05-08 14:13',-10.00,'prowizja');
141Jesteś zalogowany(a) jako Mateusz Mężyk (Wyloguj)
142POC
1430.111259 secs
144RAM: 20.9MB
145RAM peak: 21MB
146Included 84 files
147ticks: 11 user: 9 sys: 1 cuser: 0 csys: 0
148Load average: 0.28
149Record cache hit/miss ratio : 1/3
150Validate HTML Section 508 Check WCAG 1 (2,3) Check