Gz8pVsxH

1use master 
2go
3
4if exists (
5	select	name 
6	from	sys.databases 
7	where	name = N'_Starkov' )
8alter database [_Starkov] set single_user with rollback immediate
9go
10
11if exists (
12	select	name 
13	from	sys.databases 
14	where	name = N'_Starkov' )
15drop database [_Starkov]
16go
17
18create database [_Starkov]
19go
20
21use [_Starkov]
22go
23
24if object_id('_Starkov.Auctions', 'U') is not null
25	drop table  _Starkov.Auctions
26go
27
28create table Auctions (
29	id				int,
30	time_start		datetime,
31	auction_name	varchar(100),
32	auction_place	varchar(100),
33	constraint pk_auction_id primary key (id)
34)
35go
36
37if object_id('_Starkov.Peoples', 'U') is not null
38	drop table  _Starkov.Peoples
39go
40
41create table Peoples (
42	id	 int,
43	name varchar(100),
44	constraint pk_people_id primary key (id)
45)
46go
47
48if object_id('_Starkov.Things', 'U') is not null
49	drop table  _Starkov.Things
50go
51
52create table Things (
53	id	 int,
54	descr	varchar(100)
55	constraint pk_thing_id primary key (id)
56)
57go
58
59if object_id('_Starkov.ForSale', 'U') is not null
60	drop table  _Starkov.ForSale
61go
62
63create table ForSale (
64	thing_id	int,
65	lot			int,
66	cost		money,
67	auction_id	int,
68	seller_id	int,
69	constraint pk_lot_id primary key (auction_id, lot),
70	constraint not_equal	 unique(lot, auction_id),
71	constraint fk_think_id	 foreign key (thing_id)	  references Things(id)	  on update cascade,
72	constraint fk_auction_id foreign key (auction_id) references Auctions(id) on update cascade,
73	constraint fk_seller_id	 foreign key (seller_id)  references Peoples(id)  on update cascade,
74)
75go
76
77if object_id('_Starkov.Results', 'U') is not null
78	drop table  _Starkov.Results
79go
80
81create table Results (
82	lot			int,
83	auction_id	int,
84	byer_id		int,
85	sell_count	int,
86	constraint fk_buy	  foreign key (auction_id, lot) references ForSale(auction_id, lot),
87	constraint fk_byer_id foreign key (byer_id)			references Peoples(id)				on update cascade
88)
89go
90
91insert into Auctions(id, auction_place, auction_name, time_start) values
92	(1, N'Мегаполис',	N'Вещицы',   '20120601'),
93	(2, N'МЕГА',		N'Продаем',  '20120602'),
94	(3, N'Свалка',		N'Антиквар', '20120603'),
95	(4, N'Подворотня',	N'Штуки', '20120604'),
96	(5, N'Падик',		N'Сиги',  '20120605')
97go
98
99insert into Peoples(id, name) values
100	(1, N'Пятерочка'),
101	(2, N'Монетка'),
102	(3, N'Ашан'),
103	(4, N'Вася Пупкин'),
104	(5, N'Спекулянт')
105go
106
107insert into Things(id, descr) values
108	(1, N'Nuka cola'),
109	(2, N'Скума'),
110	(3, N'Огненная вода'),
111	(4, N'Колбаса Докторская'),
112	(5, N'Пицца')
113go
114
115if object_id( '_Starkov.Put', 'P' ) is not null   
116    drop procedure _Starkov.Put
117go
118
119create procedure Put
120	@seller		varchar(100), 
121	@auction	varchar(100), 
122	@thing		varchar(100),
123	@cost		money
124as
125    declare @seller_id int, @auction_id int, @thing_id int, @lot int;
126	select @lot = count(*) from ForSale;
127	select @seller_id  = id from Peoples  where name = @seller;
128	select @auction_id = id from Auctions where auction_place = @auction;
129	select @thing_id   = id from Things   where descr = @thing;
130	insert into ForSale(thing_id, lot, auction_id, seller_id, cost) values (@thing_id, @lot, @auction_id, @seller_id, @cost);
131go
132
133if object_id( '_Starkov.Buy', 'P' ) is not null   
134    drop procedure _Starkov.Buy
135go
136
137create procedure Buy
138	@buyer	 varchar(100), 
139	@auction varchar(100), 
140	@lot	 int,
141	@count	 int
142as
143    declare @buyer_id int, @auction_id int;
144	select	@buyer_id   = id from Peoples  where name = @buyer;
145	select	@auction_id = id from Auctions where auction_place = @auction;
146	insert into Results(lot, auction_id, byer_id, sell_count) values (@lot, @auction_id, @buyer_id, @count);
147go
148
149if object_id( '_Starkov.GetItemCost', 'F' ) is not null   
150    drop function _Starkov.GetItemCost
151go
152
153create function GetItemCost(@item int, @auction int, @lot int)
154returns money as
155begin
156	declare @ret money;
157	select  @ret = cost from ForSale where lot = @lot and @auction = auction_id;
158	return @ret;
159end
160go
161
162if object_id( '_Starkov.GetItemId', 'F' ) is not null   
163    drop function _Starkov.GetItemId
164go
165
166create function GetItemId(@auction_id int, @lot int)
167returns int as
168begin
169	declare @ret int;
170	select  @ret = thing_id from ForSale where @auction_id = auction_id and @lot = lot;
171	return @ret;
172end
173go
174
175if object_id( '_Starkov.OtherAuctions', 'F' ) is not null   
176    drop function _Starkov.OtherAuctions
177go
178
179create function OtherAuctions(@lot int, @auction_id int)
180returns varchar(1000) as
181begin
182	declare @item_id int;
183	select @item_id = dbo.GetItemId(@auction_id, @lot);
184	declare @ret varchar(1000);
185	select  @ret = replace(
186		(select distinct auction_place + ' ' from ForSale
187		 join Auctions a on auction_id = a.id
188		 where dbo.GetItemId(auction_id, lot) = @item_id and auction_id != @auction_id
189		 for xml path('')), ' ', ', ')
190	return @ret;
191end
192go
193
194if object_id( '_Starkov.PeopleItems', 'F' ) is not null   
195    drop function _Starkov.PeopleItems
196go
197
198create function PeopleItems(@id int)
199returns varchar(1000) as
200begin
201	declare @ret varchar(1000);
202	select  @ret = replace(
203		(select distinct i.descr + ' ' from ForSale f
204		 join Peoples p on f.seller_id = @id
205		 join Things i on f.thing_id = i.id
206		 for xml path('')), ' ', ', ')
207	return @ret;
208end
209go
210
211if object_id( '_Starkov.ItemsCount', 'F' ) is not null   
212    drop function _Starkov.ItemsCount
213go
214
215create function ItemsCount(@id int)
216returns int as
217begin
218	declare @ret int;
219	select  @ret = coalesce(count(*), 0) from Results where auction_id = @id
220	return @ret;
221end
222go
223
224if object_id( '_Starkov.BoughtCount', 'F' ) is not null   
225    drop function _Starkov.BoughtCount
226go
227
228create function BoughtCount(@id int, @start datetime, @end datetime)
229returns int as
230begin
231	declare @ret int;
232	select  @ret = coalesce(sum(sell_count), 0) from Results r join Auctions a on r.auction_id = a.id 
233				 where byer_id = @id and @start < a.time_start and a.time_start < @end;
234	return @ret;
235end
236go
237
238if object_id( '_Starkov.Income', 'F' ) is not null   
239    drop function _Starkov.Income
240go
241
242create function Income(@id int)
243returns money as
244begin
245	declare @ret money;
246	select  @ret = coalesce(sum(sell_count * dbo.GetItemCost(dbo.GetItemId(auction_id, lot), auction_id, lot)), 0) from Results where auction_id = @id;
247	return @ret;
248end
249go
250
251if object_id( '_Starkov.SellerIncome', 'F' ) is not null   
252    drop function _Starkov.SellerIncome
253go
254
255create function SellerIncome(@id int)
256returns money as
257begin
258	declare @ret money;
259	select  @ret = coalesce(sum(sell_count * dbo.GetItemCost(dbo.GetItemId(r.auction_id, r.lot), r.auction_id, r.lot)), 0) 
260				   from Results r join ForSale s on s.auction_id = r.auction_id and s.lot = r.lot where s.seller_id = @id;
261	return @ret;
262end
263go
264
265select * from ForSale
266select * from Results
267
268select auction_place as N'Место проведения аукциона', 
269	   dbo.Income(id) as N'Доход' 
270	   from Auctions order by dbo.Income(id) desc
271go
272
273declare @from datetime 
274select @from = '20120601' 
275declare @to	  datetime 
276select @to = '20120605' 
277select t.descr as N'Вещь', dbo.OtherAuctions(lot, auction_id) as N'Другие аукционы' 
278	from Results join Things t on dbo.GetItemId(auction_id, lot) = t.id
279				 join Auctions a on auction_id = a.id
280	where @from <= a.time_start and a.time_start <= @to
281go
282
283declare @from datetime 
284select @from = '20120601' 
285declare @to	  datetime 
286select @to = '20120605' 
287select p.name as N'Продавец', dbo.SellerIncome(p.id) as N'Доход' 
288	from Results r join ForSale s on r.auction_id = s.auction_id and r.lot = s.lot
289				   join Peoples p on s.seller_id = p.id
290				   join Auctions a on r.auction_id = a.id
291	where @from <= a.time_start and a.time_start <= @to
292	order by dbo.SellerIncome(p.id)
293go
294
295declare @place varchar(100)
296select auction_name as N'Аукцион', dbo.ItemsCount(id) as 'Количество вещей' 
297	from Auctions where @place = auction_place
298	order by dbo.ItemsCount(id) desc
299go
300
301declare @from datetime 
302select @from = '20120601' 
303declare @to	  datetime 
304select @to = '20120605' 
305select distinct p.name as N'Продавец', dbo.PeopleItems(p.id) as N'Выставленные предметы'
306	from ForSale s join Peoples p on  s.seller_id = p.id
307				   join Auctions a on s.auction_id = a.id
308	where @from <= a.time_start and a.time_start <= @to
309go
310
311declare @from datetime 
312select @from = '20120601' 
313declare @to	  datetime 
314select @to = '20120605' 
315select name as N'Покупатель', dbo.BoughtCount(id, @from, @to) as N'Количество купленных предметов'
316	from Peoples
317go