/[H6]/DatabaseLayer.cpp
ViewVC logotype

Diff of /DatabaseLayer.cpp

Parent Directory Parent Directory | Revision Log Revision Log | View Patch Patch

revision 23 by torben, Fri Sep 1 02:42:38 2006 UTC revision 39 by torben, Mon Sep 4 12:39:10 2006 UTC
# Line 1  Line 1 
1    /*
2     * Primary developers: Hedin & Kevin
3     */
4    
5  #include "StdAfx.h"  #include "StdAfx.h"
6  #include ".\databaselayer.h"  #include ".\databaselayer.h"
# Line 52  Person DatabaseLayer::GetPerson(CString Line 54  Person DatabaseLayer::GetPerson(CString
54          CString SQL;          CString SQL;
55    
56          Person p;          Person p;
57          //FIX SQL INJECTION          
58            wantInits.Replace("'","\"");
59    
60          SQL = "SELECT PersonID, Initialer, Navn, Admin, Pass FROM Person WHERE (Initialer = '" + wantInits + "')";          SQL = "SELECT PersonID, Initialer, Navn, Admin, Pass FROM Person WHERE (Initialer = '" + wantInits + "')";
61          CRecordset rs(&db);          CRecordset rs(&db);
62          rs.Open(AFX_DB_USE_DEFAULT_TYPE, SQL);          rs.Open(AFX_DB_USE_DEFAULT_TYPE, SQL);
63            
64          if(!rs.IsEOF() )          if(!rs.IsEOF() )
65          {          {
66                          rs.GetFieldValue((short)0,id);                          rs.GetFieldValue((short)0,id);
# Line 71  Person DatabaseLayer::GetPerson(CString Line 75  Person DatabaseLayer::GetPerson(CString
75                          p.isadmin       = (isadmin == "1");                          p.isadmin       = (isadmin == "1");
76                          p.pass          = pass;                          p.pass          = pass;
77          }          }
78            
79          return p;          return p;
80  }  }
81    
82  bool DatabaseLayer::AddPerson(Person NewPerson)  bool DatabaseLayer::AddPerson(Person NewPerson)
83  {  {
84          CString SQL;          CString SQL;
85            NewPerson.inits.Replace("'","\"");
86            NewPerson.name.Replace("'","\"");
87            NewPerson.pass.Replace("'","\"");
88          SQL.Format("INSERT into Person(Initialer, Navn, Admin, Pass) VALUES('%s', '%s', '%d', '%s')",NewPerson.inits, NewPerson.name, NewPerson.isadmin, NewPerson.pass);          SQL.Format("INSERT into Person(Initialer, Navn, Admin, Pass) VALUES('%s', '%s', '%d', '%s')",NewPerson.inits, NewPerson.name, NewPerson.isadmin, NewPerson.pass);
89          db.ExecuteSQL(SQL);          db.ExecuteSQL(SQL);
90          return true;          return true;
# Line 85  bool DatabaseLayer::AddPerson(Person New Line 93  bool DatabaseLayer::AddPerson(Person New
93  bool DatabaseLayer::UpdatePerson(Person ChangePerson)  bool DatabaseLayer::UpdatePerson(Person ChangePerson)
94  {  {
95          CString SQL;          CString SQL;
96            ChangePerson.inits.Replace("'","\"");
97            ChangePerson.name.Replace("'","\"");
98            ChangePerson.pass.Replace("'","\"");
99            ChangePerson.id.Replace("'","\"");
100          SQL.Format("UPDATE Person SET Initialer = '%s', Navn = '%s', Pass = '%s', Admin = '%d' WHERE PersonID = '%s'", ChangePerson.inits, ChangePerson.name, ChangePerson.pass, ChangePerson.isadmin, ChangePerson.id);          SQL.Format("UPDATE Person SET Initialer = '%s', Navn = '%s', Pass = '%s', Admin = '%d' WHERE PersonID = '%s'", ChangePerson.inits, ChangePerson.name, ChangePerson.pass, ChangePerson.isadmin, ChangePerson.id);
101          db.ExecuteSQL(SQL);          db.ExecuteSQL(SQL);
102          return true;          return true;
# Line 93  bool DatabaseLayer::UpdatePerson(Person Line 105  bool DatabaseLayer::UpdatePerson(Person
105  bool DatabaseLayer::DeletePerson(Person RemovePerson)  bool DatabaseLayer::DeletePerson(Person RemovePerson)
106  {  {
107          CString SQL;          CString SQL;
108            RemovePerson.id.Replace("'","\"");
109          SQL.Format("DELETE FROM Person WHERE PersonID = '%s'", RemovePerson.id);          SQL.Format("DELETE FROM Person WHERE PersonID = '%s'", RemovePerson.id);
110          db.ExecuteSQL(SQL);          db.ExecuteSQL(SQL);
111          return true;          return true;
# Line 101  bool DatabaseLayer::DeletePerson(Person Line 114  bool DatabaseLayer::DeletePerson(Person
114  bool DatabaseLayer::ReturnEquipment(CString barcode)  bool DatabaseLayer::ReturnEquipment(CString barcode)
115  {  {
116          CString SQL;          CString SQL;
117            barcode.Replace("'","\"");
118          SQL.Format("UPDATE Udstyr SET Status = '3' WHERE Stregkode= '%s'", barcode);          SQL.Format("UPDATE Udstyr SET Status = '3' WHERE Stregkode= '%s'", barcode);
119          db.ExecuteSQL(SQL);          db.ExecuteSQL(SQL);
120                    
121          SQL.Format("UPDATE Udlån SET Afleveringsdato = Getdate() WHERE Stregkode= '%s' AND Afleveringsdato IS NULL ", barcode);          SQL.Format("UPDATE Udlån SET Afleveringsdato = Getdate() WHERE Stregkode= '%s' AND Afleveringsdato IS NULL ", barcode);
122          db.ExecuteSQL(SQL);          db.ExecuteSQL(SQL);
123    
124            checkReservations(barcode);
125          return true;          return true;
126  }  }
127    
128  bool DatabaseLayer::CheckoutEquipment(Person CheckPersID, Equipment CheckEquip, int Numdays)  bool DatabaseLayer::CheckoutEquipment(Person CheckPersID, Equipment CheckEquip, int Numdays)
129  {  {
130          CString SQL;          CString SQL;
131            CheckEquip.barcode.Replace("'","\"");
132            CheckPersID.id.Replace("'","\"");
133          SQL.Format("UPDATE Udstyr SET Status = '1' WHERE Stregkode = '%s' ", CheckEquip.barcode);          SQL.Format("UPDATE Udstyr SET Status = '1' WHERE Stregkode = '%s' ", CheckEquip.barcode);
134          db.ExecuteSQL(SQL);          db.ExecuteSQL(SQL);
135          SQL.Format("INSERT INTO Udlån (PersonID, Stregkode, Startdato, Antaldage) VALUES( '%s','%s', GetDate(),'%d')", CheckPersID.id, CheckEquip.barcode, Numdays);          SQL.Format("INSERT INTO Udlån (PersonID, Stregkode, Startdato, Antaldage) VALUES( '%s','%s', GetDate(),'%d')", CheckPersID.id, CheckEquip.barcode, Numdays);
# Line 119  bool DatabaseLayer::CheckoutEquipment(Pe Line 137  bool DatabaseLayer::CheckoutEquipment(Pe
137          return true;          return true;
138  }  }
139    
140  bool DatabaseLayer::EquipmentReservation(Person CheckPersID, Equipment CheckEquip, int Numdays)  bool DatabaseLayer::EquipmentReservation(CString barcode, Person CheckPerson)
141  {  {
142          CString SQL;          CString SQL;
143          SQL.Format("UPDATE Udstyr SET Status = '2' WHERE Stregkode = '%s' ", CheckEquip.barcode);          CheckPerson.id.Replace("'","\"");
144          db.ExecuteSQL(SQL);          barcode.Replace("'","\"");
145          SQL.Format("INSERT INTO Resevation (PersonID, Stregkode, Startdato, Antaldage) VALUES( '%s','%s', GetDate(),'%d')", CheckPersID.id, CheckEquip.barcode, Numdays);          SQL.Format("INSERT INTO Resevation (PersonID, Stregkode, Startdato) VALUES( '%s','%s', GetDate())", CheckPerson.id, barcode);
146          db.ExecuteSQL(SQL);          db.ExecuteSQL(SQL);
147            checkReservations(barcode);
148          return true;          return true;
149  }  }
150    
151  bool DatabaseLayer::UpdateEquipment(Equipment CheckEquip)  bool DatabaseLayer::UpdateEquipment(Equipment CheckEquip)
152  {  {
153          CString SQL;          CString SQL;
154            CheckEquip.name.Replace("'","\"");
155            CheckEquip.description.Replace("'","\"");
156            CheckEquip.placement.Replace("'","\"");
157            CheckEquip.status.Replace("'","\"");
158            CheckEquip.barcode.Replace("'","\"");
159          SQL.Format("UPDATE Udstyr SET Navn = '%s', Beskrivelse = '%s', Placering = '%s', Status = '%s' WHERE Stregkode = '%s'", CheckEquip.name, CheckEquip.description, CheckEquip.placement, CheckEquip.status, CheckEquip.barcode);          SQL.Format("UPDATE Udstyr SET Navn = '%s', Beskrivelse = '%s', Placering = '%s', Status = '%s' WHERE Stregkode = '%s'", CheckEquip.name, CheckEquip.description, CheckEquip.placement, CheckEquip.status, CheckEquip.barcode);
160          db.ExecuteSQL(SQL);          db.ExecuteSQL(SQL);
161          return true;          return true;
# Line 140  bool DatabaseLayer::UpdateEquipment(Equi Line 164  bool DatabaseLayer::UpdateEquipment(Equi
164  bool DatabaseLayer::AddEquipment(Equipment AddEquip)  bool DatabaseLayer::AddEquipment(Equipment AddEquip)
165  {  {
166          CString SQL;          CString SQL;
167            AddEquip.barcode.Replace("'","\"");
168            AddEquip.name.Replace("'","\"");
169            AddEquip.description.Replace("'","\"");
170            AddEquip.placement.Replace("'","\"");
171          SQL.Format("INSERT into Udstyr(Stregkode, Navn, Beskrivelse, Placering, Status) VALUES('%s', '%s', '%s', '%s', '%s')",AddEquip.barcode, AddEquip.name, AddEquip.description, AddEquip.placement, "3");          SQL.Format("INSERT into Udstyr(Stregkode, Navn, Beskrivelse, Placering, Status) VALUES('%s', '%s', '%s', '%s', '%s')",AddEquip.barcode, AddEquip.name, AddEquip.description, AddEquip.placement, "3");
172          db.ExecuteSQL(SQL);          db.ExecuteSQL(SQL);
173          return true;          return true;
# Line 181  Equipment DatabaseLayer::GetEquipment(CS Line 209  Equipment DatabaseLayer::GetEquipment(CS
209    
210          CString SQL;          CString SQL;
211    
212            wantBarcode.Replace("'","\"");
213    
214          Equipment e;          Equipment e;
215    
216          SQL.Format("SELECT Stregkode,Navn,Udstyr.Beskrivelse,Placering,Status.Beskrivelse FROM Udstyr INNER JOIN Status ON Udstyr.Status = Status.StatusID Where (Stregkode = '%s')", wantBarcode);          SQL.Format("SELECT Stregkode,Navn,Udstyr.Beskrivelse,Placering,Status.Beskrivelse FROM Udstyr INNER JOIN Status ON Udstyr.Status = Status.StatusID Where (Stregkode = '%s')", wantBarcode);
# Line 207  Equipment DatabaseLayer::GetEquipment(CS Line 237  Equipment DatabaseLayer::GetEquipment(CS
237  bool DatabaseLayer::DeleteEquipment(Equipment DelEquip)  bool DatabaseLayer::DeleteEquipment(Equipment DelEquip)
238  {  {
239          CString SQL;          CString SQL;
240            DelEquip.barcode.Replace("'","\"");
241          SQL.Format("DELETE FROM Udstyr WHERE Stregkode = '%s'", DelEquip.barcode);          SQL.Format("DELETE FROM Udstyr WHERE Stregkode = '%s'", DelEquip.barcode);
242          db.ExecuteSQL(SQL);          db.ExecuteSQL(SQL);
243          return true;          return true;
# Line 218  vector<Equipment> DatabaseLayer::Search( Line 249  vector<Equipment> DatabaseLayer::Search(
249  {  {
250          vector<Equipment> buffer;          vector<Equipment> buffer;
251    
252            barcode.Replace("'","\"");
253            name.Replace("'","\"");
254            inits.Replace("'","\"");
255    
256            //here are the wanted equipments found through 3 seperate queries - I could also
257            //find them all in one query where I joined the 3 selects via a UNION
258          CString SQL;          CString SQL;
259          if (available == true) {          if (available == true) {
260                  SQL = "SELECT DISTINCT Udstyr.stregkode,Udstyr.Navn,Udstyr.Beskrivelse,Udstyr.Placering,Status.Beskrivelse \                  SQL = "SELECT DISTINCT Udstyr.stregkode,Udstyr.Navn,Udstyr.Beskrivelse,Udstyr.Placering,Status.Beskrivelse \
# Line 240  vector<Equipment> DatabaseLayer::Search( Line 277  vector<Equipment> DatabaseLayer::Search(
277                                     INNER JOIN Status ON Udstyr.status = Status.StatusID \r\n\                                     INNER JOIN Status ON Udstyr.status = Status.StatusID \r\n\
278                                     INNER JOIN Resevation ON Udstyr.Stregkode = Resevation.stregkode \r\n\                                     INNER JOIN Resevation ON Udstyr.Stregkode = Resevation.stregkode \r\n\
279                                     INNER JOIN Person ON Resevation.PersonID = Person.PersonID\r\n\                                     INNER JOIN Person ON Resevation.PersonID = Person.PersonID\r\n\
280                                     WHERE Udstyr.Status = 2 ";                                     WHERE ( Udstyr.Status = 2 OR Person.Initialer = '" + inits + "') ";
281                  if (barcode != "")                  if (barcode != "")
282                          SQL += " AND Udstyr.stregkode = '" + barcode + "' ";                          SQL += " AND Udstyr.stregkode = '" + barcode + "' ";
283                  if (name != "")                  if (name != "")
284                          SQL += " AND Udstyr.Navn LIKE '%" + name + "%' ";                          SQL += " AND Udstyr.Navn LIKE '%" + name + "%' ";
285                  if (inits != "")                  //if (inits != "")
286                          SQL += " AND Person.Initialer = '" + inits + "' ";                  //      SQL += " AND Person.Initialer = '" + inits + "' ";
287                                    
288                  CRecordset rs(&db);                  CRecordset rs(&db);
289                  rs.Open(AFX_DB_USE_DEFAULT_TYPE, SQL);                  rs.Open(AFX_DB_USE_DEFAULT_TYPE, SQL);
# Line 276  vector<Equipment> DatabaseLayer::Search( Line 313  vector<Equipment> DatabaseLayer::Search(
313                  rs.Close();                  rs.Close();
314          }          }
315    
316            // now I have the equipment rows the user asked for, now I need some more checkout and
317            // reservation data for each piece of equipment
318    
319          for (int i=0; i<buffer.size(); i++) {          for (int i=0; i<buffer.size(); i++) {
320                  //fill Equipment::checkouts                  //fill Equipment::checkouts
321                                    
# Line 301  vector<Equipment> DatabaseLayer::Search( Line 341  vector<Equipment> DatabaseLayer::Search(
341                  rs.Close();                  rs.Close();
342                                                                    
343                  // fill Equipment::reservations                  // fill Equipment::reservations
344                  SQL = "SELECT Resevation.StartDato, Resevation.Antaldage, Person.Initialer\                  SQL = "SELECT Resevation.ResevationID, Resevation.StartDato,  Person.Initialer\
345                                  FROM Resevation\                                  FROM Resevation\
346                                  INNER JOIN Person ON Resevation.PersonID = Person.PersonID ";                                  INNER JOIN Person ON Resevation.PersonID = Person.PersonID ";
347                  SQL += "WHERE Resevation.Stregkode = " + buffer[i].barcode + " ORDER BY STARTDATO ASC";                  SQL += "WHERE Resevation.Stregkode = " + buffer[i].barcode + " ORDER BY STARTDATO ASC";
# Line 311  vector<Equipment> DatabaseLayer::Search( Line 351  vector<Equipment> DatabaseLayer::Search(
351                          rs.MoveFirst();                          rs.MoveFirst();
352                          while ( !rs.IsEOF() ) {                          while ( !rs.IsEOF() ) {
353                                  Reservation res;                                  Reservation res;
354                                  rs.GetFieldValue((short)0, res.startdate);                                  rs.GetFieldValue((short)0, res.reservationID);
355                                  rs.GetFieldValue(1, res.numdays);                                  rs.GetFieldValue(1, res.startdate);
356                                  rs.GetFieldValue(2, res.inits);                                  rs.GetFieldValue(2, res.inits);
357    
358                                  buffer[i].reservations.push_back(res);                                  buffer[i].reservations.push_back(res);
# Line 345  void DatabaseLayer::FillEquipmentVector( Line 385  void DatabaseLayer::FillEquipmentVector(
385                  }                  }
386          }          }
387  }  }
388    //Developed by Torben H. Nielsen
389    void DatabaseLayer::checkReservations(CString barcode)
390    {
391            CString scount,status;
392            barcode.Replace("'","\"");
393            CString SQL = "SELECT count(*) FROM Resevation WHERE Resevation.Stregkode = '" + barcode  + "'";
394            CRecordset rs(&db);
395            rs.Open(AFX_DB_USE_DEFAULT_TYPE,SQL);
396            rs.MoveFirst();
397            rs.GetFieldValue((short)0,scount);
398            int reservationCount = atoi(scount);
399            rs.Close();
400    
401            SQL = "SELECT status FROM Udstyr WHERE Udstyr.Stregkode = '" + barcode + "'";
402            rs.Open(AFX_DB_USE_DEFAULT_TYPE,SQL);
403            if (rs.GetRecordCount() >0) {
404                    rs.MoveFirst();
405                    rs.GetFieldValue((short)0,status);
406            }
407            rs.Close();
408    
409            if (reservationCount > 0 && status == "3") {//equipment is available and has at lease one reservation
410                    SQL = "UPDATE Udstyr SET Status = 2 WHERE Stregkode = '" + barcode + "'";
411                    db.ExecuteSQL(SQL);
412            }
413    }
414    
415    //Developed by Torben H. Nielsen
416    bool DatabaseLayer::DeleteReservation(CString barcode, CString resid)
417    {
418            barcode.Replace("'","\"");
419            resid.Replace("'","\"");
420            CString SQL = "DELETE FROM Resevation WHERE resevationid = " + resid;
421            db.ExecuteSQL(SQL);
422            checkReservations(barcode);
423            return true;
424    }
425    
Legend:
Removed from v.23  
changed lines
  Added in v.39
  ViewVC Help
Powered by ViewVC 1.1.20