· 7 years ago · Dec 01, 2018, 07:54 PM
1IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('TMP'))
2DROP TABLE TMP
3
4CREATE TABLE TMP
5(
6 ID INTEGER IDENTITY(1,1),
7
8 CName VARCHAR(35)NOT NULL,
9 CSurname VARCHAR(35)NOT NULL,
10 CPhoneNumber VARCHAR(35)NOT NULL,
11 CEmail VARCHAR(35) NOT NULL,
12 CCreditCard VARCHAR(35) ,
13
14 OrderTime datetime,
15 OrderPrice int,
16
17 AdName varchar(35),
18 AdPublicationDate datetime,
19 AdsTextPrice int,
20 AdsHashtagsPrice int,
21 AdsImagesPrice int,
22);
23
24IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('customers_'))
25DROP TABLE customers_
26CREATE TABLE customers_
27(
28 ID INTEGER IDENTITY(1,1),
29
30 CName VARCHAR(35),
31 CSurname VARCHAR(35),
32 CPhoneNumber VARCHAR(35),
33 CEmail VARCHAR(35),
34 CCreditCard VARCHAR(35),
35);
36
37IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('orders_'))
38DROP TABLE orders_
39create table orders_(
40 ID INTEGER IDENTITY(1,1),
41 OrderTime datetime,
42 OrderPrice int,
43 cust int
44);
45IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('ads_'))
46DROP TABLE ads_
47create table ads_(
48 ID INTEGER IDENTITY(1,1),
49 AdName varchar(35),
50 AdPublicationDate datetime,
51 AdsTextPrice int,
52 AdsHashtagsPrice int,
53 AdsImagesPrice int,
54 ord int
55);
56
57INSERT INTO TMP
58(CName,CSurname,CPhoneNumber, CEmail,CCreditCard,
59 OrderTime,OrderPrice,
60AdName, AdPublicationDate,AdsTextPrice,AdsHashtagsPrice, AdsImagesPrice)
61SELECT Name_, Surname, PhoneNumber,Email,CreditCardNumber, OrderTime, OrderPrice,
62AdName, AdPublicationDate,AdsTextPrice,AdsHashtagsPrice,AdsImagesPrice FROM AdsList
63join OrderList on AdsList.OrderID = OrderList.OrderID
64join AdCustomer on AdCustomer.AdCustomerID = OrderList.AdCustomerID;
65
66
67
68SELECT * FROM TMP;
69
70
71declare @CName VARCHAR(35), @CSurname VARCHAR(35), @CPhoneNumber VARCHAR(35),@CEmail VARCHAR(35),@CCreditCard VARCHAR(35);
72declare CustomersCursor CURSOR for select CName,CSurname,CPhoneNumber, CEmail, CCreditCard from TMP;
73
74open CustomersCursor
75fetch next from CustomersCursor
76into
77 @CName,
78 @CSurname,
79 @CPhoneNumber,
80 @CEmail,
81 @CCreditCard
82
83while @@FETCH_STATUS = 0 begin
84select * from customers_;
85
86if 0 = (Select Count(*) from customers_ where CName = @CName and CSurname = @CSurname and CPhoneNumber = @CPhoneNumber and CEmail = @CEmail )
87 insert into customers_( CName,CSurname,CPhoneNumber, CEmail, CCreditCard ) values(@CName,
88 @CSurname,
89 @CPhoneNumber,
90 @CEmail,
91 @CCreditCard)
92fetch next from CustomersCursor
93into
94 @CName,
95 @CSurname,
96 @CPhoneNumber,
97 @CEmail,
98 @CCreditCard
99end
100close CustomersCursor
101deallocate CustomersCursor
102
103
104declare @OrderTime datetime, @OrderPrice int, @CEmail_ varchar(40), @CID INT, @TextPrice int, @ImagePrice int, @HashtagPrice int, @TOTAL int;
105declare OrdersCursor CURSOR for select OrderTime, AdsTextPrice, AdsImagesPrice,AdsHashtagsPrice ,CEmail from TMP;
106open OrdersCursor
107fetch next from OrdersCursor
108into
109 @OrderTime,
110 @TextPrice,
111 @ImagePrice,
112 @HashtagPrice,
113 @CEmail_
114while @@FETCH_STATUS = 0 begin
115
116set @CID = (select customers_.ID from customers_ where customers_.CEmail = @CEmail_);
117(Select Count(*) from orders_ where OrderTime = @OrderTime )
118SELECT * FROM orders_;
119set @TOTAL = @TextPrice + @ImagePrice + @HashtagPrice;
120SELECT @TextPrice,@ImagePrice, @HashtagPrice
121
122SELECT @TOTAL;
123if 0 = (Select Count(*) from orders_ where OrderTime = @OrderTime)
124 insert into orders_( OrderTime, OrderPrice, cust) values(@OrderTime, @TOTAL,@CID )
125else if 0 <> (Select Count(*) from orders_ where OrderTime = @OrderTime )
126 update orders_ set OrderPrice = OrderPrice + @TOTAL where orders_.cust = @CID and orders_.OrderTime = @OrderTime;
127
128fetch next from OrdersCursor
129into
130 @OrderTime,
131 @TextPrice,
132
133 @ImagePrice,
134 @HashtagPrice,
135 @CEmail_
136end
137close OrdersCursor
138deallocate OrdersCursor
139select * from orders_
140
141INSERT INTO ads_ select AdName, AdPublicationDate,AdsTextPrice,AdsHashtagsPrice,AdsImagesPrice, (Select OrderList.AdCustomerID from OrderList where OrderList.OrderTime = TMP.OrderTime) from TMP;
142select * from ads_;