viewvc/H6/PostgresLayer.cpp

/*
 * Primary developers: Hedin & Kevin
 */

#include "StdAfx.h"
#include ".\PostgresLayer.h"


PostgresLayer::PostgresLayer(ConfigFile &config)
{
        CString dsn;
        dsn.Format("ODBC;Description=asd;DRIVER=PostgreSQL ANSI;SERVER=%s; uid=%s;password=%s;database=%s",config.host, config.username, config.password, config.database);
        db.OpenEx(dsn, CDatabase::noOdbcDialog);
}

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

vector<Person> PostgresLayer::GetPersonAll(void)
{
        vector<Person> buffer;
        CString id, inits, name, isadmin, pass;

                CRecordset rs(&db);
                rs.Open(AFX_DB_USE_DEFAULT_TYPE, "SELECT PersonID, Initialer, Navn, Admin, Pass FROM Person ORDER BY Initialer DESC");
                while(!rs.IsEOF())
                {
                        rs.GetFieldValue((short)0,id);
                        rs.GetFieldValue(1,inits);
                        rs.GetFieldValue(2,name);
                        rs.GetFieldValue(3,isadmin);
                        rs.GetFieldValue(4,pass);
                        
                        Person p;
                        p.id            = id;
                        p.inits         = inits;
                        p.name          = name;
                        p.isadmin       = (isadmin == 1);
                        p.pass          = pass;

                        buffer.push_back(p);

                        rs.MoveNext();
                }
                rs.Close();
        return buffer;
}

Person PostgresLayer::GetPerson(CString wantInits)
{

        CString id, inits, name, isadmin, pass;
        CString SQL;

        Person p;
        
        wantInits.Replace("'","\"");

        SQL = "SELECT PersonID, Initialer, Navn, Admin, Pass FROM Person WHERE (Initialer ILIKE '" + wantInits + "')";
        CRecordset rs(&db);
        rs.Open(AFX_DB_USE_DEFAULT_TYPE, SQL);
        
        if(!rs.IsEOF() )
        {
                        rs.GetFieldValue((short)0,id);
                        rs.GetFieldValue(1,inits);
                        rs.GetFieldValue(2,name);
                        rs.GetFieldValue(3,isadmin);
                        rs.GetFieldValue(4,pass);
                        
                        p.id            = id;
                        p.inits         = inits;
                        p.name          = name;
                        p.isadmin       = (isadmin == "1");
                        p.pass          = pass;
        }
        
        return p;
}

bool PostgresLayer::AddPerson(Person NewPerson)
{
        CString SQL;
        NewPerson.inits.Replace("'","\"");
        NewPerson.name.Replace("'","\"");
        NewPerson.pass.Replace("'","\"");
        SQL.Format("INSERT into Person(Initialer, Navn, Admin, Pass) VALUES('%s', '%s', '%d', '%s')",
                NewPerson.inits, NewPerson.name, NewPerson.isadmin, NewPerson.pass);

        db.ExecuteSQL(SQL);
        return true;
}

bool PostgresLayer::UpdatePerson(Person ChangePerson)
{
        CString SQL;
        ChangePerson.inits.Replace("'","\"");
        ChangePerson.name.Replace("'","\"");
        ChangePerson.pass.Replace("'","\"");
        ChangePerson.id.Replace("'","\"");
        SQL.Format("UPDATE Person SET Initialer = '%s', Navn = '%s', Pass = '%s', Admin = '%d' WHERE PersonID = '%s'", 
                ChangePerson.inits, ChangePerson.name, ChangePerson.pass, ChangePerson.isadmin, ChangePerson.id);

        db.ExecuteSQL(SQL);
        return true;
}

bool PostgresLayer::DeletePerson(Person RemovePerson)
{
        CString SQL;
        RemovePerson.id.Replace("'","\"");
        SQL.Format("DELETE FROM Person WHERE PersonID = '%s'", RemovePerson.id);
        db.ExecuteSQL(SQL);
        return true;
}

