LpXTEczv

1use master 
2go
3
4if exists (
5	select	name 
6	from	sys.databases 
7	where	name = N'_Kvashnina' )
8alter database [_Kvashnina] set single_user with rollback immediate
9go
10
11if exists (
12	select	name 
13	from	sys.databases 
14	where	name = N'_Kvashnina' )
15drop database [_Kvashnina]
16go
17
18create database [_Kvashnina]
19go
20
21use [_Kvashnina]
22go
23
24if object_id('_Kvashnina.Currencies', 'U') is not null
25	drop table  _Kvashnina.Currencies
26go
27
28create table Currencies (
29	id		tinyint,
30	code	varchar(3),
31	constraint uniq_curr_name unique(code),
32	constraint pk_currency_id primary key (id)
33)
34go
35
36insert into Currencies(id, code) values
37(1, 'USD'), (2, 'RUB'), (3, 'EUR'), (4, 'JPY') --, (5, 'UAN')
38go
39
40if object_id('_Kvashnina.CurrencyExchange', 'U') is not null
41	drop table  _Kvashnina.CurrencyExchange
42go
43
44create table CurrencyExchange (
45	id_sell		tinyint,
46	id_recv		tinyint,
47	exchange	smallmoney,
48	check (id_sell != id_recv or exchange=1),
49	constraint uniq_exchn unique(id_sell, id_recv),
50	constraint fk_sell_id foreign key (id_sell) references Currencies(id),
51	constraint fk_recv_id foreign key (id_recv) references Currencies(id)
52)
53go
54
55insert into CurrencyExchange(id_sell, id_recv, exchange) values
56(1, 1, 1),		(1, 2, 64.9013), (1, 3, 0.9439), (1, 4, 113.2246),-- (1, 5, 6.9162),
57(2, 1, 0.0154),	(2, 2, 1),		 (2, 3, 0.0145), (2, 4, 1.7445),--   (2, 5, 0.1065),
58(3, 1, 1.0593), (3, 2, 68.75),	 (3, 3, 1),		 (3, 4, 119.9388),-- (3, 5, 7.3264),
59(4, 1, 0.0088), (4, 2, 0.5732),	 (4, 3, 0.0083), (4, 4, 1)--,		   (4, 5, 0.0611),
60--(5, 1, 0.1445), (5, 2, 9.3838),  (5, 3, 0.1364), (5, 4, 16.3706)  (5, 5, 1)
61go
62
63
64if object_id('_Kvashnina.Moneys', 'U') is not null
65	drop table  _Kvashnina.Moneys
66go
67
68create table Moneys (
69	currency_id	tinyint,
70	currency_count float,
71	check (currency_count > 0),
72	constraint fk_money_id FOREIGN KEY (currency_id) references Currencies(id) on update cascade,
73)
74go
75
76if object_id( '_Kvashnina.CostIn', 'F' ) is not null   
77    drop function _Kvashnina.CostIn
78go
79
80create function CostIn(@currency varchar(3))
81returns money as
82begin
83	declare @ret money;  
84    select @ret = sum(e.exchange * m.currency_count)  
85    from Moneys m
86	inner join Currencies c
87	on c.code = @currency
88	inner join CurrencyExchange e
89	on e.id_sell = m.currency_id and e.id_recv = c.id
90	return @ret
91end
92go
93
94if object_id( '_Kvashnina.CurrencyId', 'F' ) is not null   
95    drop function _Kvashnina.CurrencyId
96go
97
98create function CurrencyId(@currency varchar(3))
99returns tinyint as
100begin
101	declare @ret tinyint
102    select @ret = id  
103    from Currencies
104	where code=@currency
105	return @ret
106end
107go
108
109if object_id( '_Kvashnina.CurrencyId', 'F' ) is not null   
110    drop function _Kvashnina.CurrencyId
111go
112
113create function CurrencyCode(@currency tinyint)
114returns varchar(3) as
115begin
116	declare @ret varchar(3)
117    select @ret = code
118    from Currencies
119	where id=@currency
120	return @ret
121end
122go
123
124if object_id( '_Kvashnina.GetMoney', 'F' ) is not null   
125    drop function _Kvashnina.GetMoney
126go
127
128create function GetMoney(@currency varchar(3))
129returns money as
130begin
131	declare @retur money
132	declare @ret tinyint
133	select @ret = dbo.CurrencyId(@currency)
134	select @retur = sum(currency_count)
135	from Moneys
136	where currency_id=@ret
137	return @retur;
138
139end
140go
141
142create procedure AddMoney
143	@code varchar(3), 
144	@count money
145as
146    declare @ret tinyint
147	select @ret = dbo.CurrencyId(@code)
148	insert into Moneys(currency_id, currency_count) values (@ret, @count)
149go
150
151create procedure RemoveMoney
152	@code varchar(3),
153	@count money
154as
155    declare @ret tinyint
156	select @ret = dbo.CurrencyId(@code)
157	declare @exc money
158	select @exc = dbo.GetMoney(@code)
159	declare @got money
160	select @got = @exc - @count
161	if (@exc > @count)
162	begin
163		delete from Moneys where currency_id=@ret
164		execute AddMoney @code, @got
165	end
166	else
167		raiserror ('Íå õâàòàåò äåíåã', 10, 1)
168go
169
170declare @cols	as nvarchar(max),
171		@query	as nvarchar(max)
172
173select @cols = stuff(
174	(
175		select ',' + quotename(code) 
176		from Currencies
177		group by id, code
178		order by id
179		for xml path(''), type
180	).value('.', 'nvarchar(max)'), 1, 1, ''
181)
182
183set @query = 'SELECT name, ' + @cols + N' 
184            from 
185            (
186              select c.code,
187                e.exchange,
188                s.code name
189              from Currencies s
190              left join CurrencyExchange e
191                on s.id = e.id_recv
192              left join Currencies c
193                on e.id_sell = c.id
194            ) x
195            pivot 
196            (
197                max(exchange)
198                for code in (' + @cols + N')
199            ) p
200			order by dbo.CurrencyId(name)'
201
202execute (@query)
203go
204
205execute dbo.AddMoney 'USD', 200
206execute dbo.RemoveMoney 'USD', 200
207
208select distinct dbo.CurrencyCode(currency_id) as "‚ «îâ ", dbo.GetMoney(dbo.CurrencyCode(currency_id)) as "„¥­¥£ ¢ ª®è¥«ìª¥"
209from Moneys
210go
211
212print(dbo.CostIn('RUB'))
213go
214
215select code as "‚ «îâ ", dbo.CostIn(code) as "„¥­¥£ ¢ ª®è¥«ìª¥"
216from Currencies
217go