/[H6]/MySQLLayer.cpp
ViewVC logotype

Annotation of /MySQLLayer.cpp

Parent Directory Parent Directory | Revision Log Revision Log


Revision 60 - (hide annotations) (download)
Mon Oct 22 13:00:14 2007 UTC (16 years, 5 months ago) by torben
File size: 3429 byte(s)
Refactored the database layer classes. Pushed most of the functions to the parent class.
1 torben 41
2     #include "StdAfx.h"
3     #include ".\MySQLLayer.h"
4    
5    
6 torben 47 MySQLLayer::MySQLLayer(ConfigFile &config)
7 torben 41 {
8 torben 47 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 torben 41 }
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 torben 60 CString MySQLLayer::GenerateQueryForLatestCheckouts( CString barcode )
96 torben 41 {
97 torben 60 CString SQL;
98 torben 41
99 torben 60 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 torben 41
104 torben 60 return SQL;
105 torben 41
106     }

  ViewVC Help
Powered by ViewVC 1.1.20