bool PostgresLayer::ReturnEquipment(CString barcode)
{
        CString SQL;
        barcode.Replace("'","\"");
        SQL.Format("UPDATE Udstyr SET Status = '3' WHERE Stregkode= '%s'", barcode);
        db.ExecuteSQL(SQL);
        
        SQL.Format("UPDATE Udl�n SET Afleveringsdato = now() WHERE Stregkode= '%s' AND Afleveringsdato IS NULL ", barcode);
        db.ExecuteSQL(SQL);

        checkReservations(barcode);
        return true;
}

bool PostgresLayer::CheckoutEquipment(Person CheckPersID, Equipment CheckEquip, int Numdays)
{
        CString SQL;
        CheckEquip.barcode.Replace("'","\"");
        CheckPersID.id.Replace("'","\"");
        SQL.Format("UPDATE Udstyr SET Status = '1' WHERE Stregkode = '%s' ", CheckEquip.barcode);
        db.ExecuteSQL(SQL);
        SQL.Format("INSERT INTO Udl�n (PersonID, Stregkode, Startdato, Antaldage) VALUES( '%s','%s', now(),'%d')", CheckPersID.id, CheckEquip.barcode, Numdays);
        db.ExecuteSQL(SQL);
        return true;
}

bool PostgresLayer::EquipmentReservation(CString barcode, Person CheckPerson)
{
        CString SQL;
        CheckPerson.id.Replace("'","\"");
        barcode.Replace("'","\"");
        SQL.Format("INSERT INTO Resevation (PersonID, Stregkode, Startdato) VALUES( '%s','%s', now())", CheckPerson.id, barcode);
        db.ExecuteSQL(SQL);
        checkReservations(barcode);
        return true;
}

bool PostgresLayer::UpdateEquipment(Equipment CheckEquip)
{
        CString SQL;
        CheckEquip.name.Replace("'","\"");
        CheckEquip.description.Replace("'","\"");
        CheckEquip.placement.Replace("'","\"");
        CheckEquip.status.Replace("'","\"");
        CheckEquip.barcode.Replace("'","\"");
        SQL.Format("UPDATE Udstyr SET Navn = '%s', Beskrivelse = '%s', Placering = '%s' WHERE Stregkode = '%s'", CheckEquip.name, CheckEquip.description, CheckEquip.placement, CheckEquip.barcode);
        db.ExecuteSQL(SQL);
        return true;
}

bool PostgresLayer::AddEquipment(Equipment AddEquip)
{
        CString SQL;
        AddEquip.barcode.Replace("'","\"");
        AddEquip.name.Replace("'","\"");
        AddEquip.description.Replace("'","\"");
        AddEquip.placement.Replace("'","\"");
        SQL.Format("INSERT into Udstyr(Stregkode, Navn, Beskrivelse, Placering, Status) VALUES('%s', '%s', '%s', '%s', '%s')",AddEquip.barcode, AddEquip.name, AddEquip.description, AddEquip.placement, "3");
        db.ExecuteSQL(SQL);
        return true;
}

vector<Equipment> PostgresLayer::GetEquipmentAll(void)
{
        vector<Equipment> buffer;
        CString barcode, name, description, placement, status;

        CRecordset rs(&db);
        rs.Open(AFX_DB_USE_DEFAULT_TYPE, "SELECT Stregkode, Navn, Beskrivelse, Placering, Status FROM Udstyr");
        while(!rs.IsEOF())
        {
                rs.GetFieldValue((short)0,barcode);
                rs.GetFieldValue(1,name);
                rs.GetFieldValue(2,description);
                rs.GetFieldValue(3,placement);
                rs.GetFieldValue(4,status);
                
                Equipment p;
                p.barcode               = barcode;
                p.name                  = name;
                p.description   = description;
                p.placement             = placement;
                p.status                = status;
                buffer.push_back(p);

                rs.MoveNext();
        }
        rs.Close();
        return buffer;
}

