· 7 years ago · Feb 17, 2019, 12:20 PM
1USE [master]
2GO
3/****** Object: Database [finanzuebersicht] Script Date: 17.02.2019 13:15:44 ******/
4CREATE DATABASE [finanzuebersicht]
5 CONTAINMENT = NONE
6 ON PRIMARY
7( NAME = N'finanzuebersicht', FILENAME = N'C:\Users\Lukas\finanzuebersicht.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
8 LOG ON
9( NAME = N'finanzuebersicht_log', FILENAME = N'C:\Users\Lukas\finanzuebersicht_log.ldf' , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
10GO
11ALTER DATABASE [finanzuebersicht] SET COMPATIBILITY_LEVEL = 130
12GO
13IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
14begin
15EXEC [finanzuebersicht].[dbo].[sp_fulltext_database] @action = 'enable'
16end
17GO
18ALTER DATABASE [finanzuebersicht] SET ANSI_NULL_DEFAULT OFF
19GO
20ALTER DATABASE [finanzuebersicht] SET ANSI_NULLS OFF
21GO
22ALTER DATABASE [finanzuebersicht] SET ANSI_PADDING OFF
23GO
24ALTER DATABASE [finanzuebersicht] SET ANSI_WARNINGS OFF
25GO
26ALTER DATABASE [finanzuebersicht] SET ARITHABORT OFF
27GO
28ALTER DATABASE [finanzuebersicht] SET AUTO_CLOSE ON
29GO
30ALTER DATABASE [finanzuebersicht] SET AUTO_SHRINK OFF
31GO
32ALTER DATABASE [finanzuebersicht] SET AUTO_UPDATE_STATISTICS ON
33GO
34ALTER DATABASE [finanzuebersicht] SET CURSOR_CLOSE_ON_COMMIT OFF
35GO
36ALTER DATABASE [finanzuebersicht] SET CURSOR_DEFAULT GLOBAL
37GO
38ALTER DATABASE [finanzuebersicht] SET CONCAT_NULL_YIELDS_NULL OFF
39GO
40ALTER DATABASE [finanzuebersicht] SET NUMERIC_ROUNDABORT OFF
41GO
42ALTER DATABASE [finanzuebersicht] SET QUOTED_IDENTIFIER OFF
43GO
44ALTER DATABASE [finanzuebersicht] SET RECURSIVE_TRIGGERS OFF
45GO
46ALTER DATABASE [finanzuebersicht] SET ENABLE_BROKER
47GO
48ALTER DATABASE [finanzuebersicht] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
49GO
50ALTER DATABASE [finanzuebersicht] SET DATE_CORRELATION_OPTIMIZATION OFF
51GO
52ALTER DATABASE [finanzuebersicht] SET TRUSTWORTHY OFF
53GO
54ALTER DATABASE [finanzuebersicht] SET ALLOW_SNAPSHOT_ISOLATION OFF
55GO
56ALTER DATABASE [finanzuebersicht] SET PARAMETERIZATION SIMPLE
57GO
58ALTER DATABASE [finanzuebersicht] SET READ_COMMITTED_SNAPSHOT OFF
59GO
60ALTER DATABASE [finanzuebersicht] SET HONOR_BROKER_PRIORITY OFF
61GO
62ALTER DATABASE [finanzuebersicht] SET RECOVERY SIMPLE
63GO
64ALTER DATABASE [finanzuebersicht] SET MULTI_USER
65GO
66ALTER DATABASE [finanzuebersicht] SET PAGE_VERIFY CHECKSUM
67GO
68ALTER DATABASE [finanzuebersicht] SET DB_CHAINING OFF
69GO
70ALTER DATABASE [finanzuebersicht] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF )
71GO
72ALTER DATABASE [finanzuebersicht] SET TARGET_RECOVERY_TIME = 60 SECONDS
73GO
74ALTER DATABASE [finanzuebersicht] SET DELAYED_DURABILITY = DISABLED
75GO
76ALTER DATABASE [finanzuebersicht] SET QUERY_STORE = OFF
77GO
78USE [finanzuebersicht]
79GO
80ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = OFF;
81GO
82ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET LEGACY_CARDINALITY_ESTIMATION = PRIMARY;
83GO
84ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 0;
85GO
86ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP = PRIMARY;
87GO
88ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = ON;
89GO
90ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET PARAMETER_SNIFFING = PRIMARY;
91GO
92ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = OFF;
93GO
94ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET QUERY_OPTIMIZER_HOTFIXES = PRIMARY;
95GO
96USE [finanzuebersicht]
97GO
98/****** Object: UserDefinedFunction [dbo].[GetIDByNootPath] Script Date: 17.02.2019 13:15:44 ******/
99SET ANSI_NULLS ON
100GO
101SET QUOTED_IDENTIFIER ON
102GO
103create function [dbo].[GetIDByNootPath](@NootPath hierarchyid)
104returns int
105as
106begin
107return (select ID from categories where NootPath = @NootPath)
108end
109GO
110/****** Object: UserDefinedFunction [dbo].[GetNewChildPath] Script Date: 17.02.2019 13:15:44 ******/
111SET ANSI_NULLS ON
112GO
113SET QUOTED_IDENTIFIER ON
114GO
115CREATE function [dbo].[GetNewChildPath](@parentID int null,@UserName nvarchar(50))
116returns hierarchyid
117begin
118declare @pNootPath hierarchyid = (select Nootpath from Categories where ID = @parentID);
119return
120 (select
121 CASE WHEN @parentID IS NULL
122 THEN
123 (select @pNootPath.GetDescendant(hierarchyid::GetRoot(),null))
124 ELSE
125 (select @pNootPath.GetDescendant(dbo.maxpath(@pNootpath),null))
126 END)
127end
128GO
129/****** Object: UserDefinedFunction [dbo].[GetNootPath] Script Date: 17.02.2019 13:15:44 ******/
130SET ANSI_NULLS ON
131GO
132SET QUOTED_IDENTIFIER ON
133GO
134-- =============================================
135-- Author: <Author,,Name>
136-- Create date: <Create Date, ,>
137-- Description: <Description, ,>
138-- =============================================
139CREATE FUNCTION [dbo].[GetNootPath](@id int)
140RETURNS hierarchyid
141AS
142BEGIN
143return (select NootPath from Categories where ID=@id)
144END
145GO
146/****** Object: UserDefinedFunction [dbo].[GetSaldo] Script Date: 17.02.2019 13:15:44 ******/
147SET ANSI_NULLS ON
148GO
149SET QUOTED_IDENTIFIER ON
150GO
151
152CREATE FUNCTION [dbo].[GetSaldo] (
153 @username NVARCHAR(50),
154 @olderDate DATE,
155 @newerDate DATE,
156 @incrementFactor INT)
157RETURNS DECIMAL(18,2)
158AS
159BEGIN
160 DECLARE @res DECIMAL(18,2) = 0
161 ,@ausgangsSaldo DECIMAL(18,2) = 0
162
163 -- Bestimme Ausgangssaldo
164 SELECT @ausgangsSaldo = (
165 SELECT AusgangssaldoBalance
166 FROM Users
167 )
168
169 -- Summe aller Beträge im zeitraum
170 SELECT @res = sum(ae.Betrag)
171 FROM (
172 SELECT *
173 FROM AccountingEntries
174 WHERE @olderDate > AccountingEntries.Buchungsdatum
175 AND @newerDate <= AccountingEntries.Buchungsdatum
176 ) ae
177
178 -- Wende Vorwärts / Rückwärtsrechnung an und ergänze den Ausgangssaldo
179 SELECT @res = @res * @incrementFactor + @ausgangsSaldo
180
181 -- DELIVAAAAAAA
182 RETURN @res
183--habjanurnestundeodersogebrauchtfürdiefunction.fml
184END
185GO
186/****** Object: UserDefinedFunction [dbo].[maxpath] Script Date: 17.02.2019 13:15:44 ******/
187SET ANSI_NULLS ON
188GO
189SET QUOTED_IDENTIFIER ON
190GO
191CREATE function [dbo].[maxpath](@pNootPath hierarchyid)
192returns hierarchyid
193as
194begin
195return (select max(Nootpath) from categories where Nootpath.GetAncestor(1) = @pNootPath)
196end
197GO
198/****** Object: Table [dbo].[AccountingEntries] Script Date: 17.02.2019 13:15:44 ******/
199SET ANSI_NULLS ON
200GO
201SET QUOTED_IDENTIFIER ON
202GO
203CREATE TABLE [dbo].[AccountingEntries](
204 [ID] [int] IDENTITY(1,1) NOT NULL,
205 [Username] [nvarchar](50) NOT NULL,
206 [CategoryID] [int] NULL,
207 [Auftragskonto] [nvarchar](50) NULL,
208 [Buchungsdatum] [datetime] NULL,
209 [Valutadatum] [datetime] NULL,
210 [Buchungstext] [nvarchar](50) NULL,
211 [Verwendungszweck] [nvarchar](200) NULL,
212 [Glaeubiger_ID] [nvarchar](50) NULL,
213 [Mandatsreferenz] [nvarchar](50) NULL,
214 [Kundenreferenz] [nvarchar](50) NULL,
215 [Sammlerreferenz] [nvarchar](50) NULL,
216 [Lastschrift_Ursprungsbetrag] [decimal](18, 2) NULL,
217 [Auslagenersatz_Ruecklastschrift] [nvarchar](50) NULL,
218 [Beguenstigter] [nvarchar](50) NULL,
219 [IBAN] [nvarchar](50) NULL,
220 [BIC] [nvarchar](50) NULL,
221 [Betrag] [decimal](18, 2) NOT NULL,
222 [Info] [nvarchar](200) NULL,
223 [Hash] [varchar](64) NOT NULL,
224 [CategoryAssignedManually] [bit] NULL,
225 [RefundedID] [int] NULL,
226 CONSTRAINT [PK_AccountingEntries] PRIMARY KEY CLUSTERED
227(
228 [ID] ASC
229)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
230) ON [PRIMARY]
231GO
232/****** Object: UserDefinedFunction [dbo].[GetUserEntries] Script Date: 17.02.2019 13:15:45 ******/
233SET ANSI_NULLS ON
234GO
235SET QUOTED_IDENTIFIER ON
236GO
237
238CREATE FUNCTION [dbo].[GetUserEntries]
239(@username [NVARCHAR] (50))
240returns TABLE
241AS
242RETURN
243SELECT [ID],
244 [Username],
245 [CategoryID],
246 [Auftragskonto],
247 [Buchungsdatum],
248 [Valutadatum],
249 [Buchungstext],
250 [Verwendungszweck],
251 [Glaeubiger_ID],
252 [Mandatsreferenz],
253 [Kundenreferenz],
254 [Sammlerreferenz],
255 [Lastschrift_Ursprungsbetrag],
256 [Auslagenersatz_Ruecklastschrift],
257 [Beguenstigter],
258 [IBAN],
259 [BIC],
260 [Betrag] + (SELECT COALESCE(SUM([Betrag]), 0) FROM AccountingEntries ae2 WHERE ae1.ID = ae2.RefundedID) AS Betrag,
261 [Info],
262 [Hash],
263 CategoryAssignedManually,
264 RefundedID
265FROM AccountingEntries ae1
266WHERE Username = @username
267AND ae1.RefundedID IS NULL
268
269GO
270/****** Object: Table [dbo].[Categories] Script Date: 17.02.2019 13:15:45 ******/
271SET ANSI_NULLS ON
272GO
273SET QUOTED_IDENTIFIER ON
274GO
275CREATE TABLE [dbo].[Categories](
276 [ID] [int] IDENTITY(1,1) NOT NULL,
277 [Title] [nvarchar](50) NOT NULL,
278 [Color] [nvarchar](7) NOT NULL,
279 [OwnerUsername] [nvarchar](50) NOT NULL,
280 [NootPath] [hierarchyid] NULL,
281 CONSTRAINT [PK_Categories] PRIMARY KEY CLUSTERED
282(
283 [ID] ASC
284)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
285) ON [PRIMARY]
286GO
287/****** Object: UserDefinedFunction [dbo].[GetCategories] Script Date: 17.02.2019 13:15:45 ******/
288SET ANSI_NULLS ON
289GO
290SET QUOTED_IDENTIFIER ON
291GO
292CREATE function [dbo].[GetCategories] (@Username NVARCHAR(50))
293returns table
294as
295return
296select ID,Title,Color,OwnerUsername,(dbo.GetIDByNootPath(ca.NootPath.GetAncestor(1))) as SuperCategoryID,Nootpath from Categories ca
297where OwnerUsername = @Username
298GO
299/****** Object: Table [dbo].[SearchTerms] Script Date: 17.02.2019 13:15:45 ******/
300SET ANSI_NULLS ON
301GO
302SET QUOTED_IDENTIFIER ON
303GO
304CREATE TABLE [dbo].[SearchTerms](
305 [ID] [int] IDENTITY(1,1) NOT NULL,
306 [CategoryID] [int] NULL,
307 [Term] [nvarchar](200) NULL,
308 [OwnerUsername] [nvarchar](50) NULL,
309 CONSTRAINT [PK_SearchTerms] PRIMARY KEY CLUSTERED
310(
311 [ID] ASC
312)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
313) ON [PRIMARY]
314GO
315/****** Object: Table [dbo].[Users] Script Date: 17.02.2019 13:15:45 ******/
316SET ANSI_NULLS ON
317GO
318SET QUOTED_IDENTIFIER ON
319GO
320CREATE TABLE [dbo].[Users](
321 [Username] [nvarchar](50) NOT NULL,
322 [Password] [varchar](64) NOT NULL,
323 [AusgangssaldoDate] [datetime] NULL,
324 [AusgangssaldoBalance] [decimal](18, 2) NULL,
325 CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
326(
327 [Username] ASC
328)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
329) ON [PRIMARY]
330GO
331ALTER TABLE [dbo].[AccountingEntries] WITH NOCHECK ADD CONSTRAINT [FK_AccountingEntries_CategoryID] FOREIGN KEY([CategoryID])
332REFERENCES [dbo].[Categories] ([ID])
333GO
334ALTER TABLE [dbo].[AccountingEntries] CHECK CONSTRAINT [FK_AccountingEntries_CategoryID]
335GO
336ALTER TABLE [dbo].[AccountingEntries] WITH NOCHECK ADD CONSTRAINT [FK_AccountingEntries_RefundedID] FOREIGN KEY([RefundedID])
337REFERENCES [dbo].[AccountingEntries] ([ID])
338GO
339ALTER TABLE [dbo].[AccountingEntries] CHECK CONSTRAINT [FK_AccountingEntries_RefundedID]
340GO
341ALTER TABLE [dbo].[AccountingEntries] WITH NOCHECK ADD CONSTRAINT [FK_AccountingEntries_Username] FOREIGN KEY([Username])
342REFERENCES [dbo].[Users] ([Username])
343GO
344ALTER TABLE [dbo].[AccountingEntries] CHECK CONSTRAINT [FK_AccountingEntries_Username]
345GO
346ALTER TABLE [dbo].[Categories] WITH NOCHECK ADD CONSTRAINT [FK_Categories_OwnerUsername] FOREIGN KEY([OwnerUsername])
347REFERENCES [dbo].[Users] ([Username])
348GO
349ALTER TABLE [dbo].[Categories] CHECK CONSTRAINT [FK_Categories_OwnerUsername]
350GO
351ALTER TABLE [dbo].[SearchTerms] WITH NOCHECK ADD CONSTRAINT [FK_SearchTerms_CategoryID] FOREIGN KEY([CategoryID])
352REFERENCES [dbo].[Categories] ([ID])
353GO
354ALTER TABLE [dbo].[SearchTerms] CHECK CONSTRAINT [FK_SearchTerms_CategoryID]
355GO
356ALTER TABLE [dbo].[SearchTerms] WITH NOCHECK ADD CONSTRAINT [FK_SearchTerms_OwnerUsername] FOREIGN KEY([OwnerUsername])
357REFERENCES [dbo].[Users] ([Username])
358GO
359ALTER TABLE [dbo].[SearchTerms] CHECK CONSTRAINT [FK_SearchTerms_OwnerUsername]
360GO
361/****** Object: StoredProcedure [dbo].[AccountingEntryExists] Script Date: 17.02.2019 13:15:45 ******/
362SET ANSI_NULLS ON
363GO
364SET QUOTED_IDENTIFIER ON
365GO
366
367CREATE PROCEDURE [dbo].[AccountingEntryExists]
368 @Username [NVARCHAR] (50),
369 @Hash [VARCHAR] (64)
370AS
371SELECT CASE
372 WHEN EXISTS (
373 SELECT TOP (1)
374 *
375 FROM AccountingEntries
376 WHERE Username = @Username
377 AND Hash = @Hash
378 )
379 THEN 1
380 ELSE 0
381 END
382GO
383/****** Object: StoredProcedure [dbo].[AddAccountingEntry] Script Date: 17.02.2019 13:15:45 ******/
384SET ANSI_NULLS ON
385GO
386SET QUOTED_IDENTIFIER ON
387GO
388
389CREATE PROCEDURE [dbo].[AddAccountingEntry]
390 @Username [NVARCHAR] (50),
391 @CategoryID [INT] = null,
392 @Auftragskonto [NVARCHAR] (50),
393 @Buchungsdatum [DATETIME],
394 @Valutadatum [DATETIME],
395 @Buchungstext [NVARCHAR] (50),
396 @Verwendungszweck [NVARCHAR] (200),
397 @Glaeubiger_ID [NVARCHAR] (50),
398 @Mandatsreferenz [NVARCHAR] (50),
399 @Kundenreferenz [NVARCHAR] (50),
400 @Sammlerreferenz [NVARCHAR] (50),
401 @Lastschrift_Ursprungsbetrag [DECIMAL] (18,2),
402 @Auslagenersatz_Ruecklastschrift [NVARCHAR] (50),
403 @Beguenstigter [NVARCHAR] (50),
404 @IBAN [NVARCHAR] (50),
405 @BIC [NVARCHAR] (50),
406 @Betrag [DECIMAL] (18,2),
407 @Info [NVARCHAR] (200),
408 @Hash [VARCHAR] (64)
409AS
410INSERT INTO [dbo].[AccountingEntries]
411 (
412 [Username],
413 [CategoryID],
414 [Auftragskonto],
415 [Buchungsdatum],
416 [Valutadatum],
417 [Buchungstext],
418 [Verwendungszweck],
419 [Glaeubiger_ID],
420 [Mandatsreferenz],
421 [Kundenreferenz],
422 [Sammlerreferenz],
423 [Lastschrift_Ursprungsbetrag],
424 [Auslagenersatz_Ruecklastschrift],
425 [Beguenstigter],
426 [IBAN],
427 [BIC],
428 [Betrag],
429 [Info],
430 [Hash],
431 [CategoryAssignedManually])
432VALUES
433 (
434 @Username,
435 @CategoryID,
436 @Auftragskonto,
437 @Buchungsdatum,
438 @Valutadatum,
439 @Buchungstext,
440 @Verwendungszweck,
441 @Glaeubiger_ID,
442 @Mandatsreferenz,
443 @Kundenreferenz,
444 @Sammlerreferenz,
445 @Lastschrift_Ursprungsbetrag,
446 @Auslagenersatz_Ruecklastschrift,
447 @Beguenstigter,
448 @IBAN,
449 @BIC,
450 @Betrag,
451 @Info,
452 @Hash,
453 0)
454GO
455/****** Object: StoredProcedure [dbo].[AddCategory] Script Date: 17.02.2019 13:15:45 ******/
456SET ANSI_NULLS ON
457GO
458SET QUOTED_IDENTIFIER ON
459GO
460CREATE PROCEDURE [dbo].[AddCategory] (
461 @title NVARCHAR(50),
462 @color NVARCHAR(7),
463 @OwnerUsername NVARCHAR(50),
464 @SuperCategoryID int = NULL)
465AS
466INSERT INTO Categories
467OUTPUT INSERTED.ID
468VALUES (
469 @title,
470 @color,
471 @ownerusername,
472 dbo.GetNewChildPath(@SuperCategoryID,@OwnerUsername))
473GO
474/****** Object: StoredProcedure [dbo].[AddRootCategory] Script Date: 17.02.2019 13:15:45 ******/
475SET ANSI_NULLS ON
476GO
477SET QUOTED_IDENTIFIER ON
478GO
479CREATE procedure [dbo].[AddRootCategory]
480as
481begin
482 insert into Users values('root','J1Xm9ngI5FnwEjud3Wt66uGQOjhnvNf0wWQ5KHBYhHUh5tEcyXJJoYPOPnPuFdA5',null,null);
483 insert into Categories values('root','fffffff','root',hierarchyid::GetRoot())
484end
485GO
486/****** Object: StoredProcedure [dbo].[AddSearchterm] Script Date: 17.02.2019 13:15:45 ******/
487SET ANSI_NULLS ON
488GO
489SET QUOTED_IDENTIFIER ON
490GO
491
492CREATE PROCEDURE [dbo].[AddSearchterm]
493 (
494 @categoryid INT,
495 @term NVARCHAR(200),
496 @ownerusername NVARCHAR(50))
497AS
498INSERT INTO SearchTerms
499OUTPUT
500INSERTED.ID
501VALUES
502 (
503 @categoryid,
504 @term,
505 @ownerusername)
506GO
507/****** Object: StoredProcedure [dbo].[AddUser] Script Date: 17.02.2019 13:15:45 ******/
508SET ANSI_NULLS ON
509GO
510SET QUOTED_IDENTIFIER ON
511GO
512
513CREATE PROCEDURE [dbo].[AddUser]
514 (
515 @username NVARCHAR(50),
516 @password VARCHAR(64),
517 @ausgangssaldodate DATE,
518 @ausgangssaldobalance DECIMAL(18,2))
519AS
520INSERT INTO users
521 (
522 [Username],
523 [Password],
524 [AusgangssaldoDate],
525 [AusgangssaldoBalance]
526 )
527VALUES
528 (
529 @username,
530 @password,
531 @ausgangssaldodate,
532 @ausgangssaldobalance)
533GO
534/****** Object: StoredProcedure [dbo].[ChangePassword] Script Date: 17.02.2019 13:15:45 ******/
535SET ANSI_NULLS ON
536GO
537SET QUOTED_IDENTIFIER ON
538GO
539
540CREATE PROCEDURE [dbo].[ChangePassword]
541 (
542 @username NVARCHAR(50),
543 @password VARCHAR (64))
544AS
545UPDATE Users
546SET Password = @password
547WHERE Username = @username
548GO
549/****** Object: StoredProcedure [dbo].[DeleteCategory] Script Date: 17.02.2019 13:15:45 ******/
550SET ANSI_NULLS ON
551GO
552SET QUOTED_IDENTIFIER ON
553GO
554
555CREATE PROCEDURE [dbo].[DeleteCategory] (@username NVARCHAR(50),
556@ID INT)
557AS
558begin
559 exec dbo.DetachCategoryDescendants @idToDelete = @ID
560 DELETE FROM [dbo].[Categories]
561 WHERE [Categories].OwnerUsername = @username
562 AND [Categories].ID = @ID
563end
564GO
565/****** Object: StoredProcedure [dbo].[DeleteSearchterm] Script Date: 17.02.2019 13:15:45 ******/
566SET ANSI_NULLS ON
567GO
568SET QUOTED_IDENTIFIER ON
569GO
570
571CREATE PROCEDURE [dbo].[DeleteSearchterm]
572 (@username NVARCHAR(50),
573 @ID INT)
574AS
575DELETE FROM [dbo].[SearchTerms]
576WHERE [SearchTerms].OwnerUsername = @username
577 AND [SearchTerms].ID = @ID
578GO
579/****** Object: StoredProcedure [dbo].[DetachCategoryDescendants] Script Date: 17.02.2019 13:15:45 ******/
580SET ANSI_NULLS ON
581GO
582SET QUOTED_IDENTIFIER ON
583GO
584
585create procedure [dbo].[DetachCategoryDescendants] (@idToDelete int)
586-- Benötigt für DeleteCategory, da sonst alle Kinder des zu löschenden Nodes keinen papa mehr haben würden :(
587as
588begin
589 declare @currNootpath hierarchyid = (select Nootpath from Categories where ID = @idToDelete)
590 update Categories
591 -- Mache die Kinder des zu löschenden Nodes zu Kidern des "Opa-Nodes"
592 set Nootpath = Nootpath.GetAncestor(2).GetDescendant(null,null)
593 -- Wo der ParentNode der zu löschende Node ist
594 where Nootpath.GetAncestor(1) = @currNootpath
595end
596
597GO
598/****** Object: StoredProcedure [dbo].[GetAccountingEntries] Script Date: 17.02.2019 13:15:45 ******/
599SET ANSI_NULLS ON
600GO
601SET QUOTED_IDENTIFIER ON
602GO
603
604CREATE PROCEDURE [dbo].[GetAccountingEntries]
605 @username [NVARCHAR] (50),
606 @categoryID [INTEGER] = null,
607 @skip [INTEGER],
608 @count [INTEGER]
609AS
610SELECT *
611FROM getuserentries(@username) ae
612WHERE @categoryID = -1
613 OR ae.CategoryID = @categoryID
614ORDER BY ae.Buchungsdatum DESC,
615 ae.id DESC OFFSET(@skip) ROWS
616
617FETCH NEXT (@count) ROWS ONLY
618
619GO
620/****** Object: StoredProcedure [dbo].[GetCategory] Script Date: 17.02.2019 13:15:45 ******/
621SET ANSI_NULLS ON
622GO
623SET QUOTED_IDENTIFIER ON
624GO
625
626
627create PROCEDURE [dbo].[GetCategory] (
628 @Username NVARCHAR(50),
629 @categoryId int)
630AS
631SELECT *
632FROM GetCategories(@Username) ca
633where ca.ID = @categoryId
634GO
635/****** Object: StoredProcedure [dbo].[GetRootCategoryID] Script Date: 17.02.2019 13:15:45 ******/
636SET ANSI_NULLS ON
637GO
638SET QUOTED_IDENTIFIER ON
639GO
640create procedure [dbo].[GetRootCategoryID]
641as
642select id from Categories where NootPath = hierarchyid::GetRoot();
643GO
644/****** Object: StoredProcedure [dbo].[GetSearchterm] Script Date: 17.02.2019 13:15:45 ******/
645SET ANSI_NULLS ON
646GO
647SET QUOTED_IDENTIFIER ON
648GO
649
650CREATE PROCEDURE [dbo].[GetSearchterm]
651 (
652 @Username NVARCHAR(50),
653 @searchTermId INT)
654AS
655SELECT *
656FROM SearchTerms
657WHERE SearchTerms.OwnerUsername = @Username
658 AND SearchTerms.ID = @searchTermId
659GO
660/****** Object: StoredProcedure [dbo].[GetSearchterms] Script Date: 17.02.2019 13:15:45 ******/
661SET ANSI_NULLS ON
662GO
663SET QUOTED_IDENTIFIER ON
664GO
665
666CREATE PROCEDURE [dbo].[GetSearchterms]
667 (
668 @Username NVARCHAR(50))
669AS
670SELECT *
671FROM SearchTerms
672WHERE SearchTerms.OwnerUsername = @Username
673GO
674/****** Object: StoredProcedure [dbo].[GetSumsOfCategories] Script Date: 17.02.2019 13:15:45 ******/
675SET ANSI_NULLS ON
676GO
677SET QUOTED_IDENTIFIER ON
678GO
679
680
681create procedure [dbo].[GetSumsOfCategories] (@username nvarchar(50)) as
682select sum(UserEntries.betrag) as Summe, ca.Title as CategoryTitle
683 from (select * from GetUserEntries(@username)) UserEntries
684 join GetCategories(@Username) ca on UserEntries.CategoryID = ca.ID
685group by ca.Title
686
687GO
688/****** Object: StoredProcedure [dbo].[GetUser] Script Date: 17.02.2019 13:15:45 ******/
689SET ANSI_NULLS ON
690GO
691SET QUOTED_IDENTIFIER ON
692GO
693
694CREATE PROCEDURE [dbo].[GetUser] (@username NVARCHAR(50))
695AS
696SELECT Username,
697 AusgangssaldoDate,
698 AusgangssaldoBalance
699FROM Users
700WHERE Username = @username
701GO
702/****** Object: StoredProcedure [dbo].[GetUserCategories] Script Date: 17.02.2019 13:15:45 ******/
703SET ANSI_NULLS ON
704GO
705SET QUOTED_IDENTIFIER ON
706GO
707
708CREATE PROCEDURE [dbo].[GetUserCategories] (
709 @Username NVARCHAR(50))
710AS
711SELECT *
712FROM GetCategories(@Username)
713GO
714/****** Object: StoredProcedure [dbo].[SetAccountingEntry] Script Date: 17.02.2019 13:15:45 ******/
715SET ANSI_NULLS ON
716GO
717SET QUOTED_IDENTIFIER ON
718GO
719
720CREATE PROCEDURE [dbo].[SetAccountingEntry]
721 @ID [INT],
722 @Username [NVARCHAR] (50),
723 @CategoryID [INT] = null,
724 @Auftragskonto [NVARCHAR] (50),
725 @Buchungsdatum [DATETIME],
726 @Valutadatum [DATETIME],
727 @Buchungstext [NVARCHAR] (50),
728 @Verwendungszweck [NVARCHAR] (200),
729 @Glaeubiger_ID [NVARCHAR] (50),
730 @Mandatsreferenz [NVARCHAR] (50),
731 @Kundenreferenz [NVARCHAR] (50),
732 @Sammlerreferenz [NVARCHAR] (50),
733 @Lastschrift_Ursprungsbetrag [DECIMAL] (18,2),
734 @Auslagenersatz_Ruecklastschrift [NVARCHAR] (50),
735 @Beguenstigter [NVARCHAR] (50),
736 @IBAN [NVARCHAR] (50),
737 @BIC [NVARCHAR] (50),
738 @Betrag [DECIMAL] (18,2),
739 @Info [NVARCHAR] (200)
740AS
741UPDATE [dbo].[AccountingEntries]
742SET AccountingEntries.Username = @Username,
743 AccountingEntries.CategoryID = @CategoryID,
744 AccountingEntries.Auftragskonto = @Auftragskonto,
745 AccountingEntries.Buchungsdatum = @Buchungsdatum,
746 AccountingEntries.Valutadatum = @Valutadatum,
747 AccountingEntries.Buchungstext = @Buchungstext,
748 AccountingEntries.Verwendungszweck = @Verwendungszweck,
749 AccountingEntries.Glaeubiger_ID = @Glaeubiger_ID,
750 AccountingEntries.Mandatsreferenz = @Mandatsreferenz,
751 AccountingEntries.Kundenreferenz = @Kundenreferenz,
752 AccountingEntries.Sammlerreferenz = @Sammlerreferenz,
753 AccountingEntries.Lastschrift_Ursprungsbetrag = @Lastschrift_Ursprungsbetrag,
754 AccountingEntries.Auslagenersatz_Ruecklastschrift = @Auslagenersatz_Ruecklastschrift,
755 AccountingEntries.Beguenstigter = @Beguenstigter,
756 AccountingEntries.IBAN = @IBAN,
757 AccountingEntries.BIC = @BIC,
758 AccountingEntries.Betrag = @Betrag,
759 AccountingEntries.Info = @Info
760WHERE AccountingEntries.ID = @ID
761GO
762/****** Object: StoredProcedure [dbo].[SetAccountingEntryCategory] Script Date: 17.02.2019 13:15:45 ******/
763SET ANSI_NULLS ON
764GO
765SET QUOTED_IDENTIFIER ON
766GO
767
768CREATE PROCEDURE [dbo].[SetAccountingEntryCategory] @ID [int],
769 @CategoryID [int] = null
770AS
771UPDATE [dbo].[AccountingEntries]
772SET AccountingEntries.CategoryID = @CategoryID
773WHERE AccountingEntries.ID = @ID
774AND AccountingEntries.CategoryAssignedManually = 0;
775
776GO
777/****** Object: StoredProcedure [dbo].[SetAccountingEntryCategoryManuelly] Script Date: 17.02.2019 13:15:45 ******/
778SET ANSI_NULLS ON
779GO
780SET QUOTED_IDENTIFIER ON
781GO
782
783CREATE PROCEDURE [dbo].[SetAccountingEntryCategoryManuelly]
784 @Username [NVARCHAR] (50),
785 @ID [int],
786 @CategoryID [int] = null
787AS
788UPDATE [dbo].[AccountingEntries]
789SET AccountingEntries.CategoryID = @CategoryID,
790 AccountingEntries.CategoryAssignedManually = 1
791WHERE AccountingEntries.ID = @ID
792AND AccountingEntries.Username = @Username
793
794GO
795/****** Object: StoredProcedure [dbo].[SetAusgangssaldo] Script Date: 17.02.2019 13:15:45 ******/
796SET ANSI_NULLS ON
797GO
798SET QUOTED_IDENTIFIER ON
799GO
800
801CREATE PROCEDURE [dbo].[SetAusgangssaldo]
802 (
803 @username NVARCHAR(50),
804 @balance DECIMAL(18,2),
805 @date DATE)
806AS
807UPDATE Users
808SET AusgangssaldoBalance = @balance,
809 AusgangssaldoDate = @date
810WHERE Username = @username
811GO
812/****** Object: StoredProcedure [dbo].[SetRefundedID] Script Date: 17.02.2019 13:15:45 ******/
813SET ANSI_NULLS ON
814GO
815SET QUOTED_IDENTIFIER ON
816GO
817
818CREATE PROCEDURE [dbo].[SetRefundedID]
819 @Username [NVARCHAR] (50),
820 @ID [int],
821 @RefundedID [int]
822AS
823UPDATE [dbo].[AccountingEntries]
824SET AccountingEntries.RefundedID = @RefundedID
825WHERE AccountingEntries.ID = @ID
826AND AccountingEntries.Username = @Username
827
828GO
829/****** Object: StoredProcedure [dbo].[SumSaldoBetween] Script Date: 17.02.2019 13:15:45 ******/
830SET ANSI_NULLS ON
831GO
832SET QUOTED_IDENTIFIER ON
833GO
834
835CREATE PROCEDURE [dbo].[SumSaldoBetween]
836 @Username [NVARCHAR] (50),
837 @OlderDate [DATETIME],
838 @NewerDate [DATETIME]
839AS
840SELECT Sum(AccountingEntries.Betrag)
841FROM AccountingEntries
842WHERE @OlderDate < AccountingEntries.Buchungsdatum
843 AND @NewerDate >= AccountingEntries.Buchungsdatum
844 AND AccountingEntries.Username = @Username
845GO
846/****** Object: StoredProcedure [dbo].[UpdateCategory] Script Date: 17.02.2019 13:15:45 ******/
847SET ANSI_NULLS ON
848GO
849SET QUOTED_IDENTIFIER ON
850GO
851CREATE PROCEDURE [dbo].[UpdateCategory] (
852 @username NVARCHAR(50),
853 @ID INT,
854 @title NVARCHAR(50),
855 @color NVARCHAR(7),
856 @SuperCategoryID INT = null
857 )
858AS
859UPDATE [dbo].[Categories]
860SET [Categories].Title = @title,
861 [Categories].Color = @color,
862 [Categories].Nootpath = dbo.GetNewChildPath(@SuperCategoryID,@username )
863WHERE [Categories].ID = @ID
864AND [Categories].OwnerUsername = @username
865GO
866/****** Object: StoredProcedure [dbo].[UpdateSearchterm] Script Date: 17.02.2019 13:15:45 ******/
867SET ANSI_NULLS ON
868GO
869SET QUOTED_IDENTIFIER ON
870GO
871
872CREATE PROCEDURE [dbo].[UpdateSearchterm]
873 (
874 @username NVARCHAR(50),
875 @ID INT,
876 @term NVARCHAR(50),
877 @CategoryID INT
878)
879AS
880UPDATE [dbo].[SearchTerms]
881SET [SearchTerms].Term = @term,
882 [SearchTerms].CategoryID = @CategoryID
883WHERE [SearchTerms].ID = @ID
884 AND [SearchTerms].OwnerUsername = @username
885GO
886/****** Object: StoredProcedure [dbo].[UserAndPasswordValid] Script Date: 17.02.2019 13:15:45 ******/
887SET ANSI_NULLS ON
888GO
889SET QUOTED_IDENTIFIER ON
890GO
891
892CREATE PROCEDURE [dbo].[UserAndPasswordValid]
893 @Username [NVARCHAR] (50),
894 @Password [VARCHAR] (64)
895AS
896SELECT CASE
897 WHEN EXISTS (
898 SELECT TOP (1)
899 *
900 FROM Users
901 WHERE Username = @Username
902 AND Password = @Password)
903 THEN 1
904 ELSE 0
905 END
906GO
907/****** Object: StoredProcedure [dbo].[UserExists] Script Date: 17.02.2019 13:15:45 ******/
908SET ANSI_NULLS ON
909GO
910SET QUOTED_IDENTIFIER ON
911GO
912
913CREATE PROCEDURE [dbo].[UserExists]
914 @Username [NVARCHAR] (50)
915AS
916SELECT CASE
917 WHEN EXISTS (
918 SELECT TOP (1)
919 *
920 FROM Users
921 WHERE Username = @Username)
922 THEN 1
923 ELSE 0
924 END
925GO
926USE [master]
927GO
928ALTER DATABASE [finanzuebersicht] SET READ_WRITE
929GO