-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathdjango.sql
252 lines (220 loc) · 12 KB
/
django.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
INSERT INTO `core_brand` VALUES (1,'Acer',NULL,'lrg_acer-228x228_RKEhTaO.png'),(2,'Apple',NULL,'Logo-228x228_gi51xp4.jpg'),(3,'Asus',NULL,'lrg_asus-228x228_IlxY1cV.png'),(4,'Dell',NULL,'dell_2016_logo-228x228_MGIPKsH.png'),(5,'Hp',NULL,'HP_logo_2012.svg-228x228_80ULYZz.png'),(7,'LG',NULL,'Lg_logo-3-228x228_GtcBjKX.png'),(8,'Philips',NULL,'Philips-228x228.png'),(9,'Msi',NULL,'msi-corporate_identity-logo-black-rgb-png-228x228_wvHg2Qi.png'),(10,'Lenovo',NULL,'lenovo-logo_Yz01uGa.png');
INSERT INTO `core_product` VALUES (8,'AN515-57-71VV','Laptop Gaming Acer Nitro 5 Eagle AN515-57-71VV i7-11800H/ 8GB/ 512GB/ RTX 3050 4GB/ Win 11',1187,'laptop-acer-nitro5-eagle-01-500x500_ya3hlFc.jpg','- CPU: Intel® Core™ i7-11800H (upto 4.60GHz, 24MB)\n\n- RAM: 8GB DDR4 khe rời 3200MHz (2 khe, tối đa 32GB)\n\n- Ổ cứng: 512GB PCIe NVMe SSD cắm sẵn (nâng cấp tối đa 2TB SSD PCIe Gen3, 8 Gb/s, NVMe và 2TB HDD 2.5-inch 5400 RPM)\n\n- VGA: NVIDIA® GeForce RTX™ 3050 4GB GDDR6\n\n- Màn hình: 15.6 inch FHD(1920 x 1080) IPS 144Hz slim bezel LCD, Acer ComfyView LED-backlit TFT LCD\n\n- Pin: 57.5 Wh, 4-cell\n\n- Cân nặng: 2.2 kg\n\n- Tính năng: Đèn nền bàn phím\n\n- Màu sắc: Shale Black\n\n- OS: Windows 11 Home',15,'2022-01-07',1),(9,'A715-42G-R1SB','Laptop Acer Aspire 7 A715-42G-R1SB R5-5500U/ 8GB/ 256GB/ GTX 1650 4GB/ 15.6 inch FHD/ Win 10',989,'acer-predator-triton-300-pt315-53-71dj-i7-600x600.jpg','- CPU: AMD Ryzen R5-5500U (8MB, 2.1GHz up to 4.00GHz)\n- RAM: 8GB(1x8GB) DDR4 3200MHz\n- Ổ cứng: 256GB PCIe NVMe SSD cắm sẵn (nâng cấp tối đa 1TB SSD)\n- VGA: NVIDIA GeForce GTX 1650 4GB GDDR6\n- Màn hình: 15.6 inch FHD(1920 x 1080) 144Hz SlimBezel, Acer ComfyView™ IPS LED LCD\n- Pin: 48 Wh\n- Cân nặng: 2.1kg\n- Màu sắc: Đen\n- Tính năng: Đèn nền bàn phím\n- OS: Windows 10 Home',18,'2022-01-07',1),(10,'GA503QC-HN074T','Laptop Asus ROG Zephyrus G15 GA503QC-HN074T R9-5900HS/ 16GB/ 512GB/ RTX 3050 4GB',1298,'asus-tuf-gaming-fx516pe-i7-hn005t-600x600.jpg','- CPU: AMD Ryzen 9-5900HS (3.0GHz up to 4.6GHz, 16MB)\n- RAM: 16GB(8GB + 8GB[On board]) DDR4 3200MHz (1x SO-DIMM slot)\n- Ổ cứng: 512GB M.2 NVMe™ PCIe® 3.0 SSD\n- VGA: NVIDIA® GeForce RTX™ 3050 4GB GDDR6\n- Màn hình: 15.6 inch FHD (1920 x 1080)-144Hz\n- Pin: 4-cell, 90WHrs Li-ion\n- Cân nặng: 1.9 KG\n- HĐH: Windows 10 Home',20,'2022-01-07',3),(11,'m15-R6-01NS','Laptop Gaming Dell Alienware m15 - R6 - 01NS i7-11800H/16GB/1TB/2K 240Hz/RTX 3060 6GB',2447,'dell-gaming-g15-5515-r5-p105f004cgr-291121-115616-600x600.jpg','- Vi xử lý: Intel Core i7 11800H, 8 nhân / 16 luồng\n- Màn hình: 15.6\" QHD (2560 x 1440) 240Hz chống chói\n- RAM: 16GB DDR4 bus 3200 MHz (Nâng cấp tối đa 64GB)\n- Card đồ họa: Nvidia RTX3060 6GB GDDR6\n- Lưu trữ: 1TB m.2 NVMe (Nâng cấp tối đa 2TB x 2)\n- Pin: 86Wh\n- Kết nối chính: 1 x USB-C 3.2 Gen 2 (Hỗ trợ xuất hình DisplayPort 1.4), 3 x USB-A 3.2 Gen 1, 1 x HDMI 2.1, 1 x RJ-45, 1 x jack 3.5mm\n- Cân nặng: 2.69kg\n- Hệ điều hành: Windows 10 Home ',5,'2022-01-07',4),(12,'Z11D000E5','Laptop Apple MacBook Pro M1 2020/16GB/256GB (Z11D000E5)',1638,'macbook-pro-m1-2020-silver-600x600.jpg','- CPU: Intel® Core™ i3-1115G4 (tối đa 4.10 GHz, 6MB)\n- RAM: 8GB(8GBx1)DDR4 3200MHz (2 Khe, tối đa 64GB)\n- Ổ cứng: 256GB M.2 NVMe PCIe SSD\n- VGA: Intel® UHD Graphics\n- Màn hình: 14 inch FHD (1920*1080), 60Hz 45%NTSC IPS-Level\n- Pin: 3 cell , 39Whr\n- Màu sắc: Gray\n- Tính năng: Đèn nền bàn phím\n- Cân nặng: 1.3 kg\n- OS: Windows 11 Home',10,'2022-01-07',2),(13,'Z15H','Laptop MacBook Pro 14 M1 Max 2021 10-core CPU/32GB/1TB SSD/32-core GPU (Z15H)',2168,'macbook-pro-14-m1-max-2021-10-core-cpu-32gb-1tb-ssd-32-core-gpu-021221-040129-600x600.jpg','- CPU: Intel® Core™ i3-1005G1 (1.20GHz upto 3.40GHz, 4MB)\n- RAM: 4GB DDR4 on board (1 onboard + 1 khe rời)\n- Ổ cứng: 256GB PCIe NVMe SSD\n- VGA: Intel® UHD Graphics\n- Màn hình: 15.6 inch FHD (1920 x 1080), high-brightness Acer ComfyView™ LED-backlit TFT LCD\n- Pin: 2-cell, 36.7 Wh\n- Cân nặng: 1.7 kg\n- OS: Windows 10 SL',2,'2022-01-07',2),(14,'10SCXK-093VN','LAPTOP GAMING MSI GL65 LEOPARD 10SCXK 093VN I7 10750H/ 1650 4GB/ 8GB/ 512GB/ 15.6”/ FHD/ 144HZ/ IPS/ WIN 10',1052,'msi-gaming-ge66-raider-11ug-i7-258vn-600x600.jpg','- CPU: Intel core i7-10750H (2.60GHz upto 5.00 GHz, 12MB)\n- RAM: 8GB DDR4 2666Mhz\n- Ổ cứng: 512GB NVMe PCIe SSD + 1 slot 2.5\"\n- VGA: NVIDIA® GeForce® GTX 1650 4GB GDDR6\n- Màn hình: 15.6 inch FHD (1920*1080), IPS-Level 144Hz, 45%NTSC Thin Bezel\n- Cân nặng: 2.3 kg\n- Tính năng: Đèn nền bàn phím\n- OS: Windows 10 Home',12,'2022-01-07',9),(15,'A315-56-37DV','Laptop Lenovo 3 A315-56-37DV i3 1005G1/ 4GB RAM/ 256GB SSD/ 15.6 inch FHD/ Win 10/ Đen',998,'lenovo-thinkbook-14s-g2-itl-i5-1135g7-8gb-512g-600x600.jpg','- CPU: Intel® Core™ i3-1005G1 (1.20GHz upto 3.40GHz, 4MB)\n- RAM: 4GB DDR4 on board (1 onboard + 1 khe rời)\n- Ổ cứng: 256GB PCIe NVMe SSD\n- VGA: Intel® UHD Graphics\n- Màn hình: 15.6 inch FHD (1920 x 1080), high-brightness Acer ComfyView™ LED-backlit TFT LCD\n- Pin: 2-cell, 36.7 Wh\n- Cân nặng: 1.7 kg\n- OS: Windows 10 SL',20,'2022-01-07',10);
DELIMITER $$
CREATE PROCEDURE add_to_cart(in quan Int,in CID INT,in PID int)
BEGIN
DECLARE pprice DOUBLE DEFAULT 0;
DECLARE pstock INT DEFAULT 0;
DECLARE carttotal DOUBLE DEFAULT 0;
DECLARE dquantity INT DEFAULT 0;
declare exit handler for sqlexception rollback;
start transaction;
select Price into pprice from core_product where ID=PID;
select Stock into pstock from core_product where ID=PID;
if exists (select * from core_cartdetails where CartID=CID and ProductID=PID) then
select quantity into dquantity from core_cartdetails where CartID=CID and ProductID=PID;
if dquantity < pstock then
update core_cartdetails set quantity=quantity+1 where CartID=CID and ProductID=PID;
end if;
else
if quan<pstock then
insert into core_cartdetails(quantity, CartID, ProductID) value (quan,CID,PID);
end if;
end if;
commit;
END$$
DELIMITER ;
-----------------------------------------------
DELIMITER $$
CREATE TRIGGER on_create_cartdetails
AFTER INSERT
ON core_cartdetails FOR EACH ROW
BEGIN
DECLARE pprice DOUBLE DEFAULT 0;
select Price into pprice from core_product where ID=NEW.ProductID;
UPDATE core_cart set Total=Total+(NEW.Quantity*pprice) WHERE CartID=NEW.CartID;
UPDATE core_cart set numofproducts=numofproducts+1 WHERE CartID=NEW.CartID;
END$$
DELIMITER ;
-- drop trigger on_create_cartdetails;
----------------------------------------
DELIMITER $$
CREATE TRIGGER on_update_cartdetails
AFTER UPDATE
ON core_cartdetails FOR EACH ROW
BEGIN
DECLARE pprice DOUBLE DEFAULT 0;
select Price into pprice from core_product where ID=OLD.ProductID;
UPDATE core_cart set Total=Total+(pprice*(NEW.quantity-OLD.quantity)) where CartID=OLD.CartID;
END$$
DELIMITER ;
-- drop trigger on_update_cartdetails;
-------------------------------------------------------
-----------------------------------------------
DELIMITER $$
CREATE TRIGGER on_delete_cartdetails
AFTER DELETE
ON core_cartdetails FOR EACH ROW
BEGIN
DECLARE pprice DOUBLE DEFAULT 0;
select Price into pprice from core_product where ID=OLD.ProductID;
UPDATE core_cart set Total=Total-(pprice*OLD.quantity) where CartID=OLD.CartID;
UPDATE core_cart set numofproducts=numofproducts-1 where CartID=OLD.CartID;
END$$
DELIMITER ;
-- drop trigger on_delete_cartdetails;
------------------------------------------------
DELIMITER $$
CREATE TRIGGER on_delete_product
BEFORE DELETE
ON core_product FOR EACH ROW
BEGIN
delete from core_orderdetails where ProductID=OLD.ID;
delete from core_cartdetails where ProductID=OLD.ID;
END$$
DELIMITER ;
-- drop trigger on_delete_product;
---------------------------------
DELIMITER $$
CREATE TRIGGER on_delete_orderdetails
AFTER DELETE
ON core_orderdetails FOR EACH ROW
BEGIN
DECLARE pprice DOUBLE DEFAULT 0;
select Price into pprice from core_product where ID=OLD.ProductID;
UPDATE core_orders set Total=Total-(pprice*OLD.Quantity) where OrderID=OLD.OrderID;
END$$
DELIMITER ;
-- drop trigger on_delete_orderdetails;
-----------------------------------
DELIMITER $$
CREATE TRIGGER on_create_orderdetails
AFTER INSERT
ON core_orderdetails FOR EACH ROW
BEGIN
UPDATE core_product set Stock=Stock-NEW.Quantity WHERE ID=NEW.ProductID;
END$$
DELIMITER ;
-- drop trigger on_create_orderdetails;
-- -------------------------------------------
show triggers;
------------------------------------------
CREATE
ALGORITHM = UNDEFINED
DEFINER = `root`@`localhost`
SQL SECURITY DEFINER
VIEW `hotproducts` AS
SELECT
`core_product`.`ID` AS `ID`,
`core_product`.`ProductCode` AS `ProductCode`,
`core_product`.`Name` AS `Name`,
`core_product`.`Price` AS `Price`,
`core_product`.`IMG` AS `IMG`,
`core_product`.`Description` AS `Description`,
`core_product`.`Stock` AS `Stock`,
`core_product`.`CreateDate` AS `CreateDate`,
`core_product`.`BrandName` AS `BrandName`
FROM
`core_product`
ORDER BY `core_product`.`Stock`
LIMIT 8;
------------------------------------------
CREATE
ALGORITHM = UNDEFINED
DEFINER = `root`@`localhost`
SQL SECURITY DEFINER
VIEW `instockproduct` AS
SELECT
`core_product`.`ID` AS `ID`,
`core_product`.`ProductCode` AS `ProductCode`,
`core_product`.`Name` AS `Name`,
`core_product`.`Price` AS `Price`,
`core_product`.`IMG` AS `IMG`,
`core_product`.`Description` AS `Description`,
`core_product`.`Stock` AS `Stock`,
`core_product`.`CreateDate` AS `CreateDate`,
`core_product`.`BrandName` AS `BrandName`
FROM
`core_product`
ORDER BY `core_product`.`Stock` DESC
LIMIT 8;
-----------------------------------------
CREATE
ALGORITHM = UNDEFINED
DEFINER = `root`@`localhost`
SQL SECURITY DEFINER
VIEW `newproduct` AS
SELECT
`core_product`.`ID` AS `ID`,
`core_product`.`ProductCode` AS `ProductCode`,
`core_product`.`Name` AS `Name`,
`core_product`.`Price` AS `Price`,
`core_product`.`IMG` AS `IMG`,
`core_product`.`Description` AS `Description`,
`core_product`.`Stock` AS `Stock`,
`core_product`.`CreateDate` AS `CreateDate`,
`core_product`.`BrandName` AS `BrandName`
FROM
`core_product`
ORDER BY `core_product`.`CreateDate` DESC
LIMIT 8;
---------------------------------------
DELIMITER $$
CREATE FUNCTION getnumberoffeedback() RETURNS int
DETERMINISTIC
BEGIN
DECLARE count integer default 0;
select count(*) into count from core_feedback;
RETURN count;
END $$
DELIMITER ;
---------------------------------------------------
DELIMITER $$
CREATE FUNCTION getnumberoforders() RETURNS int
DETERMINISTIC
BEGIN
DECLARE count integer default 0;
select count(*) into count from core_orders;
RETURN count;
END $$
DELIMITER ;
----------------------------------------------------
DELIMITER $$
CREATE FUNCTION `getnumberoforderspending`() RETURNS int
DETERMINISTIC
BEGIN
DECLARE count integer default 0;
select count(*) into count from core_orders where OrderStatus=0;
RETURN count;
END $$
DELIMITER ;
------------------------------------------------------
DELIMITER $$
CREATE FUNCTION `getnumberofproductoutofstock`() RETURNS int
DETERMINISTIC
BEGIN
DECLARE count integer default 0;
select count(*) into count from core_product where Stock=0;
RETURN count;
END $$
DELIMITER ;
--------------------------------------------------
DELIMITER $$
CREATE FUNCTION `getnumberofproducts`() RETURNS int
DETERMINISTIC
BEGIN
DECLARE count integer default 0;
select count(*) into count from core_product;
RETURN count;
END $$
DELIMITER ;
------------------------------------------------------
DELIMITER $$
CREATE FUNCTION `getnumberofuser`() RETURNS int
DETERMINISTIC
BEGIN
DECLARE count integer default 0;
select count(*) into count from auth_user;
RETURN count;
END $$
DELIMITER ;