Equipment PostgresLayer::GetEquipment(CString wantBarcode)
{

        CString barcode,name,description,placement,status;

        CString SQL;

        wantBarcode.Replace("'","\"");

        Equipment e;

        SQL.Format((CString)"SELECT Stregkode,Navn,Udstyr.Beskrivelse,Placering,Status.Beskrivelse FROM Udstyr " + 
                "INNER JOIN Status ON Udstyr.Status = Status.StatusID Where (Stregkode = '%s')", wantBarcode);
        CRecordset rs(&db);
        rs.Open(AFX_DB_USE_DEFAULT_TYPE, SQL);
        if (!rs.IsEOF() )
        {
                rs.GetFieldValue((short)0,barcode);
                rs.GetFieldValue(1,name);
                rs.GetFieldValue(2,description);
                rs.GetFieldValue(3,placement);
                rs.GetFieldValue(4,status);

                e.barcode = barcode;
                e.name = name;
                e.description = description;
                e.placement = placement;
                e.status = status;
        }
        rs.Close();
        return e;
}

bool PostgresLayer::DeleteEquipment(Equipment DelEquip)
{
        CString SQL;
        DelEquip.barcode.Replace("'","\"");
        SQL.Format("DELETE FROM Udstyr WHERE Stregkode = '%s'", DelEquip.barcode);
        db.ExecuteSQL(SQL);
        return true;
}


// Developed by Torben H. Nielsen
vector<Equipment> PostgresLayer::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 ILIKE '%" + 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 ILIKE '" + inits + "') ";
                if (barcode != "")
                        SQL += " AND Udstyr.stregkode = '" + barcode + "' ";
                if (name != "")
                        SQL += " AND Udstyr.Navn ILIKE '%" + 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 ILIKE '%" + name + "%' ";
                if (inits != "")
                        SQL += " AND Person.Initialer ILIKE '" + inits + "' ";

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

        // now I have the equipment rows the user asked for, now I need some more checkout and 
        // reservation data for each piece of equipment

        for (int i=0; i<buffer.size(); i++) { 
                //fill Equipment::checkouts
                
                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 = " + buffer[i].barcode + " ORDER BY STARTDATO DESC LIMIT 10";
                CRecordset rs(&db);
                rs.Open(AFX_DB_USE_DEFAULT_TYPE, SQL);
                if (rs.GetRecordCount()>0) {
                        rs.MoveFirst();
                        while ( !rs.IsEOF() ) {
                                Checkout check;
                                rs.GetFieldValue((short)0, check.startdate);
                                rs.GetFieldValue(1, check.enddate);
                                rs.GetFieldValue(2, check.numdays);
                                rs.GetFieldValue(3, check.inits);

                                buffer[i].checkouts.push_back(check);
                                rs.MoveNext();
                        }
                }
                rs.Close();
                                
                // fill Equipment::reservations
                SQL = "SELECT Resevation.ResevationID, Resevation.StartDato,  Person.Initialer\
                                FROM Resevation\
                                INNER JOIN Person ON Resevation.PersonID = Person.PersonID ";
                SQL += "WHERE Resevation.Stregkode = " + buffer[i].barcode + " ORDER BY STARTDATO ASC";
                
                rs.Open(AFX_DB_USE_DEFAULT_TYPE, SQL);
                if (rs.GetRecordCount()>0) {
                        rs.MoveFirst();
                        while ( !rs.IsEOF() ) {
                                Reservation res;
                                rs.GetFieldValue((short)0, res.reservationID);
                                rs.GetFieldValue(1, res.startdate);
                                rs.GetFieldValue(2, res.inits);

                                buffer[i].reservations.push_back(res);
                                rs.MoveNext();
                        }
                }
                rs.Close();
        }

        return buffer;
}

