1 |
|
/* |
2 |
|
* Primary developers: Hedin & Kevin |
3 |
|
*/ |
4 |
|
|
5 |
#include "StdAfx.h" |
#include "StdAfx.h" |
6 |
#include ".\databaselayer.h" |
#include ".\databaselayer.h" |
108 |
|
|
109 |
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); |
110 |
db.ExecuteSQL(SQL); |
db.ExecuteSQL(SQL); |
111 |
|
|
112 |
|
checkReservations(barcode); |
113 |
return true; |
return true; |
114 |
} |
} |
115 |
|
|
123 |
return true; |
return true; |
124 |
} |
} |
125 |
|
|
126 |
bool DatabaseLayer::EquipmentReservation(Person CheckPersID, Equipment CheckEquip, int Numdays) |
bool DatabaseLayer::EquipmentReservation(CString barcode, Person CheckPerson) |
127 |
{ |
{ |
128 |
CString SQL; |
CString SQL; |
129 |
SQL.Format("UPDATE Udstyr SET Status = '2' WHERE Stregkode = '%s' ", CheckEquip.barcode); |
SQL.Format("INSERT INTO Resevation (PersonID, Stregkode, Startdato) VALUES( '%s','%s', GetDate())", CheckPerson.id, barcode); |
|
db.ExecuteSQL(SQL); |
|
|
SQL.Format("INSERT INTO Resevation (PersonID, Stregkode, Startdato, Antaldage) VALUES( '%s','%s', GetDate(),'%d')", CheckPersID.id, CheckEquip.barcode, Numdays); |
|
130 |
db.ExecuteSQL(SQL); |
db.ExecuteSQL(SQL); |
131 |
|
checkReservations(barcode); |
132 |
return true; |
return true; |
133 |
} |
} |
134 |
|
|
221 |
{ |
{ |
222 |
vector<Equipment> buffer; |
vector<Equipment> buffer; |
223 |
|
|
224 |
|
//here are the wanted equipments found through 3 seperate queries - I could also |
225 |
|
//find them all in one query where I joined the 3 selects via a UNION |
226 |
CString SQL; |
CString SQL; |
227 |
if (available == true) { |
if (available == true) { |
228 |
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 \ |
245 |
INNER JOIN Status ON Udstyr.status = Status.StatusID \r\n\ |
INNER JOIN Status ON Udstyr.status = Status.StatusID \r\n\ |
246 |
INNER JOIN Resevation ON Udstyr.Stregkode = Resevation.stregkode \r\n\ |
INNER JOIN Resevation ON Udstyr.Stregkode = Resevation.stregkode \r\n\ |
247 |
INNER JOIN Person ON Resevation.PersonID = Person.PersonID\r\n\ |
INNER JOIN Person ON Resevation.PersonID = Person.PersonID\r\n\ |
248 |
WHERE Udstyr.Status = 2 "; |
WHERE ( Udstyr.Status = 2 OR Person.Initialer = '" + inits + "') "; |
249 |
if (barcode != "") |
if (barcode != "") |
250 |
SQL += " AND Udstyr.stregkode = '" + barcode + "' "; |
SQL += " AND Udstyr.stregkode = '" + barcode + "' "; |
251 |
if (name != "") |
if (name != "") |
252 |
SQL += " AND Udstyr.Navn LIKE '%" + name + "%' "; |
SQL += " AND Udstyr.Navn LIKE '%" + name + "%' "; |
253 |
if (inits != "") |
//if (inits != "") |
254 |
SQL += " AND Person.Initialer = '" + inits + "' "; |
// SQL += " AND Person.Initialer = '" + inits + "' "; |
255 |
|
|
256 |
CRecordset rs(&db); |
CRecordset rs(&db); |
257 |
rs.Open(AFX_DB_USE_DEFAULT_TYPE, SQL); |
rs.Open(AFX_DB_USE_DEFAULT_TYPE, SQL); |
281 |
rs.Close(); |
rs.Close(); |
282 |
} |
} |
283 |
|
|
284 |
|
// now I have the equipment rows the user asked for, now I need some more checkout and |
285 |
|
// reservation data for each piece of equipment |
286 |
|
|
287 |
for (int i=0; i<buffer.size(); i++) { |
for (int i=0; i<buffer.size(); i++) { |
288 |
//fill Equipment::checkouts |
//fill Equipment::checkouts |
289 |
|
|
309 |
rs.Close(); |
rs.Close(); |
310 |
|
|
311 |
// fill Equipment::reservations |
// fill Equipment::reservations |
312 |
SQL = "SELECT Resevation.StartDato, Resevation.Antaldage, Person.Initialer\ |
SQL = "SELECT Resevation.ResevationID, Resevation.StartDato, Person.Initialer\ |
313 |
FROM Resevation\ |
FROM Resevation\ |
314 |
INNER JOIN Person ON Resevation.PersonID = Person.PersonID "; |
INNER JOIN Person ON Resevation.PersonID = Person.PersonID "; |
315 |
SQL += "WHERE Resevation.Stregkode = " + buffer[i].barcode + " ORDER BY STARTDATO ASC"; |
SQL += "WHERE Resevation.Stregkode = " + buffer[i].barcode + " ORDER BY STARTDATO ASC"; |
319 |
rs.MoveFirst(); |
rs.MoveFirst(); |
320 |
while ( !rs.IsEOF() ) { |
while ( !rs.IsEOF() ) { |
321 |
Reservation res; |
Reservation res; |
322 |
rs.GetFieldValue((short)0, res.startdate); |
rs.GetFieldValue((short)0, res.reservationID); |
323 |
rs.GetFieldValue(1, res.numdays); |
rs.GetFieldValue(1, res.startdate); |
324 |
rs.GetFieldValue(2, res.inits); |
rs.GetFieldValue(2, res.inits); |
325 |
|
|
326 |
buffer[i].reservations.push_back(res); |
buffer[i].reservations.push_back(res); |
353 |
} |
} |
354 |
} |
} |
355 |
} |
} |
356 |
|
//Developed by Torben H. Nielsen |
357 |
|
void DatabaseLayer::checkReservations(CString barcode) |
358 |
|
{ |
359 |
|
CString scount,status; |
360 |
|
CString SQL = "SELECT count(*) FROM Resevation WHERE Resevation.Stregkode = '" + barcode + "'"; |
361 |
|
CRecordset rs(&db); |
362 |
|
rs.Open(AFX_DB_USE_DEFAULT_TYPE,SQL); |
363 |
|
rs.MoveFirst(); |
364 |
|
rs.GetFieldValue((short)0,scount); |
365 |
|
int reservationCount = atoi(scount); |
366 |
|
rs.Close(); |
367 |
|
|
368 |
|
SQL = "SELECT status FROM Udstyr WHERE Udstyr.Stregkode = '" + barcode + "'"; |
369 |
|
rs.Open(AFX_DB_USE_DEFAULT_TYPE,SQL); |
370 |
|
if (rs.GetRecordCount() >0) { |
371 |
|
rs.MoveFirst(); |
372 |
|
rs.GetFieldValue((short)0,status); |
373 |
|
} |
374 |
|
rs.Close(); |
375 |
|
|
376 |
|
|
377 |
|
if (reservationCount > 0 && status == 3) {//equipment is available and has at lease one reservation |
378 |
|
SQL = "UPDATE Udstyr SET Status = 2 WHERE Stregkode = '" + barcode + "'"; |
379 |
|
db.ExecuteSQL(SQL); |
380 |
|
} |
381 |
|
} |
382 |
|
|
383 |
|
//Developed by Torben H. Nielsen |
384 |
|
bool DatabaseLayer::DeleteReservation(CString barcode, CString resid) |
385 |
|
{ |
386 |
|
CString SQL = "DELETE FROM Resevation WHERE resevationid = " + resid; |
387 |
|
db.ExecuteSQL(SQL); |
388 |
|
checkReservations(barcode); |
389 |
|
return true; |
390 |
|
} |
391 |
|
|