6 |
|
|
7 |
DatabaseLayer::DatabaseLayer(void) |
DatabaseLayer::DatabaseLayer(void) |
8 |
{ |
{ |
9 |
db.OpenEx("ODBC; Description=; DRIVER=SQL Server; SERVER=TK-Teacher; UID=GroupTwo; PWD=toog30"); |
db.OpenEx("ODBC; Description=; DRIVER=SQL Server; SERVER=TK-Teacher; UID=GroupTwo; PWD=toog30", CDatabase::noOdbcDialog); |
10 |
db.ExecuteSQL("USE GroupTwo"); |
db.ExecuteSQL("USE GroupTwo"); |
11 |
} |
} |
12 |
|
|
106 |
|
|
107 |
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); |
108 |
db.ExecuteSQL(SQL); |
db.ExecuteSQL(SQL); |
109 |
|
|
110 |
|
checkReservations(barcode); |
111 |
return true; |
return true; |
112 |
} |
} |
113 |
|
|
121 |
return true; |
return true; |
122 |
} |
} |
123 |
|
|
124 |
bool DatabaseLayer::EquipmentReservation(Person CheckPersID, Equipment CheckEquip, int Numdays) |
bool DatabaseLayer::EquipmentReservation(CString barcode, Person CheckPerson) |
125 |
{ |
{ |
126 |
CString SQL; |
CString SQL; |
127 |
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); |
|
128 |
db.ExecuteSQL(SQL); |
db.ExecuteSQL(SQL); |
129 |
|
checkReservations(barcode); |
130 |
return true; |
return true; |
131 |
} |
} |
132 |
|
|
219 |
{ |
{ |
220 |
vector<Equipment> buffer; |
vector<Equipment> buffer; |
221 |
|
|
222 |
|
//here are the wanted equipments found through 3 seperate queries - I could also |
223 |
|
//find them all in one query where I joined the 3 selects via a UNION |
224 |
CString SQL; |
CString SQL; |
225 |
if (available == true) { |
if (available == true) { |
226 |
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 \ |
243 |
INNER JOIN Status ON Udstyr.status = Status.StatusID \r\n\ |
INNER JOIN Status ON Udstyr.status = Status.StatusID \r\n\ |
244 |
INNER JOIN Resevation ON Udstyr.Stregkode = Resevation.stregkode \r\n\ |
INNER JOIN Resevation ON Udstyr.Stregkode = Resevation.stregkode \r\n\ |
245 |
INNER JOIN Person ON Resevation.PersonID = Person.PersonID\r\n\ |
INNER JOIN Person ON Resevation.PersonID = Person.PersonID\r\n\ |
246 |
WHERE Udstyr.Status = 2 "; |
WHERE ( Udstyr.Status = 2 OR Person.Initialer = '" + inits + "') "; |
247 |
if (barcode != "") |
if (barcode != "") |
248 |
SQL += " AND Udstyr.stregkode = '" + barcode + "' "; |
SQL += " AND Udstyr.stregkode = '" + barcode + "' "; |
249 |
if (name != "") |
if (name != "") |
250 |
SQL += " AND Udstyr.Navn LIKE '%" + name + "%' "; |
SQL += " AND Udstyr.Navn LIKE '%" + name + "%' "; |
251 |
if (inits != "") |
//if (inits != "") |
252 |
SQL += " AND Person.Initialer = '" + inits + "' "; |
// SQL += " AND Person.Initialer = '" + inits + "' "; |
253 |
|
|
254 |
CRecordset rs(&db); |
CRecordset rs(&db); |
255 |
rs.Open(AFX_DB_USE_DEFAULT_TYPE, SQL); |
rs.Open(AFX_DB_USE_DEFAULT_TYPE, SQL); |
279 |
rs.Close(); |
rs.Close(); |
280 |
} |
} |
281 |
|
|
282 |
|
// now I have the equipment rows the user asked for, now I need some more checkout and |
283 |
|
// reservation data for each piece of equipment |
284 |
|
|
285 |
for (int i=0; i<buffer.size(); i++) { |
for (int i=0; i<buffer.size(); i++) { |
286 |
//fill Equipment::checkouts |
//fill Equipment::checkouts |
287 |
|
|
307 |
rs.Close(); |
rs.Close(); |
308 |
|
|
309 |
// fill Equipment::reservations |
// fill Equipment::reservations |
310 |
SQL = "SELECT Resevation.StartDato, Resevation.Antaldage, Person.Initialer\ |
SQL = "SELECT Resevation.ResevationID, Resevation.StartDato, Person.Initialer\ |
311 |
FROM Resevation\ |
FROM Resevation\ |
312 |
INNER JOIN Person ON Resevation.PersonID = Person.PersonID "; |
INNER JOIN Person ON Resevation.PersonID = Person.PersonID "; |
313 |
SQL += "WHERE Resevation.Stregkode = " + buffer[i].barcode + " ORDER BY STARTDATO ASC"; |
SQL += "WHERE Resevation.Stregkode = " + buffer[i].barcode + " ORDER BY STARTDATO ASC"; |
317 |
rs.MoveFirst(); |
rs.MoveFirst(); |
318 |
while ( !rs.IsEOF() ) { |
while ( !rs.IsEOF() ) { |
319 |
Reservation res; |
Reservation res; |
320 |
rs.GetFieldValue((short)0, res.startdate); |
rs.GetFieldValue((short)0, res.reservationID); |
321 |
rs.GetFieldValue(1, res.numdays); |
rs.GetFieldValue(1, res.startdate); |
322 |
rs.GetFieldValue(2, res.inits); |
rs.GetFieldValue(2, res.inits); |
323 |
|
|
324 |
buffer[i].reservations.push_back(res); |
buffer[i].reservations.push_back(res); |
351 |
} |
} |
352 |
} |
} |
353 |
} |
} |
354 |
|
|
355 |
|
void DatabaseLayer::checkReservations(CString barcode) |
356 |
|
{ |
357 |
|
CString scount,status; |
358 |
|
CString SQL = "SELECT count(*) FROM Resevation WHERE Resevation.Stregkode = '" + barcode + "'"; |
359 |
|
CRecordset rs(&db); |
360 |
|
rs.Open(AFX_DB_USE_DEFAULT_TYPE,SQL); |
361 |
|
rs.MoveFirst(); |
362 |
|
rs.GetFieldValue((short)0,scount); |
363 |
|
int reservationCount = atoi(scount); |
364 |
|
rs.Close(); |
365 |
|
|
366 |
|
SQL = "SELECT status FROM Udstyr WHERE Udstyr.Stregkode = '" + barcode + "'"; |
367 |
|
rs.Open(AFX_DB_USE_DEFAULT_TYPE,SQL); |
368 |
|
if (rs.GetRecordCount() >0) { |
369 |
|
rs.MoveFirst(); |
370 |
|
rs.GetFieldValue((short)0,status); |
371 |
|
} |
372 |
|
rs.Close(); |
373 |
|
|
374 |
|
|
375 |
|
if (reservationCount > 0 && status == 3) {//equipment is available and has at lease one reservation |
376 |
|
SQL = "UPDATE Udstyr SET Status = 2 WHERE Stregkode = '" + barcode + "'"; |
377 |
|
db.ExecuteSQL(SQL); |
378 |
|
} |
379 |
|
} |
380 |
|
|
381 |
|
bool DatabaseLayer::DeleteReservation(CString barcode, CString resid) |
382 |
|
{ |
383 |
|
CString SQL = "DELETE FROM Resevation WHERE resevationid = " + resid; |
384 |
|
db.ExecuteSQL(SQL); |
385 |
|
checkReservations(barcode); |
386 |
|
return true; |
387 |
|
} |
388 |
|
|