/[H6]/PostgresLayer.cpp
ViewVC logotype

Contents of /PostgresLayer.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: 3433 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 ".\PostgresLayer.h"
7
8
9 PostgresLayer::PostgresLayer(ConfigFile &config)
10 {
11 CString dsn;
12 dsn.Format("ODBC;Description=asd;DRIVER=PostgreSQL ANSI;SERVER=%s; uid=%s;password=%s;database=%s",config.host, config.username, config.password, config.database);
13 db.OpenEx(dsn, CDatabase::noOdbcDialog);
14 }
15
16 PostgresLayer::~PostgresLayer(void)
17 {
18 db.Close();
19 }
20
21
22
23 // Developed by Torben H. Nielsen
24 vector<Equipment> PostgresLayer::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 ILIKE '%" + 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 ILIKE '" + inits + "') ";
57 if (barcode != "")
58 SQL += " AND Udstyr.stregkode = '" + barcode + "' ";
59 if (name != "")
60 SQL += " AND Udstyr.Navn ILIKE '%" + 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 ILIKE '%" + name + "%' ";
83 if (inits != "")
84 SQL += " AND Person.Initialer ILIKE '" + 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 PostgresLayer::GenerateQueryForLatestCheckouts( CString barcode )
96 {
97 CString SQL;
98 SQL = "SELECT Udlån.StartDato, Udlån.Afleveringsdato,Udlån.Antaldage, Person.Initialer\
99 FROM Udlån\
100 INNER JOIN Person ON Udlån.PersonID = Person.PersonID ";
101 SQL += "WHERE Udlån.Stregkode = " + barcode + " ORDER BY STARTDATO DESC LIMIT 10";
102
103 return SQL;
104 }

  ViewVC Help
Powered by ViewVC 1.1.20