/[H6]/MicrosoftDBLayer.cpp
ViewVC logotype

Contents of /MicrosoftDBLayer.cpp

Parent Directory Parent Directory | Revision Log Revision Log


Revision 60 - (show annotations) (download)
Mon Oct 22 13:00:14 2007 UTC (16 years, 5 months ago) by torben
File size: 3519 byte(s)
Refactored the database layer classes. Pushed most of the functions to the parent class.
1 /*
2 * Primary developers: Hedin & Kevin
3 */
4
5 #include "StdAfx.h"
6 #include ".\MicrosoftDBLayer.h"
7
8
9 MicrosoftDBLayer::MicrosoftDBLayer(ConfigFile &config)
10 {
11 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 }
19
20 MicrosoftDBLayer::~MicrosoftDBLayer(void)
21 {
22 db.Close();
23 }
24
25
26
27
28
29 // Developed by Torben H. Nielsen
30 vector<Equipment> MicrosoftDBLayer::Search(CString barcode, CString name, CString inits, bool available, bool reserved, bool deposited)
31 {
32 vector<Equipment> buffer;
33
34 barcode.Replace("'","\"");
35 name.Replace("'","\"");
36 inits.Replace("'","\"");
37
38 //here are the wanted equipments found through 3 seperate queries - I could also
39 //find them all in one query where I joined the 3 selects via a UNION
40 CString SQL;
41 if (available == true) {
42 SQL = "SELECT DISTINCT Udstyr.stregkode,Udstyr.Navn,Udstyr.Beskrivelse,Udstyr.Placering,Status.Beskrivelse \
43 FROM Udstyr \
44 INNER JOIN Status ON Udstyr.status = Status.StatusID WHERE Udstyr.Status = 3 ";
45 if (barcode != "")
46 SQL += " AND Udstyr.stregkode = '" + barcode + "' ";
47 if (name != "")
48 SQL += " AND Udstyr.Navn LIKE '%" + name + "%' ";
49
50 CRecordset rs(&db);
51 rs.Open(AFX_DB_USE_DEFAULT_TYPE, SQL);
52 FillEquipmentVector(buffer,rs);
53 rs.Close();
54 }
55
56 if (reserved == true) {
57 SQL = "SELECT DISTINCT Udstyr.stregkode,Udstyr.Navn,Udstyr.Beskrivelse,Udstyr.Placering,Status.Beskrivelse \r\n\
58 FROM Udstyr \r\n\
59 INNER JOIN Status ON Udstyr.status = Status.StatusID \r\n\
60 INNER JOIN Resevation ON Udstyr.Stregkode = Resevation.stregkode \r\n\
61 INNER JOIN Person ON Resevation.PersonID = Person.PersonID\r\n\
62 WHERE ( Udstyr.Status = 2 OR Person.Initialer = '" + inits + "') ";
63 if (barcode != "")
64 SQL += " AND Udstyr.stregkode = '" + barcode + "' ";
65 if (name != "")
66 SQL += " AND Udstyr.Navn LIKE '%" + name + "%' ";
67 //if (inits != "")
68 // SQL += " AND Person.Initialer = '" + inits + "' ";
69
70 CRecordset rs(&db);
71 rs.Open(AFX_DB_USE_DEFAULT_TYPE, SQL);
72 FillEquipmentVector(buffer,rs);
73 rs.Close();
74 }
75
76
77 if (deposited == true) {
78 SQL = "SELECT DISTINCT Udstyr.stregkode,Udstyr.Navn,Udstyr.Beskrivelse,Udstyr.Placering,Status.Beskrivelse \r\n\
79 FROM Udstyr \r\n\
80 INNER JOIN Status ON Udstyr.status = Status.StatusID \r\n\
81 INNER JOIN Udlån ON Udstyr.Stregkode = Udlån.stregkode \r\n\
82 INNER JOIN Person ON Udlån.PersonID = person.PersonID\r\n\
83 WHERE Udstyr.Status = 1 \r\n\
84 AND Udlån.Afleveringsdato IS NULL";
85 if (barcode != "")
86 SQL += " AND Udstyr.stregkode = '" + barcode + "' ";
87 if (name != "")
88 SQL += " AND Udstyr.Navn LIKE '%" + name + "%' ";
89 if (inits != "")
90 SQL += " AND Person.Initialer = '" + inits + "' ";
91
92 CRecordset rs(&db);
93 rs.Open(AFX_DB_USE_DEFAULT_TYPE, SQL);
94 FillEquipmentVector(buffer,rs);
95 rs.Close();
96 }
97
98 return buffer;
99 }
100
101 CString MicrosoftDBLayer::GenerateQueryForLatestCheckouts( CString barcode )
102 {
103 CString SQL;
104 SQL = "SELECT TOP 10 Udlån.StartDato, Udlån.Afleveringsdato,Udlån.Antaldage, Person.Initialer\
105 FROM Udlån\
106 INNER JOIN Person ON Udlån.PersonID = Person.PersonID ";
107 SQL += "WHERE Udlån.Stregkode = " + barcode + " ORDER BY STARTDATO DESC";
108 return SQL;
109
110 }
111
112
113
114

  ViewVC Help
Powered by ViewVC 1.1.20