/[H6]/MicrosoftDBLayer.cpp
ViewVC logotype

Annotation of /MicrosoftDBLayer.cpp

Parent Directory Parent Directory | Revision Log Revision Log

Revision 54 - (hide annotations) (download)
Tue Sep 12 09:09:30 2006 UTC (17 years, 7 months ago) by torben
File size: 12647 byte(s) 
In the Search() function in the databaselayers we shouldn't waste time and space on finding
the previous checkouts and the current reservations - this should be done in getEquipment().

ResultDialog and ResultDetailsDialog now communicates throuh a CString barcode, insted of an index into the search result.

On an administrative action the ResultDetailsDialog now refreshes the data instead of closing the dialog.
1 torben 41 /*
2     * Primary developers: Hedin & Kevin
3     */
4    
5     #include "StdAfx.h"
6     #include ".\MicrosoftDBLayer.h"
7    
8    
9 torben 47 MicrosoftDBLayer::MicrosoftDBLayer(ConfigFile &config)
10 torben 41 {
11 torben 47 CString dsn;
12     dsn.Format("ODBC; Description=; DRIVER=SQL Server; SERVER=%s; UID=%s; PWD=%s", config.host, config.username, config.password);
13     db.OpenEx(dsn, CDatabase::noOdbcDialog);
14    
15     CString sql;
16     sql.Format("USE %s", config.database);
17     db.ExecuteSQL(sql);
18 torben 41 }
19    
20     MicrosoftDBLayer::~MicrosoftDBLayer(void)
21     {
22     db.Close();
23     }
24    
25     vector<Person> MicrosoftDBLayer::GetPersonAll(void)
26     {
27     vector<Person> buffer;
28     CString id, inits, name, isadmin, pass;
29    
30     CRecordset rs(&db);
31     rs.Open(AFX_DB_USE_DEFAULT_TYPE, "SELECT PersonID, Initialer, Navn, Admin, Pass FROM Person ORDER BY Initialer DESC");
32     while(!rs.IsEOF())
33     {
34     rs.GetFieldValue((short)0,id);
35     rs.GetFieldValue(1,inits);
36     rs.GetFieldValue(2,name);
37     rs.GetFieldValue(3,isadmin);
38     rs.GetFieldValue(4,pass);
39    
40     Person p;
41     p.id = id;
42     p.inits = inits;
43     p.name = name;
44     p.isadmin = (isadmin == 1);
45     p.pass = pass;
46    
47     buffer.push_back(p);
48    
49     rs.MoveNext();
50     }
51     rs.Close();
52     return buffer;
53     }
54    
55     Person MicrosoftDBLayer::GetPerson(CString wantInits)
56     {
57    
58     CString id, inits, name, isadmin, pass;
59     CString SQL;
60    
61     Person p;
62    
63     wantInits.Replace("'","\"");
64    
65     SQL = "SELECT PersonID, Initialer, Navn, Admin, Pass FROM Person WHERE (Initialer = '" + wantInits + "')";
66     CRecordset rs(&db);
67     rs.Open(AFX_DB_USE_DEFAULT_TYPE, SQL);
68    
69     if(!rs.IsEOF() )
70     {
71     rs.GetFieldValue((short)0,id);
72     rs.GetFieldValue(1,inits);
73     rs.GetFieldValue(2,name);
74     rs.GetFieldValue(3,isadmin);
75     rs.GetFieldValue(4,pass);
76    
77     p.id = id;
78     p.inits = inits;
79     p.name = name;
80     p.isadmin = (isadmin == "1");
81     p.pass = pass;
82     }
83    
84     return p;
85     }
86    
87     bool MicrosoftDBLayer::AddPerson(Person NewPerson)
88     {
89     CString SQL;
90     NewPerson.inits.Replace("'","\"");
91     NewPerson.name.Replace("'","\"");
92     NewPerson.pass.Replace("'","\"");
93     SQL.Format("INSERT into Person(Initialer, Navn, Admin, Pass) VALUES('%s', '%s', '%d', '%s')",NewPerson.inits, NewPerson.name, NewPerson.isadmin, NewPerson.pass);
94     db.ExecuteSQL(SQL);
95     return true;
96     }
97    
98     bool MicrosoftDBLayer::UpdatePerson(Person ChangePerson)
99     {
100     CString SQL;
101     ChangePerson.inits.Replace("'","\"");
102     ChangePerson.name.Replace("'","\"");
103     ChangePerson.pass.Replace("'","\"");
104     ChangePerson.id.Replace("'","\"");
105     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);
106     db.ExecuteSQL(SQL);
107     return true;
108     }
109    
110     bool MicrosoftDBLayer::DeletePerson(Person RemovePerson)
111     {
112     CString SQL;
113     RemovePerson.id.Replace("'","\"");
114     SQL.Format("DELETE FROM Person WHERE PersonID = '%s'", RemovePerson.id);
115     db.ExecuteSQL(SQL);
116     return true;
117     }
118    
119     bool MicrosoftDBLayer::ReturnEquipment(CString barcode)
120     {
121     CString SQL;
122     barcode.Replace("'","\"");
123     SQL.Format("UPDATE Udstyr SET Status = '3' WHERE Stregkode= '%s'", barcode);
124     db.ExecuteSQL(SQL);
125    
126     SQL.Format("UPDATE Udlån SET Afleveringsdato = Getdate() WHERE Stregkode= '%s' AND Afleveringsdato IS NULL ", barcode);
127     db.ExecuteSQL(SQL);
128    
129     checkReservations(barcode);
130     return true;
131     }
132    
133     bool MicrosoftDBLayer::CheckoutEquipment(Person CheckPersID, Equipment CheckEquip, int Numdays)
134     {
135     CString SQL;
136     CheckEquip.barcode.Replace("'","\"");
137     CheckPersID.id.Replace("'","\"");
138     SQL.Format("UPDATE Udstyr SET Status = '1' WHERE Stregkode = '%s' ", CheckEquip.barcode);
139     db.ExecuteSQL(SQL);
140     SQL.Format("INSERT INTO Udlån (PersonID, Stregkode, Startdato, Antaldage) VALUES( '%s','%s', GetDate(),'%d')", CheckPersID.id, CheckEquip.barcode, Numdays);
141     db.ExecuteSQL(SQL);
142     return true;
143     }
144    
145     bool MicrosoftDBLayer::EquipmentReservation(CString barcode, Person CheckPerson)
146     {
147     CString SQL;
148     CheckPerson.id.Replace("'","\"");
149     barcode.Replace("'","\"");
150     SQL.Format("INSERT INTO Resevation (PersonID, Stregkode, Startdato) VALUES( '%s','%s', GetDate())", CheckPerson.id, barcode);
151     db.ExecuteSQL(SQL);
152     checkReservations(barcode);
153     return true;
154     }
155    
156     bool MicrosoftDBLayer::UpdateEquipment(Equipment CheckEquip)
157     {
158     CString SQL;
159     CheckEquip.name.Replace("'","\"");
160     CheckEquip.description.Replace("'","\"");
161     CheckEquip.placement.Replace("'","\"");
162     CheckEquip.status.Replace("'","\"");
163     CheckEquip.barcode.Replace("'","\"");
164 torben 52 SQL.Format("UPDATE Udstyr SET Navn = '%s', Beskrivelse = '%s', Placering = '%s' WHERE Stregkode = '%s'", CheckEquip.name, CheckEquip.description, CheckEquip.placement, CheckEquip.barcode);
165 torben 41 db.ExecuteSQL(SQL);
166     return true;
167     }
168    
169     bool MicrosoftDBLayer::AddEquipment(Equipment AddEquip)
170     {
171     CString SQL;
172     AddEquip.barcode.Replace("'","\"");
173     AddEquip.name.Replace("'","\"");
174     AddEquip.description.Replace("'","\"");
175     AddEquip.placement.Replace("'","\"");
176     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");
177     db.ExecuteSQL(SQL);
178     return true;
179     }
180    
181     vector<Equipment> MicrosoftDBLayer::GetEquipmentAll(void)
182     {
183     vector<Equipment> buffer;
184     CString barcode, name, description, placement, status;
185    
186     CRecordset rs(&db);
187     rs.Open(AFX_DB_USE_DEFAULT_TYPE, "SELECT Stregkode, Navn, Beskrivelse, Placering, Status FROM Udstyr");
188     while(!rs.IsEOF())
189     {
190     rs.GetFieldValue((short)0,barcode);
191     rs.GetFieldValue(1,name);
192     rs.GetFieldValue(2,description);
193     rs.GetFieldValue(3,placement);
194     rs.GetFieldValue(4,status);
195    
196     Equipment p;
197     p.barcode = barcode;
198     p.name = name;
199     p.description = description;
200     p.placement = placement;
201     p.status = status;
202     buffer.push_back(p);
203    
204     rs.MoveNext();
205     }
206     rs.Close();
207     return buffer;
208     }
209    
210     Equipment MicrosoftDBLayer::GetEquipment(CString wantBarcode)
211     {
212    
213     CString barcode,name,description,placement,status;
214    
215     CString SQL;
216    
217     wantBarcode.Replace("'","\"");
218    
219     Equipment e;
220    
221     SQL.Format("SELECT Stregkode,Navn,Udstyr.Beskrivelse,Placering,Status.Beskrivelse FROM Udstyr INNER JOIN Status ON Udstyr.Status = Status.StatusID Where (Stregkode = '%s')", wantBarcode);
222     CRecordset rs(&db);
223     rs.Open(AFX_DB_USE_DEFAULT_TYPE, SQL);
224     if (!rs.IsEOF() )
225     {
226     rs.GetFieldValue((short)0,barcode);
227     rs.GetFieldValue(1,name);
228     rs.GetFieldValue(2,description);
229     rs.GetFieldValue(3,placement);
230     rs.GetFieldValue(4,status);
231    
232     e.barcode = barcode;
233     e.name = name;
234     e.description = description;
235     e.placement = placement;
236     e.status = status;
237 torben 54
238     rs.Close();
239    
240     //fill Equipment::checkouts
241     SQL = "SELECT TOP 10 Udlån.StartDato, Udlån.Afleveringsdato,Udlån.Antaldage, Person.Initialer\
242     FROM Udlån\
243     INNER JOIN Person ON Udlån.PersonID = Person.PersonID ";
244     SQL += "WHERE Udlån.Stregkode = " + barcode + " ORDER BY STARTDATO DESC";
245     CRecordset rs(&db);
246     rs.Open(AFX_DB_USE_DEFAULT_TYPE, SQL);
247     if (rs.GetRecordCount()>0) {
248     rs.MoveFirst();
249     while ( !rs.IsEOF() ) {
250     Checkout check;
251     rs.GetFieldValue((short)0, check.startdate);
252     rs.GetFieldValue(1, check.enddate);
253     rs.GetFieldValue(2, check.numdays);
254     rs.GetFieldValue(3, check.inits);
255    
256     e.checkouts.push_back(check);
257     rs.MoveNext();
258     }
259     }
260     rs.Close();
261    
262     // fill Equipment::reservations
263     SQL = "SELECT Resevation.ResevationID, Resevation.StartDato, Person.Initialer\
264     FROM Resevation\
265     INNER JOIN Person ON Resevation.PersonID = Person.PersonID ";
266     SQL += "WHERE Resevation.Stregkode = " + barcode + " ORDER BY STARTDATO ASC";
267    
268     rs.Open(AFX_DB_USE_DEFAULT_TYPE, SQL);
269     if (rs.GetRecordCount()>0) {
270     rs.MoveFirst();
271     while ( !rs.IsEOF() ) {
272     Reservation res;
273     rs.GetFieldValue((short)0, res.reservationID);
274     rs.GetFieldValue(1, res.startdate);
275     rs.GetFieldValue(2, res.inits);
276    
277     e.reservations.push_back(res);
278     rs.MoveNext();
279     }
280     }
281     rs.Close();
282 torben 41 }
283 torben 54
284 torben 41 return e;
285     }
286    
287     bool MicrosoftDBLayer::DeleteEquipment(Equipment DelEquip)
288     {
289     CString SQL;
290     DelEquip.barcode.Replace("'","\"");
291     SQL.Format("DELETE FROM Udstyr WHERE Stregkode = '%s'", DelEquip.barcode);
292     db.ExecuteSQL(SQL);
293     return true;
294     }
295    
296    
297     // Developed by Torben H. Nielsen
298     vector<Equipment> MicrosoftDBLayer::Search(CString barcode, CString name, CString inits, bool available, bool reserved, bool deposited)
299     {
300     vector<Equipment> buffer;
301    
302     barcode.Replace("'","\"");
303     name.Replace("'","\"");
304     inits.Replace("'","\"");
305    
306     //here are the wanted equipments found through 3 seperate queries - I could also
307     //find them all in one query where I joined the 3 selects via a UNION
308     CString SQL;
309     if (available == true) {
310     SQL = "SELECT DISTINCT Udstyr.stregkode,Udstyr.Navn,Udstyr.Beskrivelse,Udstyr.Placering,Status.Beskrivelse \
311     FROM Udstyr \
312     INNER JOIN Status ON Udstyr.status = Status.StatusID WHERE Udstyr.Status = 3 ";
313     if (barcode != "")
314     SQL += " AND Udstyr.stregkode = '" + barcode + "' ";
315     if (name != "")
316     SQL += " AND Udstyr.Navn LIKE '%" + name + "%' ";
317    
318     CRecordset rs(&db);
319     rs.Open(AFX_DB_USE_DEFAULT_TYPE, SQL);
320     FillEquipmentVector(buffer,rs);
321     rs.Close();
322     }
323    
324     if (reserved == true) {
325     SQL = "SELECT DISTINCT Udstyr.stregkode,Udstyr.Navn,Udstyr.Beskrivelse,Udstyr.Placering,Status.Beskrivelse \r\n\
326     FROM Udstyr \r\n\
327     INNER JOIN Status ON Udstyr.status = Status.StatusID \r\n\
328     INNER JOIN Resevation ON Udstyr.Stregkode = Resevation.stregkode \r\n\
329     INNER JOIN Person ON Resevation.PersonID = Person.PersonID\r\n\
330     WHERE ( Udstyr.Status = 2 OR Person.Initialer = '" + inits + "') ";
331     if (barcode != "")
332     SQL += " AND Udstyr.stregkode = '" + barcode + "' ";
333     if (name != "")
334     SQL += " AND Udstyr.Navn LIKE '%" + name + "%' ";
335     //if (inits != "")
336     // SQL += " AND Person.Initialer = '" + inits + "' ";
337    
338     CRecordset rs(&db);
339     rs.Open(AFX_DB_USE_DEFAULT_TYPE, SQL);
340     FillEquipmentVector(buffer,rs);
341     rs.Close();
342     }
343    
344    
345     if (deposited == true) {
346     SQL = "SELECT DISTINCT Udstyr.stregkode,Udstyr.Navn,Udstyr.Beskrivelse,Udstyr.Placering,Status.Beskrivelse \r\n\
347     FROM Udstyr \r\n\
348     INNER JOIN Status ON Udstyr.status = Status.StatusID \r\n\
349     INNER JOIN Udlån ON Udstyr.Stregkode = Udlån.stregkode \r\n\
350     INNER JOIN Person ON Udlån.PersonID = person.PersonID\r\n\
351     WHERE Udstyr.Status = 1 \r\n\
352     AND Udlån.Afleveringsdato IS NULL";
353     if (barcode != "")
354     SQL += " AND Udstyr.stregkode = '" + barcode + "' ";
355     if (name != "")
356     SQL += " AND Udstyr.Navn LIKE '%" + name + "%' ";
357     if (inits != "")
358     SQL += " AND Person.Initialer = '" + inits + "' ";
359    
360     CRecordset rs(&db);
361     rs.Open(AFX_DB_USE_DEFAULT_TYPE, SQL);
362     FillEquipmentVector(buffer,rs);
363     rs.Close();
364     }
365    
366     return buffer;
367     }
368    
369     // Developed by Torben H. Nielsen
370     void MicrosoftDBLayer::FillEquipmentVector(vector<Equipment> &buffer, CRecordset &rs)
371     {
372     CString barcode,name,description,placement,status;
373     if (rs.GetRecordCount()>0) {
374     rs.MoveFirst();
375     while (!rs.IsEOF() ) {
376     Equipment eq;
377     rs.GetFieldValue((short)0, eq.barcode);
378     rs.GetFieldValue(1, eq.name);
379     rs.GetFieldValue(2, eq.description);
380     rs.GetFieldValue(3, eq.placement);
381     rs.GetFieldValue(4, eq.status);
382    
383     buffer.push_back(eq);
384    
385     rs.MoveNext();
386     }
387     }
388     }
389     //Developed by Torben H. Nielsen
390     void MicrosoftDBLayer::checkReservations(CString barcode)
391     {
392     CString scount,status;
393     barcode.Replace("'","\"");
394     CString SQL = "SELECT count(*) FROM Resevation WHERE Resevation.Stregkode = '" + barcode + "'";
395     CRecordset rs(&db);
396     rs.Open(AFX_DB_USE_DEFAULT_TYPE,SQL);
397     rs.MoveFirst();
398     rs.GetFieldValue((short)0,scount);
399     int reservationCount = atoi(scount);
400     rs.Close();
401    
402     SQL = "SELECT status FROM Udstyr WHERE Udstyr.Stregkode = '" + barcode + "'";
403     rs.Open(AFX_DB_USE_DEFAULT_TYPE,SQL);
404     if (rs.GetRecordCount() >0) {
405     rs.MoveFirst();
406     rs.GetFieldValue((short)0,status);
407     }
408     rs.Close();
409    
410     if (reservationCount > 0 && status == "3") {//equipment is available and has at lease one reservation
411     SQL = "UPDATE Udstyr SET Status = 2 WHERE Stregkode = '" + barcode + "'";
412     db.ExecuteSQL(SQL);
413     }
414     }
415    
416     //Developed by Torben H. Nielsen
417     bool MicrosoftDBLayer::DeleteReservation(CString barcode, CString resid)
418     {
419     barcode.Replace("'","\"");
420     resid.Replace("'","\"");
421     CString SQL = "DELETE FROM Resevation WHERE resevationid = " + resid;
422     db.ExecuteSQL(SQL);
423     checkReservations(barcode);
424     return true;
425     }
426    
  ViewVC Help
Powered by ViewVC 1.1.20