-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathMainSQL.sql
More file actions
1708 lines (1563 loc) · 52.4 KB
/
MainSQL.sql
File metadata and controls
1708 lines (1563 loc) · 52.4 KB
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
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
-- Script Reset User and Permission
DECLARE @sql NVARCHAR(MAX)=''
SELECT @sql+='DROP LOGIN '+QUOTENAME(name)+';'
FROM sys.sql_logins
WHERE name NOT LIKE '##%##' AND name <> 'sa' AND name <> 'NT AUTHORITY\\SYSTEM' AND name <> 'NT AUTHORITY\\NETWORK SERVICE' AND name <> 'NT AUTHORITY\\LOCAL SERVICE'
PRINT @sql -- Chạy câu lệnh này trước để kiểm tra xem nó xóa được user nào không
EXEC sp_executesql @sql
GO
-- Script Reset Database
USE master;
GO
IF EXISTS(SELECT * FROM sys.databases WHERE name = 'HEQUANTRICOSODULIEU')
BEGIN
ALTER DATABASE HEQUANTRICOSODULIEU SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE HEQUANTRICOSODULIEU;
END
CREATE DATABASE HEQUANTRICOSODULIEU;
GO
USE HEQUANTRICOSODULIEU;
GO
-- CREATE PERMISSION
CREATE OR ALTER PROCEDURE proc_permission (@user nvarchar (20), @pass nvarchar(20))
AS
BEGIN
DECLARE @state nvarchar (100), @role INT
SELECT @role = e.authorID
FROM ACCOUNT a
INNER JOIN EMPLOYEE e ON a.employeeID = e.employeeID
WHERE a.employeeID = @user AND a.emp_password = @pass
PRINT @role
BEGIN TRY
IF (@role = 1)
BEGIN
BEGIN TRY
BEGIN TRANSACTION;
-- -- grant sysadmin permission to new user
set @state = 'grant exec, control to [' + @user + ']'
exec (@state)
exec master ..sp_addsrvrolemember @user, N'sysadmin'
print N'Đã gán quyền của admin'
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
THROW;
END CATCH
END
ELSE IF (@role = 2)
BEGIN
BEGIN TRY
BEGIN TRANSACTION;
-----------cấp quyền vào các bảng
SET @state = 'GRANT SELECT, UPDATE, DELETE, INSERT TO [' + @user + ']'
EXEC (@state)
SET @state = 'GRANT EXEC TO [' + @user + ']'
EXEC (@state)
--cấm quyền them xoa account
SET @state = 'DENY INSERT, DELETE ON OBJECT::view_Account TO [' + @user + ']'
EXEC (@state)
----------- cấm quyền vào nhân viên
SET @state = 'DENY SELECT, UPDATE, INSERT, DELETE ON OBJECT::EMPLOYEE TO [' + @user + ']'
EXEC (@state)
--cấm quyền xem nhân viên
SET @state = 'DENY SELECT, UPDATE, INSERT, DELETE ON OBJECT::view_Employee TO [' + @user + ']'
EXEC (@state)
-- Cấm quyền thêm nhân viên
SET @state = 'DENY EXEC ON OBJECT::PROD_InsertEmployee TO [' + @user + ']'
EXEC (@state)
-- Cấm quyền sửa nhân viên
SET @state = 'DENY EXEC ON OBJECT::PROD_UpdateEmployee TO [' + @user + ']'
EXEC (@state)
-- Cấm quyền xóa nhân viên
SET @state = 'DENY EXEC ON OBJECT::PROD_DeleteEmployee TO [' + @user + ']'
EXEC (@state)
-------------Cam quyen tu them Vourcher
SET @state = 'DENY EXEC ON OBJECT::InsertVoucher TO [' + @user + ']'
EXEC (@state)
-- Cấm quyền xóa khach hang
SET @state = 'DENY EXEC ON OBJECT::Delete_Customer TO [' + @user + ']'
EXEC (@state)
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
THROW;
END CATCH
END
END TRY
BEGIN CATCH
PRINT ERROR_MESSAGE();
END CATCH
END;
GO
CREATE OR ALTER PROCEDURE proc_createUser
@user NVARCHAR(30),
@pass NVARCHAR(30)
AS
BEGIN
DECLARE @createLogin NVARCHAR(100)
DECLARE @createUser NVARCHAR(100)
set @createLogin = 'Create Login [' + @user + '] with password = ''' + @pass + ''''
set @createUser = 'Create User [' + @user + '] For Login [' + @user + ']'
print @createLogin
print @createUser
exec (@createLogin)
exec (@createUser)
exec proc_permission @user, @pass
end
go
create or ALTER PROCEDURE proc_deleteUser (@user nvarchar(20))
as
begin
declare @state1 nvarchar(200), @state2 nvarchar(200)
begin transaction deleteUser
begin try
SET @state1 = 'DROP LOGIN ' + QUOTENAME(@user)
SET @state2 = 'DROP USER ' + QUOTENAME(@user)
exec (@state1)
exec (@state2)
commit transaction deleteUser
end try
begin catch
print (error_message())
rollback transaction deleteUser
end catch
end
go
CREATE OR ALTER PROCEDURE proc_updateUser
@user varchar(20),
@newPass varchar(30),
@oldPass varchar(30)
AS
BEGIN
DECLARE @state nvarchar(max)
SET @state = 'ALTER LOGIN [' + @user + '] WITH PASSWORD = ''' + @newPass + ''' OLD_PASSWORD = ''' + @oldPass + ''''
EXEC (@state)
END
GO
-- CREATE DATATABLE
CREATE TABLE AUTHORIZATION_USER(
authorID INT PRIMARY KEY NOT NULL,
authorName VARCHAR(255) NOT NULL);
GO
CREATE TABLE BRAND(
brandID VARCHAR(10) PRIMARY KEY NOT NULL,
brandName VARCHAR(255) NOT NULL);
GO
CREATE TABLE PRODUCT_TYPE(
typeID VARCHAR(10) PRIMARY KEY NOT NULL,
typeName VARCHAR(255) NOT NULL);
GO
CREATE TABLE PRODUCT(
productID VARCHAR(10) PRIMARY KEY NOT NULL,
productName VARCHAR(255) NOT NULL,
productImageURL varbinary(max),
quantity INT NOT NULL,
CHECK(quantity >= 0));
GO
CREATE TABLE PRODUCT_DETAIL(
productID VARCHAR(10) NOT NULL PRIMARY KEY REFERENCES PRODUCT(productID),
typeID VARCHAR(10) NOT NULL REFERENCES PRODUCT_TYPE(typeID),
brandID VARCHAR(10) NOT NULL REFERENCES BRAND(brandID),
importPrice FLOAT NOT NULL,
sellPrice FLOAT NOT NULL,
Check(importPrice >=0),
Check(sellPrice >=0),
descript VARCHAR(1000));
GO
CREATE TABLE EMPLOYEE(
employeeID INT PRIMARY KEY NOT NULL,
fullName VARCHAR(50) NOT NULL,
sex VARCHAR(6) NOT NULL,
formatName VARCHAR(10) NOT NULL,
wage FLOAT NOT NULL,
employeeImage VARCHAR(255),
phoneNumber VARCHAR(10) UNIQUE NOT NULL,
Em_address VARCHAR(255),
citizenID VARCHAR(12) UNIQUE NOT NULL,
commissionRate FLOAT NOT NULL,
dateOfBirth DATE NOT NULL,
age INT,
statusJob VARCHAR(255),
authorID INT NOT NULL REFERENCES AUTHORIZATION_USER(authorID),
CHECK(len(citizenID) =12), --CCCD phải có đúng 12 chữ số
CHECK(len(phoneNumber) = 10)); --Số điện thoại phải có đúng 10 chữ số
GO
CREATE TABLE COMMISSION_DETAIL(
comAnaID INT NOT NULL PRIMARY KEY,
brandID VARCHAR(10) NOT NULL REFERENCES BRAND(brandID),
minCommission INT)
GO
CREATE TABLE ACCOUNT(
employeeID INT PRIMARY KEY NOT NULL REFERENCES EMPLOYEE(employeeID),
emp_password VARCHAR(255) NOT NULL,
CHECK (len(emp_password)>=6)); --PASSWORD phải có ít nhất 06 ký tự
GO
CREATE TABLE WORK_TIME(
employeeID INT NOT NULL REFERENCES EMPLOYEE(employeeID),
checkIn DATETIME NOT NULL,
checkOut DATETIME,
PRIMARY KEY (employeeID, checkIn));
GO
CREATE TABLE CUSTOMER(
phoneNumber VARCHAR(10) NOT NULL PRIMARY KEY,
fullName VARCHAR(50) NOT NULL,
cus_address VARCHAR(255));
GO
CREATE TABLE VOUCHER_STATUS(
voucherStatusID INT PRIMARY KEY,
voucherStatusName VARCHAR(255));
GO
CREATE TABLE VOUCHER(
voucherID VARCHAR(15) PRIMARY KEY NOT NULL,
voucherName VARCHAR(255) NOT NULL,
percentReduction FLOAT NOT NULL,
voucherStatusID INT REFERENCES VOUCHER_STATUS(voucherStatusID),
expiryDate DATETIME,
limitNumber INT,
numberUsed INT);
GO
CREATE TABLE BILL(
billID VARCHAR(20) PRIMARY KEY NOT NULL,
employeeID INT REFERENCES EMPLOYEE(employeeID) NOT NULL,
phoneNumber VARCHAR(10) REFERENCES CUSTOMER(phoneNumber),
billExportTime DATETIME)
GO
CREATE TABLE VOUCHER_APPLY(
billID VARCHAR(20) NOT NULL PRIMARY KEY REFERENCES BILL(billID),
voucherID VARCHAR(15) REFERENCES VOUCHER(voucherID));
GO
CREATE TABLE BILL_DETAIL(
billID VARCHAR(20) NOT NULL REFERENCES BILL(billID),
productID VARCHAR(10) NOT NULL REFERENCES PRODUCT(productID),
number INT NOT NULL,
PRIMARY KEY(billID, productID));
GO
CREATE TABLE IMPORT(
importID VARCHAR(20) PRIMARY KEY NOT NULL,
employeeID INT REFERENCES EMPLOYEE(employeeID) NOT NULL,
importDate DATETIME NOT NULL);
GO
CREATE TABLE IMPORT_DETAIL(
importID VARCHAR(20) NOT NULL REFERENCES IMPORT(importID),
productID VARCHAR(10) NOT NULL REFERENCES PRODUCT(productID),
PRIMARY KEY (importID, productID),
numberOfImport INT NOT NULL,
descript VARCHAR(1000));
GO
CREATE TABLE WARRANTY_STATUS(
warr_StatusID INT NOT NULL PRIMARY KEY,
statusName VARCHAR(100) NOT NULL);
GO
CREATE TABLE WARRANTY_CARD(
warrantyID VARCHAR(10) NOT NULL PRIMARY KEY,
productID VARCHAR(10) NOT NULL REFERENCES PRODUCT(productID),
billID VARCHAR(20) NOT NULL REFERENCES BILL(billID),
quantity INT NOT NULL,
CHECK (quantity >0),
warr_StatusID INT NOT NULL REFERENCES WARRANTY_STATUS(warr_StatusID),
descript VARCHAR(1000));
GO
--TRIGGER FINAL
CREATE TRIGGER CHECK_WARRANTY ON WARRANTY_CARD
FOR INSERT, UPDATE
AS
BEGIN
DECLARE @billQuantity INT, @warrantyQuantity INT
SELECT @billQuantity = number FROM BILL_DETAIL WHERE productID = (SELECT productID FROM inserted)
SELECT @warrantyQuantity = quantity FROM inserted
IF(@billQuantity < @warrantyQuantity)
BEGIN
RAISERROR('Số lượng nhập vào không hợp lệ!',6,1)
ROLLBACK TRANSACTION
END
END
GO
CREATE TRIGGER AUTO_CREATE_ACCOUNT ON EMPLOYEE
FOR INSERT
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
BEGIN TRANSACTION
DECLARE @employeeID INT, @password VARCHAR(255) ,@employeeID_str VARCHAR(20)
SELECT @employeeID = employeeID FROM inserted
SET @employeeID_str = CAST(@employeeID AS VARCHAR(20))
--SET @password = SUBSTRING(CAST(NEWID() AS VARCHAR(36)), 1, 6)
SET @password = 'admin123'
INSERT INTO Account (employeeID, emp_Password)
VALUES (@employeeID, @password)
EXEC proc_createUser @employeeID_str, @password
COMMIT TRANSACTION
PRINT('Thêm mới nhân viên và tạo tài khoản thành công!')
END TRY
BEGIN CATCH
PRINT('Thêm mới nhân viên và tạo tài khoản thất bại! Lỗi: ' + ERROR_MESSAGE())
ROLLBACK TRANSACTION
END CATCH
END;
GO
--OLD
--CREATE TRIGGER AUTO_CREATE_ACCOUNT ON EMPLOYEE
--FOR INSERT
--AS
--BEGIN
-- DECLARE @employeeID INT, @employeeName VARCHAR(255), @password VARCHAR(255)
-- SELECT @employeeID = employeeID FROM inserted
-- SET @password = SUBSTRING(CAST(NEWID() AS VARCHAR(36)), 1, 6)
-- INSERT INTO Account (employeeID, emp_Password)
-- VALUES (@employeeID, @password)
-- Print('Tao tai khoan nhan vien thanh cong!');
--END;
--GO
--OLD
--CREATE TRIGGER AUTO_DELETE_ACCOUNT
--ON Employee
--AFTER UPDATE
--AS
--BEGIN
-- IF UPDATE(statusJob) AND EXISTS(SELECT 1 FROM inserted WHERE statusJob = 'Non-Active') -- kiểm tra xem cột statusJob có được cập nhật hay không
-- BEGIN
-- DELETE FROM ACCOUNT WHERE employeeID = (SELECT employeeID FROM inserted)
-- END
--END;
--GO
CREATE TRIGGER AUTO_DELETE_ACCOUNT
ON Employee
AFTER UPDATE
AS
BEGIN
IF UPDATE(statusJob) AND EXISTS(SELECT 1 FROM inserted WHERE statusJob = 'Non-Active') -- kiểm tra xem cột statusJob có được cập nhật hay không
BEGIN
DECLARE @employeeID INT
SELECT @employeeID = employeeID FROM inserted
DECLARE @employeeID_str VARCHAR(20)
SET @employeeID_str = CAST(@employeeID AS VARCHAR(20))
BEGIN TRANSACTION
EXEC proc_deleteUser @user = @employeeID_str
DELETE FROM Account WHERE employeeID = @employeeID
COMMIT TRANSACTION
END
END;
GO
--CREATE TRIGGER AUTO_CREATE_ACCOUNT_CONTINUE_WORK
--ON EMPLOYEE
--AFTER UPDATE
--AS
--BEGIN
--IF ((SELECT statusJob FROM inserted) = 'Active')
--BEGIN
-- DECLARE @employeeID INT, @employeeName VARCHAR(255), @password VARCHAR(255)
-- SELECT @employeeID = employeeID FROM inserted
-- -- Kiểm tra xem đã có bản ghi nào trong bảng ACCOUNT có cùng employeeID chưa
-- IF NOT EXISTS (SELECT * FROM ACCOUNT WHERE employeeID = @employeeID)
-- BEGIN
-- SET @password = SUBSTRING(CAST(NEWID() AS VARCHAR(36)), 1, 6)
-- INSERT INTO Account (employeeID, emp_Password)
-- VALUES (@employeeID, @password)
-- END
-- ELSE
-- BEGIN
-- -- Nếu đã có bản ghi thì cập nhật lại mật khẩu
-- SET @password = SUBSTRING(CAST(NEWID() AS VARCHAR(36)), 1, 6)
-- UPDATE ACCOUNT SET emp_Password = @password WHERE employeeID = @employeeID
-- END
-- END
--END;
--GO
CREATE TRIGGER CHECK_PRICE
ON PRODUCT_DETAIL
FOR INSERT, UPDATE
AS
BEGIN
DECLARE @IP FLOAT, @SP FLOAT
SELECT @IP = importPrice , @SP = sellPrice FROM inserted
IF(@IP > @SP)
BEGIN
Print('Gia ban nhap vao lon hon gia nhap! Xin vui long kiem tra lai!');
ROLLBACK TRANSACTION
END
END;
go
CREATE TRIGGER AUTO_CHECK_VOUCHER
ON VOUCHER_APPLY
AFTER INSERT, UPDATE
AS
BEGIN
DECLARE @voucherID VARCHAR(15),@limitNumber INT, @numberUsed INT, @expiryDate DateTime,@status VARCHAR(15)
SELECT @voucherID = voucherID FROM inserted
SELECT @expiryDate = expiryDate, @status = voucherStatusID, @limitNumber = limitNumber, @numberUsed = numberUsed FROM inserted i INNER JOIN VOUCHER v ON i.voucherID = v.voucherID
IF((@expiryDate < GETDATE()))
BEGIN
UPDATE VOUCHER
SET voucherStatusID = 2
WHERE voucherID = @voucherID
SELECT @status = 2
END
IF((@limitNumber < @numberUsed + 1))
BEGIN
UPDATE VOUCHER
SET voucherStatusID = 2
WHERE voucherID = @voucherID
SELECT @status = 2
END
IF(@status = 2)
BEGIN
Print('Voucher da het han! Xin vui long thu lai sau!')
ROLLBACK TRANSACTION
END
ELSE IF(@status = 1)
BEGIN
Print('Voucher du dieu kien su dung!')
Print('Da ap dung Voucher thanh cong!')
END
END;
GO
CREATE TRIGGER TRG_AUTO_SET_STATUSJOB
ON EMPLOYEE
AFTER INSERT
AS
BEGIN
DECLARE @active VARCHAR(255) = 'Active';
UPDATE EMPLOYEE
SET statusJob = @active
WHERE employeeID IN (SELECT employeeID FROM inserted) AND statusJob IS NULL;
END
GO
CREATE TRIGGER CHECK_MANAGER
ON IMPORT
FOR INSERT
AS
BEGIN
IF((SELECT e.authorID -- Chọn phân quyền của employee
FROM inserted i INNER JOIN EMPLOYEE e ON i.employeeID = e.employeeID) --Kết bảng vừa nhập và bảng Employee
!= 1) --Vì cấp bậc quản lý có cấp author ID là 1
BEGIN
PRINT('Cap bac nhan vien khong du de thuc hien chuc nang, xin thu lai sau!')
ROLLBACK;
END
ELSE
BEGIN
PRINT('Nhap hang thanh cong!')
END
END;
GO
CREATE TRIGGER CHECK_CITIZENID
ON EMPLOYEE
FOR INSERT, UPDATE
AS
BEGIN
DECLARE @maTinh VARCHAR(3), @maGioiTinh VARCHAR(1), @maNamSinh VARCHAR(2)
SELECT @maTinh = SUBSTRING(citizenID, 1, 3),
@maGioiTinh = SUBSTRING(citizenID, 4, 1),
@maNamSinh = SUBSTRING(citizenID, 5,6)
FROM inserted
IF ( ( Convert(INT ,@maTinh ) >= 1 ) AND ( CONVERT(INT, @maTinh) <=96) ) -- Kiểm tra mã tỉnh có đúng định dạng chưa
BEGIN
-- Kiểm tra giới tính trên căn cước với hệ thống
IF ( (Convert(INT ,@maGioiTinh) = 0) OR (Convert(INT ,@maGioiTinh) = 2) OR (Convert(INT ,@maGioiTinh) = 4) OR (Convert(INT ,@maGioiTinh) = 6) OR (Convert(INT ,@maGioiTinh) = 8) )
BEGIN
IF( (SELECT sex FROM inserted) = 'Female')
BEGIN
PRINT('Gioi Tinh Khong Khop Voi Can Cuoc Cong Dan. Xin Vui Long Kiem Tra Lai!')
ROLLBACK TRANSACTION
END
END
ELSE
BEGIN
IF( (SELECT sex FROM inserted) = 'Male' )
BEGIN
PRINT('Gioi Tinh Khong Khop Voi Can Cuoc Cong Dan. Xin Vui Long Kiem Tra Lai!')
ROLLBACK TRANSACTION
END
END
DECLARE @yearOBirth VARCHAR(2), @currentYear INT
SELECT @currentYear = YEAR(GETDATE()) - 2000
IF ( (SELECT YEAR(dateOfBirth) FROM inserted) > 1999 )
BEGIN
IF(CONVERT(INT,@maNamSinh) > @currentYear)
BEGIN
PRINT('So Can Cuoc Khong Khop Voi Thoi Gian Thuc Tai! Vui Long Kiem Tra Lai!')
ROLLBACK TRANSACTION
END
END
SELECT @yearOBirth = RIGHT(CONVERT(VARCHAR(4), dateOfBirth), 2)FROM inserted
IF(@maNamSinh != @yearOBirth) --Kiểm tra năm sinh trên CCCD với hệ thống
BEGIN
PRINT('Ma Nam Sinh Khong Khop Voi Thong Tin Da Nhap. Xin Vui Long Kiem Tra Lai!')
ROLLBACK TRANSACTION
END
END
ELSE
BEGIN
--PRINT('fgsdg')
PRINT(CONVERT(VARCHAR,@maTinh) + ' ' + CONVERT(VARCHAR,@maGioiTinh) + ' '+ CONVERT(VARCHAR,@maNamSinh) )
--PRINT('Can Cuoc Cong Dan Khong Hop Le! Tao tai khoan that bai!')
ROLLBACK TRANSACTION
END
END
go
CREATE TRIGGER BUY_PRODUCT
ON BILL_DETAIL
AFTER INSERT, UPDATE
--làm trc về sau dính nhiều thứ lắm ,
AS
BEGIN
DECLARE @buyQuantity INT, @oldBQuantity INT
SELECT @buyQuantity = number
FROM inserted
SET @oldBQuantity = 0
IF EXISTS (SELECT 1 FROM deleted)
BEGIN
SELECT @oldBQuantity = number
FROM deleted
END
UPDATE PRODUCT
SET quantity = quantity - (@buyQuantity-@oldBQuantity)
WHERE productID = (SELECT productID FROM inserted)
END
GO
CREATE TRIGGER IMPORT_PRODUCT
ON IMPORT_DETAIL
AFTER INSERT, UPDATE
AS
BEGIN
DECLARE @iQuan INT, @oldIQ INT
SELECT @iQuan = numberOfImport FROM inserted
SET @oldIQ = 0
IF EXISTS (SELECT 1 FROM deleted)
BEGIN
SELECT @oldIQ = numberOfImport FROM deleted
END
UPDATE PRODUCT
SET quantity = quantity + (@iQuan - @oldIQ)
WHERE productID = (SELECT productID FROM inserted)
END
GO
-- VIEW FINAL
CREATE VIEW VIEW_VOUCHER AS
SELECT voucherID , vc.voucherName as [Name Voucher], percentReduction as [Percent Reduction ] ,vcs.voucherStatusName as [Name of status] , expiryDate , limitNumber , numberUsed
FROM VOUCHER vc INNER JOIN VOUCHER_STATUS vcs
ON vc.voucherStatusID = vcs.voucherStatusID;
GO
CREATE VIEW VIEW_CUSTOMER AS
SELECT fullName as [Full Name] , phoneNumber as[Number Phone], cus_address as Address
FROM CUSTOMER;
GO
CREATE VIEW VIEW_EMPLOYEE
AS
SELECT
e.employeeID AS EmloyeeID,
a.emp_password AS Password,
e.fullName AS FullName,
e.sex AS Sex,
e.formatName AS FormatName,
e.wage AS Wage,
e.employeeImage AS EmployeeImage,
e.phoneNumber AS PhoneNumber,
e.Em_address AS Address,
e.citizenID AS CitizenID,
e.commissionRate AS CommissionRate,
e.dateOfBirth AS DateOfBirth,
e.age AS Age,
e.statusJob AS StatusJob,
u.authorName AS AuthorName
FROM EMPLOYEE e
INNER JOIN AUTHORIZATION_USER u ON e.authorID = u.authorID
INNER JOIN ACCOUNT a ON e.employeeID = a.employeeID
GO
CREATE VIEW COMPLETED_BILL_DETAIL AS
SELECT billID, pd.productID, number, sellPrice*number as totalMoney
FROM BILL_DETAIL bd INNER JOIN PRODUCT_DETAIL pd
ON bd.productID = pd.productID;
GO
CREATE VIEW BILL_TOTAL_MONEY AS
SELECT b.billID, b.employeeID, b.phoneNumber, b.billExportTime, SUM(cbd.totalMoney) as totalPay
FROM BILL b INNER JOIN COMPLETED_BILL_DETAIL cbd
ON b.billID = cbd.billID
GROUP BY b.billID, b.employeeID, b.phoneNumber, b.billExportTime;
GO
CREATE VIEW COMPLETED_BILL
AS
SELECT c.billID, employeeID, phoneNumber, billExportTime, totalPay,
totalPay - (
CASE
WHEN EXISTS (
SELECT percentReduction
FROM VOUCHER v
INNER JOIN VOUCHER_APPLY va ON v.voucherID = va.voucherID
WHERE va.billID = c.billID
)
THEN (
SELECT percentReduction
FROM VOUCHER v
INNER JOIN VOUCHER_APPLY va ON v.voucherID = va.voucherID
WHERE va.billID = c.billID
)/100.0 * totalPay
ELSE 0
END
) as Payment
FROM BILL_TOTAL_MONEY c
GO
CREATE VIEW VIEW_PRODUCT
AS
SELECT p.productID, productName, productImageURL, quantity, typeName, brandName, importPrice, sellPrice, descript
FROM PRODUCT p
FULL OUTER JOIN (SELECT pde.productID, typeName, brandName,importPrice,sellPrice,descript FROM PRODUCT_DETAIL pde, PRODUCT_TYPE pt, BRAND b WHERE pde.brandID = b.brandID AND pde.typeID = pt.typeID ) pd
ON p.productID = pd.productID
GO
CREATE VIEW VIEW_WARRANTY AS
SELECT w.warrantyID, w.productID, productName, w.billID, w.quantity, warr_StatusID, descript
FROM WARRANTY_CARD w INNER JOIN PRODUCT p ON w.productID = p.productID
INNER JOIN BILL b ON w.billID = b.billID
GO
--INSERT INTO WARRANTY_CARD(warrantyID, warr_StatusID,productID,billID,quantity) VALUES ('1',1,'1','1',1);
GO
/*CREATE VIEW VIEW_WARRENTY AS
SELECT wr.productID, wr.warr_StatusID, wr.descript
FROM WARRANTY_CARD wr INNER JOIN BILL_DETAIL bld
ON wr.productID = bld.productID
GROUP BY wr.productID, wr.warr_StatusID, wr.descript;
GO*/
--Func + Proc
--FUNCTION FINAL
CREATE FUNCTION FUNC_GetMaxEmployeeId()
RETURNS INT
AS
BEGIN
DECLARE @maxId INT
SELECT @maxId = MAX(employeeID) FROM EMPLOYEE
RETURN @maxId
END
GO
CREATE FUNCTION FUNC_CheckAuthorExists(@AuthorName VARCHAR(50))
RETURNS INT
AS
BEGIN
DECLARE @AuthorId INT
SELECT TOP 1 @AuthorId = authorID FROM AUTHORIZATION_USER WHERE authorName = @AuthorName
IF @AuthorId IS NULL
SELECT TOP 1 @AuthorId = authorID FROM AUTHORIZATION_USER
RETURN @AuthorId
END
GO
CREATE FUNCTION FUNC_CHECK_EMPLOYEE_VALUE(
@Sex nvarchar(max),
@PhoneNumber nvarchar(max),
@CitizenID nvarchar(max),
@Wage float,
@CommissionRate float
)
RETURNS nvarchar(max)
AS
BEGIN
DECLARE @result nvarchar(max) = ''
IF @Sex NOT IN ('Male', 'Female')
BEGIN
SET @result = 'Sex must be "Male" or "Female". '
END
IF LEN(@PhoneNumber) <> 10 OR @PhoneNumber NOT LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
BEGIN
SET @result = @result + 'PhoneNumber must have 10 digits. '
END
IF @CitizenID IS NULL OR LEN(@CitizenID) <> 12 OR @CitizenID NOT LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
BEGIN
SET @result = @result + 'CitizenID must have 12 digits. '
END
--ELSE IF EXISTS (SELECT 1 FROM EMPLOYEE WHERE citizenID = @CitizenID)
--BEGIN
-- SET @result = @result + 'CitizenID is already in use. '
--END
IF @Wage <= 0
BEGIN
SET @result = @result + 'Wage must be greater than 0. '
END
IF @CommissionRate < 0 OR @CommissionRate > 1
BEGIN
SET @result = @result + 'CommissionRate must be between 0 and 1. '
END
RETURN @result
END
GO
CREATE FUNCTION FUNC_TOP5_PRODUCT(@Daystart DATE, @Dayend DATE)
RETURNS TABLE
AS
RETURN
SELECT TOP 5 pd.productID , p.productName , SUM(bd.number) AS totalSold
FROM BILL_DETAIL bd
INNER JOIN PRODUCT_DETAIL pd ON bd.productID = pd.productID
INNER JOIN BILL b ON bd.billID = b.billID
INNER JOIN PRODUCT p ON p.productID = pd.productID
WHERE b.billExportTime BETWEEN @Daystart AND @Dayend
GROUP BY pd.productID ,p.productName
ORDER BY totalSold DESC;
GO
CREATE FUNCTION FUNC_BOTTOM5_PRODUCT(@Daystart DATE, @Dayend DATE)
RETURNS TABLE
AS
RETURN
SELECT TOP 5 pd.productID, pd.brandID, pd.typeID, p.productName , SUM(bd.number) AS totalSold, b.billExportTime
FROM BILL_DETAIL bd
INNER JOIN PRODUCT_DETAIL pd ON bd.productID = pd.productID
INNER JOIN BILL b ON bd.billID = b.billID
INNER JOIN PRODUCT p ON p.productID = pd.productID
WHERE b.billExportTime BETWEEN @Daystart AND @Dayend
GROUP BY pd.productID, pd.brandID, pd.typeID, b.billExportTime,p.productName
ORDER BY totalSold ASC;
GO
CREATE FUNCTION FUNC_TOTAL_REVENUE(@Daystart DATE, @Dayend DATE)
RETURNS INT
AS
BEGIN
DECLARE @TotalValue INT
SELECT @TotalValue = SUM(Payment)
FROM COMPLETED_BILL
WHERE billExportTime BETWEEN @DayStart AND @DayEnd
RETURN @TotalValue
END
GO
CREATE FUNCTION FUNC_TOTAL_COMPLETE_BILL(@Daystart DATE, @Dayend DATE)
RETURNS INT
AS
BEGIN
DECLARE @TotalValue INT
SELECT @TotalValue = COUNT(CB.billID)
FROM COMPLETED_BILL CB
WHERE CB.billExportTime BETWEEN @DayStart AND @DayEnd
RETURN @TotalValue
END
GO
CREATE FUNCTION FUNC_REVENUE_SPLINE(@Daystart DATE, @Dayend DATE)
RETURNS @Revenue TABLE (
totalValue INT,
date DATE
)
AS
BEGIN
DECLARE @currentDate DATE = @Daystart;
WHILE @currentDate <= @Dayend
BEGIN
DECLARE @totalValue INT = (
SELECT SUM(Payment)
FROM COMPLETED_BILL
WHERE billExportTime = @currentDate
);
INSERT INTO @Revenue (totalValue, date) VALUES (@totalValue, @currentDate);
SET @currentDate = DATEADD(day, 1, @currentDate);
END
RETURN;
END
GO
CREATE FUNCTION FUNC_TOTAL_COMPLETE_BILL_COMPONENT(@Daystart DATE, @Dayend DATE)
RETURNS INT
AS
BEGIN
DECLARE @TotalValue INT
SELECT @TotalValue = COUNT(CBL.productID)
FROM COMPLETED_BILL CB
INNER JOIN COMPLETED_BILL_DETAIL CBL ON CB.billID = CBL.billID
WHERE CB.billExportTime BETWEEN @DayStart AND @DayEnd
RETURN @TotalValue
END
GO
CREATE PROCEDURE PROD_InsertEmployee
@FullName nvarchar(max),
@Sex nvarchar(max),
@FormatName nvarchar(max),
@Wage FLOAT ,
@EmployeeImage nvarchar(max),
@PhoneNumber nvarchar(max),
@Address nvarchar(max),
@CitizenID nvarchar(max),
@CommissionRate nvarchar(max),
@DateOfBirth date,
@Age int,
@AuthorName nvarchar(max)
AS
BEGIN
DECLARE @maxID INT = dbo.FUNC_GetMaxEmployeeId() + 1
DECLARE @AuthorID INT = dbo.FUNC_CheckAuthorExists(@AuthorName)
DECLARE @errorMsg nvarchar(max) = dbo.FUNC_CHECK_EMPLOYEE_VALUE(@Sex, @PhoneNumber, @CitizenID, @Wage , @CommissionRate)
IF @errorMsg <> ''
BEGIN
RAISERROR(@errorMsg, 16, 1)
ROLLBACK TRANSACTION
RETURN
END
INSERT INTO Employee(employeeID, FullName, Sex, FormatName, Wage, EmployeeImage, PhoneNumber, Em_address, CitizenID, CommissionRate, DateOfBirth, Age, AuthorID)
VALUES ( @maxID , @FullName, @Sex, @FormatName, @Wage, @EmployeeImage, @PhoneNumber, @Address, @CitizenID, @CommissionRate, @DateOfBirth, @Age, @AuthorID)
END
GO
CREATE PROCEDURE PROD_UpdateEmployee
@employeeID INT,
@FullName nvarchar(max),
@Sex nvarchar(max),
@FormatName nvarchar(max),
@Wage FLOAT ,
@EmployeeImage nvarchar(max),
@PhoneNumber nvarchar(max),
@Address nvarchar(max),
@CitizenID nvarchar(max),
@CommissionRate nvarchar(max),
@DateOfBirth date,
@Age int,
@AuthorName nvarchar(max)
AS
BEGIN
BEGIN TRANSACTION
DECLARE @AuthorID INT = dbo.FUNC_CheckAuthorExists(@AuthorName)
DECLARE @errorMsg nvarchar(max) = dbo.FUNC_CHECK_EMPLOYEE_VALUE(@Sex, @PhoneNumber, @CitizenID, @Wage , @CommissionRate)
IF @errorMsg <> ''
BEGIN
RAISERROR(@errorMsg, 16, 1)
ROLLBACK TRANSACTION
RETURN
END
UPDATE Employee
SET FullName = @FullName,
Sex = @Sex,
FormatName = @FormatName,
Wage = @Wage,
EmployeeImage = @EmployeeImage,
PhoneNumber = @PhoneNumber,
Em_address = @Address,
CitizenID = @CitizenID,
CommissionRate = @CommissionRate,
DateOfBirth = @DateOfBirth,
Age = @Age,
AuthorID = @AuthorID
WHERE employeeID = @employeeID
COMMIT TRANSACTION
END
GO
CREATE PROCEDURE PROD_DeleteEmployee
@employeeID INT
AS
BEGIN
UPDATE EMPLOYEE SET statusJob = 'Non-Active' WHERE employeeID = @employeeID;
END
GO
--UPDATE
Create View View_Account
AS
SELECT a.employeeID, fullName, emp_password
FROM ACCOUNT a, EMPLOYEE e
WHERE a.employeeID = e.employeeID
GO
--Procedure and Function
CREATE PROCEDURE insert_Customer
@phoneNumber varchar(10) = NULL,
@fullName varchar(50) = NULL,
@cus_address varchar(255) = NULL
AS
BEGIN
-- Kiểm tra xem SĐT đã tồn tại chưa
IF EXISTS (SELECT * FROM CUSTOMER WHERE phoneNumber = @phoneNumber)
BEGIN
RAISERROR (N'SĐT đã tồn tại', 16, 1);
RETURN
END
-- Set giá trị default khi tham số nhận giá trị null
IF (@phoneNumber is NULL)
BEGIN
RAISERROR (N'Số điện thoại chưa được nhập. Vui lòng nhập số điện thoại', 16, 1);
RETURN
END
IF(@fullName is NULL)
BEGIN
SET @fullName = N'Khách hàng chưa đăng ký tên'
END
IF(@cus_address is NULL)
BEGIN
SET @cus_address = N'Khách hàng chưa đưa địa chỉ'
END
--Thêm khách hàng mới
INSERT INTO CUSTOMER(phoneNumber, fullName, cus_address) VALUES (@phoneNumber, @fullName, @cus_address)
END
GO
--PROCEDURE TẠO VOUCHERID NGẪU NHIÊN (PROCEDURE HỖ TRỢ)----------------------
CREATE PROCEDURE GenerateNewVoucherID
@id VARCHAR(6) OUTPUT
AS
BEGIN
DECLARE @newID VARCHAR(6)
SET @newID = SUBSTRING(CAST(NEWID() AS VARCHAR(36)), 1, 6)
WHILE EXISTS(SELECT 1 FROM VOUCHER WHERE voucherID = @newID)
BEGIN
SET @newID = SUBSTRING(CAST(NEWID() AS VARCHAR(36)), 1, 6)
END
SET @id = @newID
END
GO
Insert Into VOUCHER_STATUS VALUES (1,'Active'),(2,'Non-Active')
GO
CREATE PROC InsertVoucher
@voucherID VARCHAR(15) = NULL,
@voucherName VARCHAR(255) = NULL,
@percent INT = NULL,
@statusVoucher VARCHAR(255) = NULL,
@expiryDate DATETIME = NULL,
@limitNumber INT = NULL,
@numberUsed INT = NULL
AS
BEGIN
DECLARE @statusID INT = 1
IF(@percent IS NULL)
BEGIN