· 5 years ago · Jun 12, 2020, 09:38 AM
1
2CREATE DATABASE [LaundryDatabase]
3USE [LaundryDatabase]
4GO
5
6CREATE TABLE [dbo].[tblCentral](
7 [CentralID] [bigint] NOT NULL,
8 [Address] [varchar](250) NULL,
9 [Name] [varchar](250) NULL,
10 [Email] [varchar](250) NULL,
11 [PhoneNumber] [varchar](20) NULL,
12 CONSTRAINT [PK_tblCentral] PRIMARY KEY CLUSTERED
13(
14 [CentralID] ASC
15)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
16) ON [PRIMARY]
17GO
18
19CREATE TABLE [dbo].[tblCustomer](
20 [CustomerID] [bigint] NOT NULL,
21 [PhoneNumber] [varchar](20) NULL,
22 [Email] [varchar](250) NULL,
23 [Name] [varchar](250) NULL,
24 CONSTRAINT [PK_tblCustomer] PRIMARY KEY CLUSTERED
25(
26 [CustomerID] ASC
27)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
28) ON [PRIMARY]
29GO
30
31CREATE TABLE [dbo].[tblDeliveryPoint](
32 [DeliveryPointID] [bigint] NOT NULL,
33 [Address] [varchar](250) NULL,
34 [Name] [varchar](250) NULL,
35 [Email] [varchar](250) NULL,
36 [PhoneNumber] [varchar](20) NULL,
37 CONSTRAINT [PK_tblDeliveryPoint] PRIMARY KEY CLUSTERED
38(
39 [DeliveryPointID] ASC
40)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
41) ON [PRIMARY]
42GO
43
44CREATE TABLE [dbo].[tblDriver](
45 [DriverID] [bigint] NOT NULL,
46 [Name] [varchar](100) NULL,
47 [PhoneNumber] [varchar](20) NULL,
48 [Email] [varchar](100) NULL,
49 CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED
50(
51 [DriverID] ASC
52)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
53) ON [PRIMARY]
54GO
55
56CREATE TABLE [dbo].[tblLaundryCategory](
57 [CategoryID] [bigint] NOT NULL,
58 [Name] [varchar](100) NULL,
59 CONSTRAINT [PK_tblLaundryCategory] PRIMARY KEY CLUSTERED
60(
61 [CategoryID] ASC
62)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
63) ON [PRIMARY]
64GO
65
66CREATE TABLE [dbo].[tblOrder](
67 [OrderID] [bigint] NOT NULL,
68 [Date] [datetime] NULL,
69 [CentralID] [bigint] NULL,
70 [Status] [int] NULL,
71 [DeliveryPointID] [bigint] NULL,
72 [CustomerID] [bigint] NULL,
73 [ReadableID] [varchar](5) NULL,
74 CONSTRAINT [PK_tblOrder] PRIMARY KEY CLUSTERED
75(
76 [OrderID] ASC
77)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
78) ON [PRIMARY]
79GO
80
81CREATE TABLE [dbo].[tblOrderContent](
82 [OrderID] [bigint] NULL,
83 [CategoryID] [bigint] NULL,
84 [Amount] [int] NULL
85) ON [PRIMARY]
86GO
87
88CREATE TABLE [dbo].[tblTransport](
89 [TransportID] [bigint] NOT NULL,
90 [DriverID] [bigint] NULL,
91 [Date] [datetime] NULL,
92 [Status] [int] NULL,
93 [isDone] [bit] NULL,
94 CONSTRAINT [PK_tblTransport] PRIMARY KEY CLUSTERED
95(
96 [TransportID] ASC
97)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
98) ON [PRIMARY]
99GO
100
101CREATE TABLE [dbo].[tblTransportContent](
102 [TransportID] [bigint] NULL,
103 [OrderID] [bigint] NULL
104) ON [PRIMARY]
105GO
106
107CREATE TABLE [dbo].[tblUser](
108 [Username] [varchar](100) NOT NULL,
109 [Password] [varchar](100) NULL,
110 [DeliveryPointID] [bigint] NULL,
111 [CentralID] [bigint] NULL,
112 [DriverID] [bigint] NULL,
113 [PermDeliveryPoint] [bit] NULL,
114 [PermCentral] [bit] NULL,
115 [PermDriver] [bit] NULL,
116 [PermAdmin] [bit] NULL,
117PRIMARY KEY CLUSTERED
118(
119 [Username] ASC
120)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
121) ON [PRIMARY]
122GO
123ALTER TABLE [dbo].[tblOrder] WITH CHECK ADD CONSTRAINT [FK_tblOrder_tblCentral] FOREIGN KEY([CentralID])
124REFERENCES [dbo].[tblCentral] ([CentralID])
125GO
126ALTER TABLE [dbo].[tblOrder] CHECK CONSTRAINT [FK_tblOrder_tblCentral]
127GO
128ALTER TABLE [dbo].[tblOrder] WITH CHECK ADD CONSTRAINT [FK_tblOrder_tblCustomer] FOREIGN KEY([CustomerID])
129REFERENCES [dbo].[tblCustomer] ([CustomerID])
130GO
131ALTER TABLE [dbo].[tblOrder] CHECK CONSTRAINT [FK_tblOrder_tblCustomer]
132GO
133ALTER TABLE [dbo].[tblOrder] WITH CHECK ADD CONSTRAINT [FK_tblOrder_tblDeliveryPoint] FOREIGN KEY([DeliveryPointID])
134REFERENCES [dbo].[tblDeliveryPoint] ([DeliveryPointID])
135GO
136ALTER TABLE [dbo].[tblOrder] CHECK CONSTRAINT [FK_tblOrder_tblDeliveryPoint]
137GO
138ALTER TABLE [dbo].[tblOrderContent] WITH CHECK ADD CONSTRAINT [FK_tblOrderContent_tblLaundryCategory] FOREIGN KEY([CategoryID])
139REFERENCES [dbo].[tblLaundryCategory] ([CategoryID])
140GO
141ALTER TABLE [dbo].[tblOrderContent] CHECK CONSTRAINT [FK_tblOrderContent_tblLaundryCategory]
142GO
143ALTER TABLE [dbo].[tblOrderContent] WITH CHECK ADD CONSTRAINT [FK_tblOrderContent_tblOrder] FOREIGN KEY([OrderID])
144REFERENCES [dbo].[tblOrder] ([OrderID])
145GO
146ALTER TABLE [dbo].[tblOrderContent] CHECK CONSTRAINT [FK_tblOrderContent_tblOrder]
147GO
148ALTER TABLE [dbo].[tblTransport] WITH CHECK ADD CONSTRAINT [FK_tblTransport_tblDriver] FOREIGN KEY([DriverID])
149REFERENCES [dbo].[tblDriver] ([DriverID])
150GO
151ALTER TABLE [dbo].[tblTransport] CHECK CONSTRAINT [FK_tblTransport_tblDriver]
152GO
153ALTER TABLE [dbo].[tblTransportContent] WITH CHECK ADD CONSTRAINT [FK_tblTransportContent_tblOrder] FOREIGN KEY([OrderID])
154REFERENCES [dbo].[tblOrder] ([OrderID])
155GO
156ALTER TABLE [dbo].[tblTransportContent] CHECK CONSTRAINT [FK_tblTransportContent_tblOrder]
157GO
158ALTER TABLE [dbo].[tblTransportContent] WITH CHECK ADD CONSTRAINT [FK_tblTransportContent_tblTransport] FOREIGN KEY([TransportID])
159REFERENCES [dbo].[tblTransport] ([TransportID])
160GO
161ALTER TABLE [dbo].[tblTransportContent] CHECK CONSTRAINT [FK_tblTransportContent_tblTransport]
162GO
163ALTER TABLE [dbo].[tblUser] WITH CHECK ADD CONSTRAINT [FK_tblUser_tblCentral] FOREIGN KEY([CentralID])
164REFERENCES [dbo].[tblCentral] ([CentralID])
165GO
166ALTER TABLE [dbo].[tblUser] CHECK CONSTRAINT [FK_tblUser_tblCentral]
167GO
168ALTER TABLE [dbo].[tblUser] WITH CHECK ADD CONSTRAINT [FK_tblUser_tblDeliveryPoint] FOREIGN KEY([DeliveryPointID])
169REFERENCES [dbo].[tblDeliveryPoint] ([DeliveryPointID])
170GO
171ALTER TABLE [dbo].[tblUser] CHECK CONSTRAINT [FK_tblUser_tblDeliveryPoint]
172GO
173ALTER TABLE [dbo].[tblUser] WITH CHECK ADD CONSTRAINT [FK_tblUser_tblDriver] FOREIGN KEY([DriverID])
174REFERENCES [dbo].[tblDriver] ([DriverID])
175GO
176ALTER TABLE [dbo].[tblUser] CHECK CONSTRAINT [FK_tblUser_tblDriver]
177GO
178
179CREATE PROCEDURE [dbo].[insertCategory](@CategoryID BIGINT, @Name VARCHAR(100))
180AS
181 BEGIN
182 IF NOT EXISTS (SELECT * FROM tblLaundryCategory WHERE CategoryID = @CategoryID OR Name = @Name)
183 INSERT INTO tblLaundryCategory (CategoryID, Name) VALUES (@CategoryID, @Name)
184 END
185GO
186
187SET ANSI_NULLS ON
188GO
189SET QUOTED_IDENTIFIER ON
190GO
191CREATE PROCEDURE [dbo].[SetStatus] @OrderID BIGINT
192AS
193DECLARE @Count INT
194SET @Count = (SELECT Status FROM tblOrder WHERE OrderID = @OrderID)
195
196IF @Count < 9
197 SET @Count = @Count + 1
198 UPDATE tblOrder SET Status = @Count WHERE OrderID = @OrderID
199 PRINT 'test'
200GO
201USE [master]
202GO
203ALTER DATABASE [LaundryDatabase] SET READ_WRITE
204GO