/[H6]/MySQLLayer.cpp
ViewVC logotype

Contents of /MySQLLayer.cpp

Parent Directory Parent Directory | Revision Log Revision Log


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

  ViewVC Help
Powered by ViewVC 1.1.20