B321_SQL_CSharp_Project/B321_StoredProceduresScript.sql at main · IBickCoding/B321_SQL_CSharp_Project · GitHub

History

288 lines (249 loc) · 7.89 KB

Raw

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

Programmers: Ian Bickford, Shaun Poole, Roddey Sims

Course: CSCI/ISAT - B321 Database Driven Application Developement

Assignment: B321 Final Project

File Name: b321_StoredProcedures.sql

Version: December 10th, 2024

Purpose: The purpose of this script is to hold the Stored procedures with the data of our creation script.

The script will include procedures for when the user needs to login .

drop procedure if exists LoginAdvisee;

drop procedure if exists CreateScheduledAppointment;

drop procedure if exists CancelAppointment;

drop procedure if exists RescheduleAppointment;

drop procedure if exists GetAdvisees;

drop procedure if exists GetAdvisors;

drop procedure if exists GetModalities;

drop procedure if exists GetApptTypes;

drop procedure if exists GetLocations;

drop procedure if exists GetAvailability;

drop procedure if exists GetUserAppointments;

drop procedure if exists CheckForOverlappingAppointments;

drop procedure if exists CheckForOverlappingAppointmentsReschedule;

select AdviseeUsername,

AdviseePassword

from Advisees

create procedure LoginAdvisee

@Username NVARCHAR(50),

@Password NVARCHAR(50)

begin

select AdviseeID, AdviseeFName, AdviseeLName

from Advisees

where AdviseeUsername = @Username and AdviseePassword = @Password

end;

execute LoginAdvisee @Username = '', @Password = '';

