viewvc/H6/MySQLLayer.cpp


#include "StdAfx.h"
#include ".\MySQLLayer.h"


MySQLLayer::MySQLLayer(ConfigFile &config)
{
        CString dsn;
        dsn.Format("ODBC;Description=asd;DRIVER=MySQL ODBC 3.51 Driver;SERVER=%s;user=%s;password=%s", config.host, config.username, config.password);
        db.OpenEx(dsn, CDatabase::noOdbcDialog);

        CString sql;
        sql.Format("USE %s", config.database);
        db.ExecuteSQL( sql );
}

MySQLLayer::~MySQLLayer(void)
{
        db.Close();
}


// Developed by Torben H. Nielsen
vector<Equipment> MySQLLayer::Search(CString barcode, CString name, CString inits, bool available, bool reserved, bool deposited)
{
        vector<Equipment> buffer;

        barcode.Replace("'","\"");
        name.Replace("'","\"");
        inits.Replace("'","\"");

        //here are the wanted equipments found through 3 seperate queries - I could also 
        //find them all in one query where I joined the 3 selects via a UNION
        CString SQL;
        if (available == true) {
                SQL = "SELECT DISTINCT Udstyr.stregkode,Udstyr.Navn,Udstyr.Beskrivelse,Udstyr.Placering,Status.Beskrivelse \
                                   FROM Udstyr \
                                   INNER JOIN Status ON Udstyr.status = Status.StatusID WHERE Udstyr.Status = 3 ";
                if (barcode != "")
                        SQL += " AND Udstyr.stregkode = '" + barcode + "' ";
                if (name != "")
                        SQL += " AND Udstyr.Navn LIKE '%" + name + "%' ";
                
                CRecordset rs(&db);
                rs.Open(AFX_DB_USE_DEFAULT_TYPE, SQL);
                FillEquipmentVector(buffer,rs);
                rs.Close();
        }
        
        if (reserved == true) {
                SQL = "SELECT DISTINCT Udstyr.stregkode,Udstyr.Navn,Udstyr.Beskrivelse,Udstyr.Placering,Status.Beskrivelse \r\n\
                                   FROM Udstyr \r\n\
                                   INNER JOIN Status ON Udstyr.status = Status.StatusID \r\n\
                                   INNER JOIN Resevation ON Udstyr.Stregkode = Resevation.stregkode \r\n\
                                   INNER JOIN Person ON Resevation.PersonID = Person.PersonID\r\n\
                                   WHERE ( Udstyr.Status = 2 OR Person.Initialer = '" + inits + "') ";
                if (barcode != "")
                        SQL += " AND Udstyr.stregkode = '" + barcode + "' ";
                if (name != "")
                        SQL += " AND Udstyr.Navn LIKE '%" + name + "%' ";
                //if (inits != "")
                //      SQL += " AND Person.Initialer = '" + inits + "' ";
                
                CRecordset rs(&db);
                rs.Open(AFX_DB_USE_DEFAULT_TYPE, SQL);
                FillEquipmentVector(buffer,rs);
                rs.Close();
        }


        if (deposited == true) {
                SQL = "SELECT DISTINCT Udstyr.stregkode,Udstyr.Navn,Udstyr.Beskrivelse,Udstyr.Placering,Status.Beskrivelse \
                                   FROM Udstyr \
                                   INNER JOIN Status ON Udstyr.status = Status.StatusID \
                                   INNER JOIN Udl�n ON Udstyr.Stregkode = Udl�n.stregkode \
                                   INNER JOIN Person ON Udl�n.PersonID = Person.PersonID \
                                   WHERE Udstyr.Status = 1 \
                                   AND Udl�n.Afleveringsdato IS NULL";
                if (barcode != "")
                        SQL += " AND Udstyr.stregkode = '" + barcode + "' ";
                if (name != "")
                        SQL += " AND Udstyr.Navn LIKE '%" + name + "%' ";
                if (inits != "")
                        SQL += " AND Person.Initialer = '" + inits + "' ";

                CRecordset rs(&db);
                rs.Open(AFX_DB_USE_DEFAULT_TYPE, SQL);
                FillEquipmentVector(buffer,rs);
                rs.Close();
        }

        return buffer;
}

CString MySQLLayer::GenerateQueryForLatestCheckouts( CString barcode )
{
        CString SQL;

        SQL = "SELECT Udl�n.StartDato, Udl�n.Afleveringsdato,Udl�n.Antaldage, Person.Initialer\
                  FROM Udl�n\
                  INNER JOIN Person ON Udl�n.PersonID = Person.PersonID ";
        SQL += "WHERE Udl�n.Stregkode = " + barcode + " ORDER BY STARTDATO DESC LIMIT 10";      return SQL;

        return SQL;

}
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			}