// Developed by Torben H. Nielsen
void PostgresLayer::FillEquipmentVector(vector<Equipment> &buffer, CRecordset &rs)
{
        CString barcode,name,description,placement,status;
        if (rs.GetRecordCount()>0) {
                rs.MoveFirst();
                while (!rs.IsEOF() ) {
                        Equipment eq;
                        rs.GetFieldValue((short)0, eq.barcode);
                        rs.GetFieldValue(1, eq.name);
                        rs.GetFieldValue(2, eq.description);
                        rs.GetFieldValue(3, eq.placement);
                        rs.GetFieldValue(4, eq.status);

                        buffer.push_back(eq);

                        rs.MoveNext();
                }
        }
}
//Developed by Torben H. Nielsen
void PostgresLayer::checkReservations(CString barcode)
{
        CString scount,status;
        barcode.Replace("'","\"");
        CString SQL = "SELECT count(*) FROM Resevation WHERE Resevation.Stregkode = '" + barcode  + "'";
        CRecordset rs(&db);
        rs.Open(AFX_DB_USE_DEFAULT_TYPE,SQL);
        rs.MoveFirst();
        rs.GetFieldValue((short)0,scount);
        int reservationCount = atoi(scount);
        rs.Close();

        SQL = "SELECT status FROM Udstyr WHERE Udstyr.Stregkode = '" + barcode + "'";
        rs.Open(AFX_DB_USE_DEFAULT_TYPE,SQL);
        if (rs.GetRecordCount() >0) {
                rs.MoveFirst();
                rs.GetFieldValue((short)0,status);
        }
        rs.Close();

        if (reservationCount > 0 && status == "3") {//equipment is available and has at lease one reservation
                SQL = "UPDATE Udstyr SET Status = 2 WHERE Stregkode = '" + barcode + "'";
                db.ExecuteSQL(SQL);
        }
}