/* This procedure handles the logic of making an appointment by taking the info that the user makes in the front end and inserts it in the database.

The appointment could then be removed/canceled or Reschedule the appointment.

create procedure CreateScheduledAppointment

@LocationID int,

@ModalityID int,

@AdvisorID int,

@AdviseeID int,

@AppointmentTypeID int,

@AppointmentDate date,

@AppointmentStartTime time,

@AppointmentDuration int = 30,

@AppointmentDescription varchar(300) = NULL

begin

insert into ScheduledAppointments (

LocationID,

ModalityID,

AdvisorID,

AdviseeID,

AppointmentTypeID,

AppointmentDate,

AppointmentStartTime,

AppointmentDuration,

AppointmentDescription

)

values (

@LocationID,

@ModalityID,

@AdvisorID,

@AdviseeID,

@AppointmentTypeID,

@AppointmentDate,

@AppointmentStartTime,

@AppointmentDuration,

@AppointmentDescription

);

declare @NewAppointmentID int = SCOPE_IDENTITY();

select 'Appointment created successfully' as ConfirmationMessage,

AppointmentID,

LocationID,

ModalityID,

AdvisorID,

AdviseeID,

AppointmentTypeID,

AppointmentDate,

AppointmentStartTime,

AppointmentDuration,

AppointmentDescription

from ScheduledAppointments

where AppointmentID = @NewAppointmentID;

end;

/* This procedure deals with the logic of taking a scheduled appointment

that the user makes and if the user decides to cancel a appointment

the procedure will take the ID that was created with the appointment and delete

everything tied to that ID.

create procedure CancelAppointment

@AppointmentID int

begin

IF NOT EXISTS (select 1 from ScheduledAppointments where AppointmentID = @AppointmentID)

begin

select 'Error: Appointment not found.' as ErrorMessage;

return;

end

select *

into #AppointmentDetails

from ScheduledAppointments

where AppointmentID = @AppointmentID;

-- delete the appointment

delete from ScheduledAppointments where AppointmentID = @AppointmentID;

select 'Appointment canceled successfully' as ConfirmationMessage, *

from #AppointmentDetails;

drop table #AppointmentDetails;

end;

/*This stored procedure will take a scheduled appointment that is made in the frontend.

the appointment that was made and then reschedule/make a new appointment for the user by updating

the scheduled appointment with the new inform

create procedure RescheduleAppointment

@AppointmentID int,

@NewDate date,

@NewStartTime time

begin

IF NOT EXISTS (select 1 from ScheduledAppointments where AppointmentID = @AppointmentID)

begin

select 'Error: Appointment not found.' as ErrorMessage;

return;

end

update ScheduledAppointments

set AppointmentDate = @NewDate,

AppointmentStartTime = @NewStartTime

where AppointmentID = @AppointmentID;

select 'Appointment rescheduled successfully' as ConfirmationMessage,

AppointmentID,

LocationID,

ModalityID,

AdvisorID,

AdviseeID,

AppointmentTypeID,

AppointmentDate,

AppointmentStartTime,

AppointmentDuration,

AppointmentDescription

from ScheduledAppointments

where AppointmentID = @AppointmentID;

end;

-- Get Info for front end --

-- Advisees

create procedure GetAdvisees

select * from Advisees

order by AdviseeID

;

-- Advisors

create procedure GetAdvisors

select * from Advisors

order by AdvisorID

;

-- Modalities

create procedure GetModalities

select *

from Modality

;

-- App Types

create procedure GetApptTypes

select *

from AppointmentType

;

-- Locations (With building names. No ID needed because that is contained in the Locations ID)

create procedure GetLocations

select LocationID, BuildingTitle,

BuildingAddress1, BuildingAddress2,

BuildingCity, BuildingState, LocationDescription

from Locations l

inner join Buildings b

on l.BuildingID = b.BuildingID

;

-- Appointments for Each advisor for time population

create procedure GetAvailability

select AdvisorID, AppointmentDate,

AppointmentStartTime, AppointmentDuration

from ScheduledAppointments

where AppointmentDate >= GETDATE()

;

-- Appointments for Current User

create procedure GetUserAppointments

@UserID int -- assume only students

begin

select AppointmentID, AppointmentDate, AppointmentStartTime, AppointmentDuration,

sa.AdvisorID, AdvisorLName + ', ' + AdvisorFName as AdvisorFullName, AdvisorEmail,

bu.BuildingAddress1, AdviseeID

from ScheduledAppointments sa

inner join Advisors adv

on adv.AdvisorID = sa.AdvisorID

inner join Locations loc

on sa.LocationID = loc.LocationID

inner join Buildings bu

on loc.BuildingID = bu.BuildingID

where sa.AdviseeID = @UserID;

end

-- Checks for if a users new appointment will overlap with another appointment

create procedure CheckForOverlappingAppointments

@LocationID int,

@ModalityID int,

@AdvisorID int,

@AdviseeID int,

@AppointmentTypeID int,

@AppointmentDate date,

@AppointmentStartTime time,

@AppointmentDuration int

begin

declare @EndTime time = dateadd(minute, @AppointmentDuration, @AppointmentStartTime)

if EXISTS (

select 1

from ScheduledAppointments

where LocationID = @LocationID

and ModalityID = @ModalityID

and AdvisorID = @AdvisorID

and AdviseeID = @AdviseeID

and AppointmentTypeID = @AppointmentTypeID

and AppointmentDate = @AppointmentDate

and (

(@AppointmentStartTime >= AppointmentStartTime and @AppointmentStartTime < dateadd(minute, AppointmentDuration, AppointmentStartTime))

OR (@EndTime > AppointmentStartTime and @EndTime <= dateadd(minute, AppointmentDuration, AppointmentStartTime))

OR (@AppointmentStartTime <= AppointmentStartTime and @EndTime >= dateadd(minute, AppointmentDuration, AppointmentStartTime))

)

begin

raiserror ('An overlapping appointment exists.', 16, 1)

end

B321_SQL_CSharp_Project/B321_StoredProceduresScript.sql at main · IBickCoding/B321_SQL_CSharp_Project · GitHub

Expand file tree

Breadcrumbs

B321_StoredProceduresScript.sql

Latest commit

History

Breadcrumbs

B321_StoredProceduresScript.sql

File metadata and controls