· 7 years ago · Oct 18, 2018, 07:00 PM
1using System;
2using System.Collections.Generic;
3using System.Linq;
4
5namespace HumaneSociety
6{
7 public static class Query
8 {
9 private static HumaneSocietyDataContext db = new HumaneSocietyDataContext();
10
11 /// <summary>
12 /// A method to read the employee from the employee table
13 /// </summary>
14 /// <param name="employee"></param>
15 internal static void ReadEmployee(Employee employee)
16 {
17 //Console.WriteLine("Retrieving Employee");
18 employee = db.Employees.Where(e => e.EmployeeNumber == employee.EmployeeNumber).Single();
19 UserInterface.DisplayUserOptions("Name: " + employee.FirstName + " " + employee.LastName);
20 UserInterface.DisplayUserOptions("Username: " + employee.UserName);
21 UserInterface.DisplayUserOptions("Password: " + employee.Password);
22 UserInterface.DisplayUserOptions("Email: " + employee.Email);
23 UserInterface.DisplayUserOptions("Employee Number: " + employee.EmployeeNumber);
24 UserInterface.DisplayUserOptions("Assigned Animals: ");
25 foreach (Animal a in employee.Animals)
26 {
27 UserInterface.DisplayUserOptions(a.Name);
28 }
29 }
30
31 /// <summary>
32 /// A method to delete an employee from the employee table
33 /// </summary>
34 /// <param name="employee"></param>
35 internal static void DeleteEmployee(Employee employee)
36 {
37 Employee target = db.Employees.Where(e => e.EmployeeNumber == employee.EmployeeNumber && e.LastName == employee.LastName).Single();
38 Console.WriteLine("You are about to delete {0} {1}, Employee Number: {2}. Are you sure? (y/n)", target.FirstName, target.LastName, target.EmployeeNumber);
39 if (Console.ReadKey().KeyChar == 'y')
40 {
41 db.Employees.DeleteOnSubmit(target);
42 db.SubmitChanges();
43 //Console.WriteLine("Employee deleted.");
44 }
45 else
46 {
47 Console.WriteLine("Delete aborted.");
48 }
49 Console.WriteLine("Press any key to return to the previous menu.");
50 Console.ReadKey(true);
51 }
52
53 internal static void UpdateEmployee(int employeeNumber)
54 {
55 Employee target = db.Employees.Where(e => e.EmployeeNumber == employeeNumber).Single();
56 Console.WriteLine("Current values:");
57 ReadEmployee(target);
58 Console.WriteLine("Which field would you like to change?");
59 Console.ReadLine();
60 }
61
62 internal static bool EmployeeNumberIsAlreadyInUse(int? employeeNumber)
63 {
64 try
65 {
66 Employee test = db.Employees.Where(e => e.EmployeeNumber == employeeNumber).Single();
67 Console.WriteLine("Employee Number {0} is already in use", employeeNumber);
68 Console.WriteLine("Press any key to return to previous menu.");
69 Console.ReadKey(true);
70 return true;
71 }
72 catch (Exception)
73 {
74 Console.WriteLine("Valid number");
75 return false;
76 }
77 }
78
79 /// <summary>
80 /// A method to insert an employee into employee table
81 /// </summary>
82 /// <param name="employee"></param>
83 internal static void CreateEmployee(Employee employee)
84 {
85 db.Employees.InsertOnSubmit(employee);
86 db.SubmitChanges();
87 }
88
89 //A method to retrieve a client from the clients table
90 internal static Client GetClient(string userName, string password)
91 {
92 Client outputClient = db.Clients.Where(u => u.UserName == userName && u.Password == password).Single();
93 return outputClient;
94 }
95
96 /// <summary>
97 /// A method to return an enumerable list of animals available for adoption
98 /// </summary>
99 /// <param name="client"></param>
100 /// <returns></returns>
101 internal static IQueryable<Adoption> GetUserAdoptionStatus(Client client)
102 {
103 //SELECT * FROM ADOPTIONS WHERE CLIENTID = ""
104 return db.Adoptions.Where(a => a.ClientId == client.ClientId);
105 }
106
107 /// <summary>
108 /// A method to get an animal based on ID
109 /// </summary>
110 /// <param name="iD"></param>
111 /// <returns></returns>
112 internal static Animal GetAnimalByID(int iD)
113 {
114 //SELECT FROM ANIMALS WHERE ANIMALID = ""
115 return db.Animals.Where(x => x.AnimalId == iD).Single();
116 }
117
118 internal static void Adopt(Animal animal, Client client)
119 {
120 animal.AdoptionStatus = "Pending";
121 Adoption adoption = new Adoption();
122 adoption.Client = client;
123 adoption.Animal = animal;
124 adoption.ApprovalStatus = "Pending";
125 adoption.AdoptionFee = 75;
126 adoption.PaymentCollected = true;
127
128 db.Adoptions.InsertOnSubmit(adoption);
129 db.SubmitChanges();
130
131 }
132
133 internal static IQueryable<Animal> SearchForAnimalByMultipleTraits()
134 {
135 //SELECT FROM ANIMALS WHERE ANIMALS
136 return db.Animals.Where(x => true);
137 }
138
139 internal static IQueryable<Client> RetrieveClients()
140 {
141 return db.Clients;
142 }
143
144 internal static IQueryable<USState> GetStates()
145 {
146 return db.USStates;
147 }
148
149 internal static void AddNewClient(string firstName, string lastName, string username, string password, string email, string streetAddress, int zipCode, int state)
150 {
151 Address address = new Address();
152 address.AddressLine1 = streetAddress;
153 address.Zipcode = zipCode;
154 address.USStateId = state;
155 db.Addresses.InsertOnSubmit(address);
156 db.SubmitChanges();
157
158 Client client = new Client();
159 client.FirstName = firstName;
160 client.LastName = lastName;
161 client.UserName = username;
162 client.Password = password;
163 client.Email = email;
164 client.AddressId = address.AddressId;
165 db.Clients.InsertOnSubmit(client);
166 db.SubmitChanges();
167 }
168
169 internal static void UpdateClient(Client client)
170 {
171 db.SubmitChanges();
172 }
173
174 internal static IQueryable<Adoption> GetPendingAdoptions()
175 {
176 throw new NotImplementedException();
177 }
178
179 internal static void RemoveAnimal(Animal animal)
180 {
181 db.Animals.DeleteOnSubmit(animal);
182 db.SubmitChanges();
183 }
184
185 /// <summary>
186 /// A method to get species as a list
187 /// </summary>
188 /// <returns></returns>
189 internal static Species GetSpecies()
190 {
191 List<Species> listingOfSpecies = new List<Species>();
192 foreach (Species s in db.Species)
193 {
194 listingOfSpecies.Add(s);
195 }
196 Species newSpecies;
197 DisplaySpeciesOptions();
198 string input = UserInterface.GetStringData("menu number", "the species");
199
200 try
201 {
202 if (Int32.Parse(input) - 1 >= listingOfSpecies.Count)
203 {
204 return CreateSpecies();
205 }
206 else
207 {
208 newSpecies = listingOfSpecies[Int32.Parse(input) - 1];
209 return newSpecies;
210 }
211 }
212 catch (Exception)
213 {
214 Console.Clear();
215 Console.WriteLine("Incorrect input please try again! \n");
216 GetSpecies();
217 }
218
219 return null;
220 }
221
222 /// <summary>
223 /// A method to display the species options
224 /// </summary>
225 internal static void DisplaySpeciesOptions()
226 {
227 int counter = 1;
228 Console.WriteLine("Please select the animal's species: \n");
229
230 foreach (Species s in db.Species)
231 {
232 Console.WriteLine(counter + "- " + s.Name + "\n");
233 counter++;
234 }
235 Console.WriteLine((counter) + "- Species Not Listed \n");
236 }
237
238 /// <summary>
239 /// A method to create the species
240 /// </summary>
241 /// <returns></returns>
242 internal static Species CreateSpecies()
243 {
244 Species newSpecies = new Species();
245 newSpecies.Name = UserInterface.GetStringData("the species", "the name of");
246 db.Species.InsertOnSubmit(newSpecies);
247 db.SubmitChanges();
248 return newSpecies;
249 }
250
251 /// <summary>
252 /// A method to get the diet plans
253 /// </summary>
254 /// <returns></returns>
255 internal static DietPlan GetDietPlan()
256 {
257 DisplayDietPlans();
258 string dietName = UserInterface.GetStringData("name", "the desired diet plan");
259 return db.DietPlans.Where(d => d.Name == dietName).Single();
260 }
261
262
263 /// <summary>
264 /// A method to get display the diet plans
265 /// </summary>
266 internal static void DisplayDietPlans()
267 {
268 UserInterface.DisplayUserOptions("Available Diet Plans: ");
269 foreach (DietPlan d in db.DietPlans.Where(p => true))
270 {
271 UserInterface.DisplayUserOptions("- " + d.Name);
272 }
273 }
274
275 /// <summary>
276 /// A method to add an animal
277 /// </summary>
278 /// <param name="animal"></param>
279 internal static void AddAnimal(Animal animal)
280 {
281 throw new NotImplementedException();
282 }
283
284 /// <summary>
285 /// A method to login to the database
286 /// </summary>
287 /// <param name="userName"></param>
288 /// <param name="password"></param>
289 /// <returns></returns>
290 internal static Employee EmployeeLogin(string userName, string password)
291 {
292 //SELECT FROM EMPLOYEES WHERE USERNAME = "" AND PASSWORD = ""
293 var Employee = db.Employees.Where(x => x.UserName == userName && x.Password == password);
294 return Employee.Single();
295 }
296
297 /// <summary>
298 /// A method to get the employee login information
299 /// </summary>
300 /// <param name="email"></param>
301 /// <param name="employeeNumber"></param>
302 /// <returns></returns>
303 internal static Employee RetrieveEmployeeUser(string email, int? employeeNumber)
304 {
305 //SELECT FROM EMPLOYEES WHERE EMAIL = "" AND EMPLOYEENUMBER = ""
306 var Employee = db.Employees.Where(e => e.Email == email && e.EmployeeNumber == employeeNumber);
307 return Employee.Single();
308 }
309
310 /// <summary>
311 /// A method to update the adoption status
312 /// </summary>
313 /// <param name="v"></param>
314 /// <param name="adoption"></param>
315 internal static void UpdateAdoption(bool v, Adoption adoption)
316 {
317 throw new NotImplementedException();
318 }
319
320 /// <summary>
321 /// A method to get a room for an animal
322 /// </summary>
323 /// <param name="animalId"></param>
324 /// <returns></returns>
325 internal static Room GetRoom(int animalId)
326 {
327 //SELECT FROM ROOMS WHERE ANIMALID = ""
328 return db.Rooms.Where(r => r.AnimalId == animalId).Single();
329 }
330
331 /// <summary>
332 /// A method to add an employee user account
333 /// </summary>
334 /// <param name="employee"></param>
335 internal static void AddUsernameAndPassword(Employee employee)
336 {
337 //SELECT FROM EMPLOYEE WHERE EMAIL = "" AND EMPLOYEENUMBER = ""
338 RetrieveEmployeeUser(employee.Email, employee.EmployeeNumber).UserName = employee.UserName;
339 RetrieveEmployeeUser(employee.Email, employee.EmployeeNumber).Password = employee.Password;
340 db.SubmitChanges();
341 }
342
343 /// <summary>
344 /// A method to check if employee username exists in employee table
345 /// </summary>
346 /// <param name="username"></param>
347 /// <returns></returns>
348 internal static bool CheckEmployeeUserNameExist(string username)
349 {
350 //SELECT FROM EMPLOYEE WHERE USERNAME = ""
351 var nameExists = db.Employees.Where(e => e.UserName == username);
352 if (nameExists.Count() > 0)
353 {
354 return true;
355 }
356 return false;
357 }
358
359 internal static IQueryable<AnimalShot> GetShots(Animal animal)
360 {
361 var shots = db.AnimalShots.Where(a => a.AnimalId == animal.AnimalId);
362 return shots;
363 }
364
365 internal static void UpdateShot(string v, Animal animal)
366 {
367 AnimalShot vaccinatedAnimal = new AnimalShot();
368 if (CheckShot(v))
369 {
370 vaccinatedAnimal.Animal = animal;
371
372 vaccinatedAnimal.DateReceived = DateTime.Now;
373 vaccinatedAnimal.Shot = db.Shots.Where(s => s.Name == v).Single();
374 db.AnimalShots.InsertOnSubmit(vaccinatedAnimal);
375 db.SubmitChanges();
376 }
377 else
378 {
379 Console.WriteLine("Shot does not exist! \n");
380 }
381 }
382
383 internal static bool CheckShot(string shotName)
384 {
385 bool shotExists = false;
386 foreach (Shot s in db.Shots)
387 {
388 if (s.Name == shotName)
389 {
390 shotExists = true;
391 }
392 }
393 return shotExists;
394 }
395
396 /// <summary>
397 /// A method to enter update an animal into a record book
398 /// </summary>
399 /// <param name="animal"></param>
400 /// <param name="updates"></param>
401 internal static void EnterUpdate(Animal animal, Dictionary<int, string> updates)
402 {
403 foreach (KeyValuePair<int, string> entry in updates)
404 {
405 switch (entry.Key)
406 {
407 case 1:
408 //SELECT FROM SPECIES WHERE NAME = ""
409 animal.Species = db.Species.Where(s => s.Name == entry.Value).Single();
410 break;
411 case 2:
412 animal.Name = entry.Value;
413 break;
414 case 3:
415 animal.Age = Int32.Parse(entry.Value);
416 break;
417 case 4:
418 animal.Demeanor = entry.Value;
419 break;
420 case 5:
421 if (entry.Value.ToLower() == "true")
422 {
423 animal.KidFriendly = true;
424 }
425 else
426 {
427 animal.KidFriendly = false;
428 }
429 break;
430 case 6:
431 if (entry.Value.ToLower() == "true")
432 {
433 animal.PetFriendly = true;
434 }
435 else
436 {
437 animal.PetFriendly = false;
438 }
439 break;
440 case 7:
441 animal.Weight = Int32.Parse(entry.Value);
442 break;
443 default:
444 break;
445 }
446 }
447 db.SubmitChanges();
448 }
449
450 /// <summary>
451 /// A method to change animal room
452 /// </summary>
453 /// <param name="animal"></param>
454 internal static void ChangeAnimalRoom(Animal animal)
455 {
456 //SELECT FROM ROOMS WHERE ANIMAL = "" AND ANIMALID = NULL
457 db.Rooms.Where(r => r.Animal == animal).Single().AnimalId = null;
458 DisplayAvailableRooms();
459 int newRoomNumber = UserInterface.GetIntegerData("room number", "the new");
460 db.Rooms.Where(r => r.RoomNumber == newRoomNumber).Single().AnimalId = animal.AnimalId;
461 db.SubmitChanges();
462 }
463
464 /// <summary>
465 /// A method to show available rooms
466 /// </summary>
467 internal static void DisplayAvailableRooms()
468 {
469 //SELECT FROM ROOMS WHERE ANIMALID = 0 AND ANIMALID = NULL
470 var rooms = db.Rooms.Where(r => true);
471 UserInterface.DisplayUserOptions("Rooms Available: ");
472 foreach (Room r in rooms)
473 {
474 if (r.AnimalId == 0 || r.AnimalId == null)
475 {
476 UserInterface.DisplayUserOptions(r.RoomNumber + ", ");
477 }
478 }
479 Console.WriteLine("\n");
480 }
481 }
482}