NXLZ8eMd

1DO LANGUAGE 'plpgsql' $$
2BEGIN
3  ----------Удаление текущей базы данных--------------------
4  DROP TABLE IF EXISTS FM_OPERATION;
5  DROP TABLE IF EXISTS FM_REGULARINCOME;
6  DROP TABLE IF EXISTS FM_CATEGORY;
7  DROP TABLE IF EXISTS FM_USER;
8  DROP TABLE IF EXISTS FM_ROLE;
9  DROP TABLE IF EXISTS FM_PRIORITY;
10
11  -----------Создание таблицы приоритетов----------------
12
13  CREATE TABLE FM_PRIORITY (
14    "priority_id" SERIAL NOT NULL,
15    "name"        VARCHAR(30),
16    CONSTRAINT PRIORITIES_pk PRIMARY KEY ("priority_id")
17  );
18
19  ---------Заполнение базы приоритетами----------------------------
20  INSERT INTO FM_PRIORITY VALUES (1, 'Бесполезно');
21  INSERT INTO FM_PRIORITY VALUES (2, 'Нет необходимости');
22  INSERT INTO FM_PRIORITY VALUES (3, 'Второстепенно');
23  INSERT INTO FM_PRIORITY VALUES (4, 'Не так важно');
24  INSERT INTO FM_PRIORITY VALUES (5, 'Нейтрально');
25  INSERT INTO FM_PRIORITY VALUES (6, 'Желательно');
26  INSERT INTO FM_PRIORITY VALUES (7, 'Необходимо');
27  INSERT INTO FM_PRIORITY VALUES (8, 'Очень необходимо');
28  INSERT INTO FM_PRIORITY VALUES (9, 'Крайне важно');
29  INSERT INTO FM_PRIORITY VALUES (10, 'Жизненно необходимо');
30
31  -----------Создание таблицы ролей----------------
32
33  CREATE TABLE FM_ROLE (
34    "role_id" SERIAL NOT NULL,
35    "name"    VARCHAR(30),
36    CONSTRAINT FM_ROLE_pk PRIMARY KEY ("role_id")
37  );
38
39  ---------Заполнение базы ролями----------------------------
40  INSERT INTO FM_ROLE VALUES (1, 'administrator');
41  INSERT INTO FM_ROLE VALUES (2, 'moderator');
42  INSERT INTO FM_ROLE VALUES (3, 'user');
43
44  --------- Создание таблицы категорий---------------
45
46  CREATE TABLE FM_CATEGORY (
47    "category_id" SERIAL NOT NULL,
48    "parent_id"   INT,
49    "priority_id" INT,
50    "name"        VARCHAR(50),
51    CONSTRAINT FM_CATEGORY_pk PRIMARY KEY ("category_id")
52  );
53
54  ALTER TABLE FM_CATEGORY ADD CONSTRAINT "FM_CATEGORY_fk0" FOREIGN KEY (parent_id) REFERENCES FM_CATEGORY (category_id);
55  ALTER TABLE FM_CATEGORY ADD CONSTRAINT "FM_CATEGORY_fk1" FOREIGN KEY (priority_id) REFERENCES FM_PRIORITY (priority_id);
56
57  INSERT INTO FM_CATEGORY VALUES (1, NULL, 10, 'Еда');
58  INSERT INTO FM_CATEGORY VALUES (2, NULL, 7, 'Отдых');
59  INSERT INTO FM_CATEGORY VALUES (3, NULL, 9, 'Транспорт');
60  INSERT INTO FM_CATEGORY VALUES (4, NULL, 6, 'Спорт');
61
62  ----------- Создание таблицы пользователей---------------
63
64  CREATE TABLE FM_USER (
65    "user_id"           SERIAL             NOT NULL,
66    "name"              VARCHAR(30),
67    "surname"           VARCHAR(30),
68    "patronymic"        VARCHAR(30),
69    "email"             VARCHAR(50),
70    "registration_date" TIMESTAMP WITH TIME ZONE,
71    "login"             VARCHAR(30) UNIQUE NOT NULL,
72    "password"          VARCHAR(50),
73    "is_active"         BOOLEAN DEFAULT 'true',
74    "role_id"           INTEGER,
75    CONSTRAINT FM_USER_pk PRIMARY KEY ("user_id")
76  );
77
78  ALTER TABLE FM_USER ADD CONSTRAINT "FM_USER_fk0" FOREIGN KEY (role_id) REFERENCES FM_ROLE (role_id);
79
80  INSERT INTO FM_USER (user_id, name, surname, patronymic, email, registration_date, login, password, is_active, role_id)
81  VALUES (1, 'Root', 'Toor', NULL, 'admin@mail.ru', now(), 'admin', 'password', 'true', 1);
82  INSERT INTO FM_USER (user_id, name, surname, patronymic, email, registration_date, login, password, is_active, role_id)
83  VALUES (2, 'User', 'Test', NULL, 'test@mail.ru', now(), 'test', 'test', 'true', 3);
84
85  ----------- Создание таблицы операций---------------
86
87  CREATE TABLE FM_OPERATION (
88    "operation_id"        SERIAL          NOT NULL,
89    "value"               NUMERIC(50, 10) NOT NULL,
90    "date_of_transaction" TIMESTAMP WITH TIME ZONE,
91    "name"                VARCHAR(100),
92    "category_id"         INT,
93    "type"                INT             NOT NULL,
94    "user_id"             INT             NOT NULL,
95    CONSTRAINT FM_OPERATION_pk PRIMARY KEY ("operation_id")
96  );
97
98  ALTER TABLE FM_OPERATION ADD CONSTRAINT "FM_OPERATION_fk0" FOREIGN KEY (category_id) REFERENCES FM_CATEGORY (category_id);
99  ALTER TABLE FM_OPERATION ADD CONSTRAINT "FM_OPERATION_fk1" FOREIGN KEY (user_id) REFERENCES FM_USER (user_id);
100
101  INSERT INTO FM_OPERATION (value, date_of_transaction, name, category_id, type, user_id)
102  VALUES (30000, now(), 'Зарплата', NULL, 1, 2);
103  INSERT INTO FM_OPERATION (value, date_of_transaction, name, category_id, type, user_id)
104  VALUES (121.23, now() - INTERVAL '1 day', 'Chips', 1, 2, 2);
105  INSERT INTO FM_OPERATION (value, date_of_transaction, name, category_id, type, user_id)
106  VALUES (1000000, now() - INTERVAL '7 days', 'Jackpot', NULL, 1, 2);
107  INSERT INTO FM_OPERATION (value, date_of_transaction, name, category_id, type, user_id)
108  VALUES (11, now() - INTERVAL '1 month', 'old operation', NULL, 1, 2);
109  INSERT INTO FM_OPERATION (value, date_of_transaction, name, category_id, type, user_id)
110  VALUES (1000, now() - INTERVAL '13 days', 'Проездной', 2, 2, 2);
111
112  --------- Создание таблицы регулярных доходов---------------
113
114  CREATE TABLE FM_REGULARINCOME (
115    "id"               SERIAL                   NOT NULL,
116    "frequency"        INTERVAL                 NOT NULL,
117    "last_income_date" TIMESTAMP WITH TIME ZONE NOT NULL,
118    "value"            NUMERIC(50, 10)          NOT NULL,
119    "name"             VARCHAR(50),
120    "user_id"          INT                      NOT NULL,
121    CONSTRAINT FM_REGULARINCOME_pk PRIMARY KEY ("id")
122  );
123
124  ALTER TABLE FM_REGULARINCOME ADD CONSTRAINT "FM_REGULARINCOME_fk0" FOREIGN KEY (user_id) REFERENCES FM_USER (user_id);
125
126  -- -----------------------Создание API для регистрации юзера--------------------------------
127
128  DROP FUNCTION IF EXISTS AddUser( VARCHAR, VARCHAR, VARCHAR, VARCHAR, VARCHAR, VARCHAR, INTEGER );
129
130  CREATE OR REPLACE FUNCTION AddUser(userName     VARCHAR, userSurname VARCHAR, userPatronymic VARCHAR,
131                                     userEmail    VARCHAR, userLogin VARCHAR,
132                                     userPassword VARCHAR, userRoleId INTEGER)
133    RETURNS VOID AS
134    'BEGIN
135      INSERT INTO FM_USER (name, surname, patronymic, email, registration_date, login, password, role_id)
136      VALUES (userName, userSurname, userPatronymic, userEmail, now(), userLogin, userPassword, userRoleid);
137    END;'
138  LANGUAGE 'plpgsql';
139
140  -----------------Создание API для получения данных о юзере по его логину--------------------------------
141
142  DROP FUNCTION IF EXISTS getUserByLogin( VARCHAR );
143
144  CREATE OR REPLACE FUNCTION getUserByLogin(userLogin VARCHAR)
145    RETURNS TABLE(
146    id INTEGER,
147    name VARCHAR(30),
148    surname VARCHAR(30),
149    patronymic VARCHAR(30),
150    email VARCHAR(50),
151    registration_date TIMESTAMP WITH TIME ZONE,
152    login VARCHAR(30),
153    password VARCHAR(50),
154    isActive BOOLEAN,
155    role VARCHAR(30)
156    )
157  AS '
158  BEGIN
159  RETURN QUERY SELECT
160                 u.user_id,
161                 u.name,
162                 u.surname,
163                 u.patronymic,
164                 u.email,
165                 u.registration_date,
166                 u.login,
167                 u.password,
168                 u.is_active,
169                 r.name as role
170               FROM fm_user u
171                 JOIN fm_role r on u.role_id=r.role_id
172               where u.login = userLogin;
173  END;
174  '
175  LANGUAGE 'plpgsql';
176
177  -----------------------Создание API для получения списка операций юзера за промежуток времени--------------------------------
178
179  DROP FUNCTION IF EXISTS getOperationForSpecifiedPeriod( VARCHAR, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH TIME ZONE );
180
181  CREATE OR REPLACE FUNCTION getOperationForSpecifiedPeriod(
182    user_login VARCHAR,
183    start_date TIMESTAMP WITH TIME ZONE,
184    end_date   TIMESTAMP WITH TIME ZONE
185  )
186    RETURNS TABLE(
187    value NUMERIC(50, 10),
188    date_transaction TIMESTAMP WITH TIME ZONE,
189    comment VARCHAR(100),
190    category VARCHAR(50),
191    type INTEGER
192    )
193  AS
194    '
195    BEGIN
196    RETURN QUERY SELECT
197                   op.value,
198                   op.date_of_transaction,
199                   op.name,
200                   cat.name,
201                   op.type
202                 FROM FM_OPERATION op
203                   LEFT JOIN FM_CATEGORY cat
204                     ON op.category_id = cat.category_id
205                 WHERE op.date_of_transaction >= start_date AND op.date_of_transaction <= end_date
206                       AND op.user_id = (SELECT user_id
207                                         FROM FM_USER
208                                         WHERE login = user_login);
209    END;
210    '
211  LANGUAGE 'plpgsql';
212
213  ------------------------Создание API для получения списка всех категорий--------------------------------
214
215  DROP FUNCTION IF EXISTS getAllCategories();
216
217  CREATE OR REPLACE FUNCTION getAllCategories()
218    RETURNS TABLE(categories_id INTEGER, categories_name CHARACTER VARYING, priority VARCHAR) AS
219    '
220    BEGIN
221    RETURN QUERY SELECT
222                   c.category_id,
223                   c.name,
224                   p.name
225                 FROM FM_CATEGORY c
226                   LEFT JOIN FM_PRIORITY p
227                     ON c.priority_id = p.priority_id;
228    END;
229    '
230  LANGUAGE 'plpgsql';
231
232  -----------------------Создание API для добавления новой финансовой операции--------------------------------
233
234  DROP FUNCTION IF EXISTS AddOperation( NUMERIC, TIMESTAMPTZ, VARCHAR, VARCHAR, INTEGER, INTEGER );
235
236  CREATE OR REPLACE FUNCTION AddOperation(amount       NUMERIC, dateOfTransaction TIMESTAMPTZ, operationName VARCHAR,
237                                          categoryName VARCHAR, operationType INTEGER,
238                                          userId       INTEGER)
239    RETURNS VOID AS
240    'BEGIN
241      INSERT INTO FM_OPERATION (value, date_of_transaction, name, category_id, type, user_id)
242      SELECT amount, dateOfTransaction, operationName, fm_category.category_id, operationType, userId
243      FROM fm_category
244      WHERE categoryName=fm_category.name;
245    END;'
246  LANGUAGE 'plpgsql';
247
248  -----------------------Создание API для создания нового регулярного дохода--------------------------------
249
250  DROP FUNCTION IF EXISTS AddRegularIncome( INTERVAL, TIMESTAMPTZ, NUMERIC, VARCHAR, INT4 );
251
252  CREATE OR REPLACE FUNCTION AddRegularIncome(incomePeriod      INTERVAL, lastIncomeDate TIMESTAMPTZ, amount NUMERIC,
253                                              regularIncomeName VARCHAR, userId INT4)
254    RETURNS VOID AS
255    'BEGIN
256      INSERT INTO fm_regularincome (id, frequency, last_income_date, value, name, user_id)
257      VALUES (incomePeriod, lastIncomeDate, amount, regularIncomeName, userId);
258    END;'
259  LANGUAGE 'plpgsql';
260END
261$$;