//Developed by Torben H. Nielsen
bool PostgresLayer::DeleteReservation(CString barcode, CString resid)
{
        barcode.Replace("'","\"");
        resid.Replace("'","\"");
        CString SQL = "DELETE FROM Resevation WHERE resevationid = " + resid;
        db.ExecuteSQL(SQL);
        checkReservations(barcode);
        return true;
}

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	vector<Person> PostgresLayer::GetPersonAll(void)
22	{
23	vector<Person> buffer;
24	CString id, inits, name, isadmin, pass;
25
26	CRecordset rs(&db);
27	rs.Open(AFX_DB_USE_DEFAULT_TYPE, "SELECT PersonID, Initialer, Navn, Admin, Pass FROM Person ORDER BY Initialer DESC");
28	while(!rs.IsEOF())
29	{
30	rs.GetFieldValue((short)0,id);
31	rs.GetFieldValue(1,inits);
32	rs.GetFieldValue(2,name);
33	rs.GetFieldValue(3,isadmin);
34	rs.GetFieldValue(4,pass);
35
36	Person p;
37	p.id = id;
38	p.inits = inits;
39	p.name = name;
40	p.isadmin = (isadmin == 1);
41	p.pass = pass;
42
43	buffer.push_back(p);
44
45	rs.MoveNext();
46	}
47	rs.Close();
48	return buffer;
49	}
50
51	Person PostgresLayer::GetPerson(CString wantInits)
52	{
53
54	CString id, inits, name, isadmin, pass;
55	CString SQL;
56
57	Person p;
58
59	wantInits.Replace("'","\"");
60
61	SQL = "SELECT PersonID, Initialer, Navn, Admin, Pass FROM Person WHERE (Initialer ILIKE '" + wantInits + "')";
62	CRecordset rs(&db);
63	rs.Open(AFX_DB_USE_DEFAULT_TYPE, SQL);
64
65	if(!rs.IsEOF() )
66	{
67	rs.GetFieldValue((short)0,id);
68	rs.GetFieldValue(1,inits);
69	rs.GetFieldValue(2,name);
70	rs.GetFieldValue(3,isadmin);
71	rs.GetFieldValue(4,pass);
72
73	p.id = id;
74	p.inits = inits;
75	p.name = name;
76	p.isadmin = (isadmin == "1");
77	p.pass = pass;
78	}
79
80	return p;
81	}
82
83	bool PostgresLayer::AddPerson(Person NewPerson)
84	{
85	CString SQL;
86	NewPerson.inits.Replace("'","\"");
87	NewPerson.name.Replace("'","\"");
88	NewPerson.pass.Replace("'","\"");
89	SQL.Format("INSERT into Person(Initialer, Navn, Admin, Pass) VALUES('%s', '%s', '%d', '%s')",
90	NewPerson.inits, NewPerson.name, NewPerson.isadmin, NewPerson.pass);
91
92	db.ExecuteSQL(SQL);
93	return true;
94	}
95
96	bool PostgresLayer::UpdatePerson(Person ChangePerson)
97	{
98	CString SQL;
99	ChangePerson.inits.Replace("'","\"");
100	ChangePerson.name.Replace("'","\"");
101	ChangePerson.pass.Replace("'","\"");
102	ChangePerson.id.Replace("'","\"");
103	SQL.Format("UPDATE Person SET Initialer = '%s', Navn = '%s', Pass = '%s', Admin = '%d' WHERE PersonID = '%s'",
104	ChangePerson.inits, ChangePerson.name, ChangePerson.pass, ChangePerson.isadmin, ChangePerson.id);
105
106	db.ExecuteSQL(SQL);
107	return true;
108	}
109
110	bool PostgresLayer::DeletePerson(Person RemovePerson)
111	{
112	CString SQL;
113	RemovePerson.id.Replace("'","\"");
114	SQL.Format("DELETE FROM Person WHERE PersonID = '%s'", RemovePerson.id);
115	db.ExecuteSQL(SQL);
116	return true;
117	}
118
119	bool PostgresLayer::ReturnEquipment(CString barcode)
120	{
121	CString SQL;
122	barcode.Replace("'","\"");
123	SQL.Format("UPDATE Udstyr SET Status = '3' WHERE Stregkode= '%s'", barcode);
124	db.ExecuteSQL(SQL);
125
126	SQL.Format("UPDATE Udl�n SET Afleveringsdato = now() WHERE Stregkode= '%s' AND Afleveringsdato IS NULL ", barcode);
127	db.ExecuteSQL(SQL);
128
129	checkReservations(barcode);
130	return true;
131	}
132
133	bool PostgresLayer::CheckoutEquipment(Person CheckPersID, Equipment CheckEquip, int Numdays)
134	{
135	CString SQL;
136	CheckEquip.barcode.Replace("'","\"");
137	CheckPersID.id.Replace("'","\"");
138	SQL.Format("UPDATE Udstyr SET Status = '1' WHERE Stregkode = '%s' ", CheckEquip.barcode);
139	db.ExecuteSQL(SQL);
140	SQL.Format("INSERT INTO Udl�n (PersonID, Stregkode, Startdato, Antaldage) VALUES( '%s','%s', now(),'%d')", CheckPersID.id, CheckEquip.barcode, Numdays);
141	db.ExecuteSQL(SQL);
142	return true;
143	}
144
145	bool PostgresLayer::EquipmentReservation(CString barcode, Person CheckPerson)
146	{
147	CString SQL;
148	CheckPerson.id.Replace("'","\"");
149	barcode.Replace("'","\"");
150	SQL.Format("INSERT INTO Resevation (PersonID, Stregkode, Startdato) VALUES( '%s','%s', now())", CheckPerson.id, barcode);
151	db.ExecuteSQL(SQL);
152	checkReservations(barcode);
153	return true;
154	}
155
156	bool PostgresLayer::UpdateEquipment(Equipment CheckEquip)
157	{
158	CString SQL;
159	CheckEquip.name.Replace("'","\"");
160	CheckEquip.description.Replace("'","\"");
161	CheckEquip.placement.Replace("'","\"");
162	CheckEquip.status.Replace("'","\"");
163	CheckEquip.barcode.Replace("'","\"");
164	SQL.Format("UPDATE Udstyr SET Navn = '%s', Beskrivelse = '%s', Placering = '%s' WHERE Stregkode = '%s'", CheckEquip.name, CheckEquip.description, CheckEquip.placement, CheckEquip.barcode);
165	db.ExecuteSQL(SQL);
166	return true;
167	}
168
169	bool PostgresLayer::AddEquipment(Equipment AddEquip)
170	{
171	CString SQL;
172	AddEquip.barcode.Replace("'","\"");
173	AddEquip.name.Replace("'","\"");
174	AddEquip.description.Replace("'","\"");
175	AddEquip.placement.Replace("'","\"");
176	SQL.Format("INSERT into Udstyr(Stregkode, Navn, Beskrivelse, Placering, Status) VALUES('%s', '%s', '%s', '%s', '%s')",AddEquip.barcode, AddEquip.name, AddEquip.description, AddEquip.placement, "3");
177	db.ExecuteSQL(SQL);
178	return true;
179	}
180
181	vector<Equipment> PostgresLayer::GetEquipmentAll(void)
182	{
183	vector<Equipment> buffer;
184	CString barcode, name, description, placement, status;
185
186	CRecordset rs(&db);
187	rs.Open(AFX_DB_USE_DEFAULT_TYPE, "SELECT Stregkode, Navn, Beskrivelse, Placering, Status FROM Udstyr");
188	while(!rs.IsEOF())
189	{
190	rs.GetFieldValue((short)0,barcode);
191	rs.GetFieldValue(1,name);
192	rs.GetFieldValue(2,description);
193	rs.GetFieldValue(3,placement);
194	rs.GetFieldValue(4,status);
195
196	Equipment p;
197	p.barcode = barcode;
198	p.name = name;
199	p.description = description;
200	p.placement = placement;
201	p.status = status;
202	buffer.push_back(p);
203
204	rs.MoveNext();
205	}
206	rs.Close();
207	return buffer;
208	}
209
210	Equipment PostgresLayer::GetEquipment(CString wantBarcode)
211	{
212
213	CString barcode,name,description,placement,status;
214
215	CString SQL;
216
217	wantBarcode.Replace("'","\"");
218
219	Equipment e;
220
221	SQL.Format((CString)"SELECT Stregkode,Navn,Udstyr.Beskrivelse,Placering,Status.Beskrivelse FROM Udstyr " +
222	"INNER JOIN Status ON Udstyr.Status = Status.StatusID Where (Stregkode = '%s')", wantBarcode);
223	CRecordset rs(&db);
224	rs.Open(AFX_DB_USE_DEFAULT_TYPE, SQL);
225	if (!rs.IsEOF() )
226	{
227	rs.GetFieldValue((short)0,barcode);
228	rs.GetFieldValue(1,name);
229	rs.GetFieldValue(2,description);
230	rs.GetFieldValue(3,placement);
231	rs.GetFieldValue(4,status);
232
233	e.barcode = barcode;
234	e.name = name;
235	e.description = description;
236	e.placement = placement;
237	e.status = status;
238	}
239	rs.Close();
240	return e;
241	}
242
243	bool PostgresLayer::DeleteEquipment(Equipment DelEquip)
244	{
245	CString SQL;
246	DelEquip.barcode.Replace("'","\"");
247	SQL.Format("DELETE FROM Udstyr WHERE Stregkode = '%s'", DelEquip.barcode);
248	db.ExecuteSQL(SQL);
249	return true;
250	}
251
252
253	// Developed by Torben H. Nielsen
254	vector<Equipment> PostgresLayer::Search(CString barcode, CString name, CString inits, bool available, bool reserved, bool deposited)
255	{
256	vector<Equipment> buffer;
257
258	barcode.Replace("'","\"");
259	name.Replace("'","\"");
260	inits.Replace("'","\"");
261
262	//here are the wanted equipments found through 3 seperate queries - I could also
263	//find them all in one query where I joined the 3 selects via a UNION
264	CString SQL;
265	if (available == true) {
266	SQL = "SELECT DISTINCT Udstyr.stregkode,Udstyr.Navn,Udstyr.Beskrivelse,Udstyr.Placering,Status.Beskrivelse \
267	FROM Udstyr \
268	INNER JOIN Status ON Udstyr.status = Status.StatusID WHERE Udstyr.Status = 3 ";
269	if (barcode != "")
270	SQL += " AND Udstyr.stregkode = '" + barcode + "' ";
271	if (name != "")
272	SQL += " AND Udstyr.Navn ILIKE '%" + name + "%' ";
273
274	CRecordset rs(&db);
275	rs.Open(AFX_DB_USE_DEFAULT_TYPE, SQL);
276	FillEquipmentVector(buffer,rs);
277	rs.Close();
278	}
279
280	if (reserved == true) {
281	SQL = "SELECT DISTINCT Udstyr.stregkode,Udstyr.Navn,Udstyr.Beskrivelse,Udstyr.Placering,Status.Beskrivelse \r\n\
282	FROM Udstyr \r\n\
283	INNER JOIN Status ON Udstyr.status = Status.StatusID \r\n\
284	INNER JOIN Resevation ON Udstyr.Stregkode = Resevation.stregkode \r\n\
285	INNER JOIN Person ON Resevation.PersonID = Person.PersonID\r\n\
286	WHERE ( Udstyr.Status = 2 OR Person.Initialer ILIKE '" + inits + "') ";
287	if (barcode != "")
288	SQL += " AND Udstyr.stregkode = '" + barcode + "' ";
289	if (name != "")
290	SQL += " AND Udstyr.Navn ILIKE '%" + name + "%' ";
291	//if (inits != "")
292	// SQL += " AND Person.Initialer = '" + inits + "' ";
293
294	CRecordset rs(&db);
295	rs.Open(AFX_DB_USE_DEFAULT_TYPE, SQL);
296	FillEquipmentVector(buffer,rs);
297	rs.Close();
298	}
299
300
301	if (deposited == true) {
302	SQL = "SELECT DISTINCT Udstyr.stregkode,Udstyr.Navn,Udstyr.Beskrivelse,Udstyr.Placering,Status.Beskrivelse \
303	FROM Udstyr \
304	INNER JOIN Status ON Udstyr.status = Status.StatusID \
305	INNER JOIN Udl�n ON Udstyr.Stregkode = Udl�n.stregkode \
306	INNER JOIN Person ON Udl�n.PersonID = Person.PersonID \
307	WHERE Udstyr.Status = 1 \
308	AND Udl�n.Afleveringsdato IS NULL";
309	if (barcode != "")
310	SQL += " AND Udstyr.stregkode = '" + barcode + "' ";
311	if (name != "")
312	SQL += " AND Udstyr.Navn ILIKE '%" + name + "%' ";
313	if (inits != "")
314	SQL += " AND Person.Initialer ILIKE '" + inits + "' ";
315
316	CRecordset rs(&db);
317	rs.Open(AFX_DB_USE_DEFAULT_TYPE, SQL);
318	FillEquipmentVector(buffer,rs);
319	rs.Close();
320	}
321
322	// now I have the equipment rows the user asked for, now I need some more checkout and
323	// reservation data for each piece of equipment
324
325	for (int i=0; i<buffer.size(); i++) {
326	//fill Equipment::checkouts
327
328	SQL = "SELECT Udl�n.StartDato, Udl�n.Afleveringsdato,Udl�n.Antaldage, Person.Initialer\
329	FROM Udl�n\
330	INNER JOIN Person ON Udl�n.PersonID = Person.PersonID ";
331	SQL += "WHERE Udl�n.Stregkode = " + buffer[i].barcode + " ORDER BY STARTDATO DESC LIMIT 10";
332	CRecordset rs(&db);
333	rs.Open(AFX_DB_USE_DEFAULT_TYPE, SQL);
334	if (rs.GetRecordCount()>0) {
335	rs.MoveFirst();
336	while ( !rs.IsEOF() ) {
337	Checkout check;
338	rs.GetFieldValue((short)0, check.startdate);
339	rs.GetFieldValue(1, check.enddate);
340	rs.GetFieldValue(2, check.numdays);
341	rs.GetFieldValue(3, check.inits);
342
343	buffer[i].checkouts.push_back(check);
344	rs.MoveNext();
345	}
346	}
347	rs.Close();
348
349	// fill Equipment::reservations
350	SQL = "SELECT Resevation.ResevationID, Resevation.StartDato, Person.Initialer\
351	FROM Resevation\
352	INNER JOIN Person ON Resevation.PersonID = Person.PersonID ";
353	SQL += "WHERE Resevation.Stregkode = " + buffer[i].barcode + " ORDER BY STARTDATO ASC";
354
355	rs.Open(AFX_DB_USE_DEFAULT_TYPE, SQL);
356	if (rs.GetRecordCount()>0) {
357	rs.MoveFirst();
358	while ( !rs.IsEOF() ) {
359	Reservation res;
360	rs.GetFieldValue((short)0, res.reservationID);
361	rs.GetFieldValue(1, res.startdate);
362	rs.GetFieldValue(2, res.inits);
363
364	buffer[i].reservations.push_back(res);
365	rs.MoveNext();
366	}
367	}
368	rs.Close();
369	}
370
371	return buffer;
372	}
373
374	// Developed by Torben H. Nielsen
375	void PostgresLayer::FillEquipmentVector(vector<Equipment> &buffer, CRecordset &rs)
376	{
377	CString barcode,name,description,placement,status;
378	if (rs.GetRecordCount()>0) {
379	rs.MoveFirst();
380	while (!rs.IsEOF() ) {
381	Equipment eq;
382	rs.GetFieldValue((short)0, eq.barcode);
383	rs.GetFieldValue(1, eq.name);
384	rs.GetFieldValue(2, eq.description);
385	rs.GetFieldValue(3, eq.placement);
386	rs.GetFieldValue(4, eq.status);
387
388	buffer.push_back(eq);
389
390	rs.MoveNext();
391	}
392	}
393	}
394	//Developed by Torben H. Nielsen
395	void PostgresLayer::checkReservations(CString barcode)
396	{
397	CString scount,status;
398	barcode.Replace("'","\"");
399	CString SQL = "SELECT count(*) FROM Resevation WHERE Resevation.Stregkode = '" + barcode + "'";
400	CRecordset rs(&db);
401	rs.Open(AFX_DB_USE_DEFAULT_TYPE,SQL);
402	rs.MoveFirst();
403	rs.GetFieldValue((short)0,scount);
404	int reservationCount = atoi(scount);
405	rs.Close();
406
407	SQL = "SELECT status FROM Udstyr WHERE Udstyr.Stregkode = '" + barcode + "'";
408	rs.Open(AFX_DB_USE_DEFAULT_TYPE,SQL);
409	if (rs.GetRecordCount() >0) {
410	rs.MoveFirst();
411	rs.GetFieldValue((short)0,status);
412	}
413	rs.Close();
414
415	if (reservationCount > 0 && status == "3") {//equipment is available and has at lease one reservation
416	SQL = "UPDATE Udstyr SET Status = 2 WHERE Stregkode = '" + barcode + "'";
417	db.ExecuteSQL(SQL);
418	}
419	}
420
421	//Developed by Torben H. Nielsen
422	bool PostgresLayer::DeleteReservation(CString barcode, CString resid)
423	{
424	barcode.Replace("'","\"");
425	resid.Replace("'","\"");
426	CString SQL = "DELETE FROM Resevation WHERE resevationid = " + resid;
427	db.ExecuteSQL(SQL);
428	checkReservations(barcode);
429	return true;
430	}
431