1: <?php
  2: 
  3: /**
  4:  * 
  5:  * @package Tests
  6:  */
  7: if(!class_exists('Person')){
  8:     require_once __INCLUDES__ .'/model/Person.class.php';
  9:     
 10: }
 11: if(!class_exists('Project')){
 12:     require_once __INCLUDES__ .'/model/Project.class.php';
 13: }
 14: if(!class_exists('Login')){
 15:     require_once __INCLUDES__ .'/model/Login.class.php';
 16: }
 17: if(!class_exists('Milestone')){
 18:     require_once __INCLUDES__ .'/model/Milestone.class.php';
 19: }
 20: if(!class_exists('Address')){
 21:     require_once __INCLUDES__ .'/model/Address.class.php';
 22: }
 23: if(!class_exists('TwoKey')){
 24:     require_once __INCLUDES__ .'/model/TwoKey.class.php';
 25: }
 26: 
 27: class BasicOrmTests extends QUnitTestCaseBase {    
 28:     public function testSaveAndDelete() {
 29:         $objPerson1 = new Person();
 30:         $objPerson1->FirstName = "Test1";
 31:         $objPerson1->LastName = "Last1";
 32:         $objPerson1->Save();
 33:         
 34:         $items = Person::QueryArray(
 35:             QQ::AndCondition(
 36:                 QQ::Equal(QQN::Person()->FirstName, "Test1"),
 37:                 QQ::Equal(QQN::Person()->LastName, "Last1")
 38:             )
 39:         );
 40:                 
 41:         $this->assertEquals(1, sizeof($items), "Saved the Person object");
 42:             
 43:         $objPerson2 = $items[0];
 44:         $this->assertEquals("Test1", $objPerson2->FirstName, "The first name is correct");
 45:         $this->assertEquals("Last1", $objPerson2->LastName, "The last name is correct");
 46:         
 47:         $objPerson2->Delete();
 48: 
 49:         $items = Person::QueryArray(
 50:             QQ::AndCondition(
 51:                 QQ::Equal(QQN::Person()->FirstName, "Test1"),
 52:                 QQ::Equal(QQN::Person()->LastName, "Last1")
 53:             )                         
 54:         );
 55:         
 56:         $this->assertEquals(0, sizeof($items), "Deleting the Person object");
 57:     }
 58: 
 59:     public function testQueryArray() {
 60:         $someDate = new QDateTime();
 61:         $someDate->setDate(2006, 1, 1);
 62:         
 63:         $objItems = Milestone::QueryArray(
 64:             QQ::GreaterThan(QQN::Milestone()->Project->StartDate, $someDate),
 65:             QQ::OrderBy(QQN::Milestone()->Project->Name)
 66:         );
 67:         
 68:         $this->assertEquals(3, sizeof($objItems));
 69: 
 70:         $this->assertEquals("Milestone F", $objItems[0]->Name);
 71:         $this->assertEquals("Blueman Industrial Site Architecture", $objItems[0]->Project->Name);
 72: 
 73:         $this->assertEquals("Milestone D", $objItems[1]->Name);
 74:         $this->assertEquals("State College HR System", $objItems[1]->Project->Name);
 75: 
 76:         $this->assertEquals("Milestone E", $objItems[2]->Name);
 77:         $this->assertEquals("State College HR System", $objItems[2]->Project->Name);
 78:     }
 79:     
 80:     public function testQueryCount() {
 81:         $someDate = new QDateTime();
 82:         $someDate->setDate(2006, 1, 1);
 83:         
 84:         $intItemCount = Milestone::QueryCount(
 85:             QQ::GreaterThan(QQN::Milestone()->Project->StartDate, $someDate),
 86:             // test for single QQClause object
 87:             // the subject of the https://github.com/qcubed/framework/issues/100 issue #100
 88:             QQ::Distinct()
 89:         );
 90:         
 91:         $this->assertEquals(3, $intItemCount);
 92: 
 93:         $intItemCount2 = Milestone::QueryCount(
 94:             QQ::GreaterThan(QQN::Milestone()->Project->StartDate, $someDate),
 95:             // test for an array of QQClause objects
 96:             QQ::Clause(
 97:                 // The QQ::Distinct is used because of the https://github.com/qcubed/framework/issues/231 issue #231
 98:                 QQ::Distinct()
 99:                 , QQ::Distinct()
100:             )
101:         );
102:         
103:         $this->assertEquals(3, $intItemCount2);
104:     }
105:     
106:     public function testOrderByCondition() {
107:         $objItems = Person::QueryArray(
108:             QQ::All(),
109:             QQ::Clause(
110:                 QQ::OrderBy(
111:                     QQ::NotEqual(QQN::Person()->LastName, 'Smith'), 
112:                     QQN::Person()->FirstName)
113:                 )
114:             );
115: 
116:         $this->assertEquals("Alex Smith", $objItems[0]->FirstName . " " . $objItems[0]->LastName);
117:         $this->assertEquals("Jennifer Smith", $objItems[1]->FirstName . " " . $objItems[1]->LastName);
118:         $this->assertEquals("Wendy Smith", $objItems[2]->FirstName . " " . $objItems[2]->LastName);
119:         $this->assertEquals("Ben Robinson", $objItems[3]->FirstName . " " . $objItems[3]->LastName);
120:     }
121:     
122:     public function testGroupBy() {
123:         $objItems = Project::QueryArray(
124:             QQ::All(),
125:             QQ::Clause(
126:                 QQ::GroupBy(QQN::Project()->Id),
127:                 QQ::Count(QQN::Project()->PersonAsTeamMember->PersonId, 'team_member_count'),
128:                 QQ::OrderBy(QQN::Project()->Id)
129:             )
130:         );
131:         
132:         $this->assertEquals(4, sizeof($objItems), "4 projects found");
133:         
134:         $this->assertEquals(5, $objItems[0]->GetVirtualAttribute('team_member_count'), "5 team members found for the first project");
135:         $this->assertEquals(6, $objItems[1]->GetVirtualAttribute('team_member_count'), "6 team members found for the second project");
136:         $this->assertEquals(5, $objItems[2]->GetVirtualAttribute('team_member_count'), "5 team members found for the third project");
137:         $this->assertEquals(7, $objItems[3]->GetVirtualAttribute('team_member_count'), "7 team members found for the forth project");
138:     }
139:     
140:     public function testAssociationTables() {
141:         // All People Who Are on a Project Managed by Karen Wolfe (Person ID #7)        
142:         $objPersonArray = Person::QueryArray(
143:             QQ::Equal(QQN::Person()->ProjectAsTeamMember->Project->ManagerPersonId, 7),
144:             QQ::Clause(
145:                 QQ::Distinct(),
146:                 QQ::OrderBy(QQN::Person()->LastName, QQN::Person()->FirstName)
147:             )
148:         );
149:         
150:         $arrNamesOnly = array();
151:         foreach ($objPersonArray as $item) {
152:             $arrNamesOnly[] = $item->FirstName . " " . $item->LastName;
153:          }
154:         
155:         $this->assertEquals(array(
156:             "Brett Carlisle",
157:             "John Doe",
158:             "Samantha Jones",
159:             "Jacob Pratt",
160:             "Kendall Public",
161:             "Ben Robinson",
162:             "Alex Smith",
163:             "Wendy Smith",
164:             "Karen Wolfe"),
165:             $arrNamesOnly,
166:             "List managed persons is correct");
167:         
168:         $objPersonArray = Person::QueryArray(
169:             QQ::Equal(QQN::Person()->PersonType->PersonTypeId, PersonType::Inactive),
170:             QQ::Clause(
171:                 QQ::OrderBy(QQN::Person()->LastName, QQN::Person()->FirstName)
172:             )
173:         );
174:         
175:         $arrNamesOnly = array();
176:         foreach ($objPersonArray as $item) {
177:             $arrNamesOnly[] = $item->FirstName . " " . $item->LastName;
178:         }
179:         
180:         $this->assertEquals(array(
181:             "Linda Brady",
182:             "John Doe",
183:             "Ben Robinson")
184:             , $arrNamesOnly
185:             , "Person-PersonType assn is correct");
186:         
187:     }
188:     
189:     public function testQuerySingleEmpty() {
190:         $targetPerson = Person::QuerySingle(
191:             QQ::Equal(QQN::Person()->Id, 1241243));
192:         
193:         $this->assertEquals(null, $targetPerson, "QuerySingle should return null for a not-found record");
194:     }
195: 
196:     public function testQuerySelectSubset() {
197:         $objPersonArray = Person::LoadAll(QQ::Select(QQN::Person()->FirstName));
198:         foreach ($objPersonArray as $objPerson) {
199:             $this->assertNull($objPerson->LastName, "LastName should be null, since it was not selected");
200:             $this->assertNotNull($objPerson->Id, "Id should not be null since it's always added to the select list");
201:         }
202:     }
203:     
204:     public function testLoadAll() {
205:         $objPersonArray = Person::LoadAll ();
206:         $this->assertEquals(12, count($objPersonArray), "12 people found.");
207:         
208:         $objTwoKeyArray = TwoKey::LoadAll();
209:         $this->assertEquals(6, count($objTwoKeyArray), "6 TwoKey items found.");
210:     }
211:     
212:     public function testQuerySelectSubsetSkipPK() {
213:         $objSelect = QQ::Select(QQN::Person()->FirstName);
214:         $objSelect->SetSkipPrimaryKey(true);
215:         $objPersonArray = Person::LoadAll($objSelect);
216:         foreach ($objPersonArray as $objPerson) {
217:             $this->assertNull($objPerson->LastName, "LastName should be null, since it was not selected");
218:             $this->assertNull($objPerson->Id, "Id should be null since SkipPrimaryKey is set on the Select object");
219:         }
220:     }
221: 
222:     public function testExpand() {
223:         // Test intermediate nodes on expansion
224:          $clauses = QQ::Clause(
225:             QQ::Expand(QQN::Milestone()->Project->ManagerPerson)
226:         );
227:         
228:         $objMilestone = 
229:             Milestone::QuerySingle(
230:                 QQ::Equal (QQN::Milestone()->Id, 1),
231:                 $clauses
232:             );
233:         
234:         $this->assertTrue(!is_null($objMilestone->Name), "Milestone 1 has a name");
235:         $this->assertEquals("Milestone A", $objMilestone->Name, "Milestone 1 has name of Milestone A");
236:         $this->assertTrue(!is_null($objMilestone->Project->Name), "Project 1 has a name");
237:         $this->assertEquals("ACME Website Redesign", $objMilestone->Project->Name, "Project 1 has name of ACME Website Redesign");
238:         $this->assertTrue(!is_null($objMilestone->Project->ManagerPerson->FirstName), "Person 7 has a name");
239:         $this->assertEquals("Karen", $objMilestone->Project->ManagerPerson->FirstName, "Person 7 has first name of Karen");
240:         
241:          $clauses = QQ::Clause(
242:             QQ::ExpandAsArray (QQN::Project()->PersonAsTeamMember),
243:             QQ::OrderBy (QQN::Project()->PersonAsTeamMember->Person->LastName, QQN::Project()->PersonAsTeamMember->Person->FirstName)
244:         );
245:         
246:         // short reach
247:         $objProject = 
248:             Project::QuerySingle(
249:                 QQ::Equal (QQN::Project()->Id, 1),
250:                 $clauses
251:             );
252:             
253:         $objPersonArray = $objProject->_PersonAsTeamMemberArray;
254:         $arrNamesOnly = array();
255:         foreach ($objPersonArray as $item) {
256:             $arrNamesOnly[] = $item->FirstName . " " . $item->LastName;
257:         }
258:         
259:         $this->assertEquals(array(
260:             "Samantha Jones",
261:             "Kendall Public",
262:             "Alex Smith",
263:             "Wendy Smith",
264:             "Karen Wolfe")
265:             , $arrNamesOnly
266:             , "Project Team Member expansion is correct");
267:         
268:         // long reach
269:         $clauses = QQ::Clause(
270:             QQ::ExpandAsArray (QQN::Milestone()->Project->PersonAsTeamMember),
271:             QQ::OrderBy (QQN::Milestone()->Project->PersonAsTeamMember->Person->LastName, QQN::Milestone()->Project->PersonAsTeamMember->Person->FirstName)
272:         );
273:         
274:         
275:         $objMilestone = 
276:             Milestone::QuerySingle(
277:                 QQ::Equal (QQN::Milestone()->Id, 1),
278:                 $clauses
279:             );
280:             
281:         $objPersonArray = $objMilestone->Project->_PersonAsTeamMemberArray;
282:         $arrNamesOnly = array();
283:         foreach ($objPersonArray as $item) {
284:             $arrNamesOnly[] = $item->FirstName . " " . $item->LastName;
285:         }
286:         
287:         $this->assertEquals(array(
288:             "Samantha Jones",
289:             "Kendall Public",
290:             "Alex Smith",
291:             "Wendy Smith",
292:             "Karen Wolfe"
293:             )
294:             , $arrNamesOnly
295:             , "Long reach Milestone to Project Team Member expansion is correct");
296:     }
297: 
298:     /**
299:      * Make sure that expansions looking backwards are pointing to the same object looking forwards
300:      */
301:     public function testExpandReverseReferences() {
302: 
303:         // Test virtual binding of reverse relationships
304:         $clauses = [QQ::Expand(QQN::Person()->ProjectAsManager)];
305:         $objPerson = Person::QuerySingle(QQ::All(), $clauses);
306:         $objPerson->FirstName = 'test';
307:         $objProject = $objPerson->ProjectAsManager;
308:         $objPerson2 = $objProject->ManagerPerson;
309: 
310:         $this->assertEquals('test', $objPerson2->FirstName);
311: 
312:         
313:         // Test forward reference looking back
314:         $clauses = [QQ::Expand(QQN::Project()->ManagerPerson)];
315:         $objProject = Project::QuerySingle(QQ::All(), $clauses);
316:         $objProject->Name = 'test';
317:         $objPerson = $objProject->ManagerPerson;
318:         $objProject2 = $objPerson->ProjectAsManager;
319: 
320:         $this->assertEquals('test', $objProject2->Name);
321: 
322:         // test unique reverse reference
323:         $clauses = [QQ::Expand(QQN::Person()->Login)];
324:         $objPerson = Person::QuerySingle(QQ::All(), $clauses);
325:         $objPerson->FirstName = 'test';
326:         $objLogin = $objPerson->Login;
327:         $objPerson2 = $objLogin->Person;
328: 
329:         $this->assertEquals('test', $objPerson2->FirstName);
330: 
331:         // test many-to-many expansion
332:         $clauses = [QQ::ExpandAsArray(QQN::Project()->PersonAsTeamMember)];
333:         $objProject = Project::QuerySingle(QQ::All(), $clauses);
334:         $objProject->Name = 'test';
335:         $objPersonArray = $objProject->_PersonAsTeamMemberArray;
336:         $objProject2 = $objPersonArray[0]->_ProjectAsTeamMember;
337: 
338:         $this->assertEquals('test', $objProject2->Name);
339: 
340:     }
341:     
342:     public function testHaving() {
343:         $objItems = Project::QueryArray(
344:             QQ::All(),
345:             QQ::Clause(
346:                 QQ::Select(QQN::Project()->Id, QQN::Project()->Name),   // Some databases require selecting specific fields when aggregating
347:                 QQ::GroupBy(QQN::Project()->Id),
348:                 QQ::Count(QQN::Project()->PersonAsTeamMember->PersonId, 'team_member_count'),
349:                 QQ::Having(QQ::SubSql('COUNT({1}) > 5', QQN::Project()->PersonAsTeamMember->PersonId)),
350:                 QQ::OrderBy(QQN::Project()->Id)
351:             )
352:         );
353:         
354:         $this->assertEquals(2, sizeof($objItems), "2 projects found");
355:         
356:         $this->assertEquals("State College HR System", $objItems[0]->Name, "Project " . $objItems[0]->Name . " found");
357:         $this->assertEquals(6, $objItems[0]->GetVirtualAttribute('team_member_count'), "6 team members found for project " . $objItems[0]->Name);
358:     }
359:     
360:     public function testEmptyColumns() {
361:         $objItem = Login::QuerySingle(
362:             QQ::Equal(QQN::Login()->Id, 1)
363:         );
364:         
365:         $var = $objItem->IsEnabled;
366: 
367:         $this->assertNotNull($var, "Zero column does not return null. ");
368:         $this->assertTrue($var == 0, "Zero boolean column is false or zero. ");
369: 
370:         $objItem = Project::QuerySingle(
371:             QQ::Equal(QQN::Project()->Id, 2)
372:         );
373:         $this->assertTrue($objItem->EndDate === null, "Null date column returns a null.");
374: 
375:         // Testing unique reverse reference on null
376:         $objPerson = new Person();
377:         $objLogin = $objPerson->Login;
378: 
379:         $this->assertNull($objLogin, "New record should not be associated with null PK.");
380:     }
381: 
382:     public function testOrderByReverseReference() {
383:         // orders by the private key of the reverse reference node.
384:         $objPerson = Person::QuerySingle(
385:             QQ::IsNotNull(QQN::Person()->ProjectAsManager->Id),
386:             [QQ::OrderBy(QQN::Person()->ProjectAsManager)]
387:         );
388:         $this->assertEquals(7, $objPerson->Id, "Manager of first project found.");
389: 
390:     }
391: 
392:     public function testOrderByExpansion() {
393:         $objPersonArray = Person::QueryArray(
394:             QQ::All(),
395:             QQ::OrderBy(
396:                 QQ::IsNotNull(QQN::Person()->ProjectAsManager->Description), false, QQN::Person()->ProjectAsManager->Id
397:             )
398:         );
399: 
400:         $this->assertEquals(7, $objPersonArray[0]->Id, "Found first project with manager");
401:     }
402: 
403:     public function testVirtualAttributeAliases() {
404:         $clauses = [
405:             QQ::GroupBy(QQN::Project()->ProjectStatusTypeId),
406:             QQ::Sum(QQN::Project()->Budget, 'Budget Amount'),
407:             QQ::Expand(QQ::Virtual('Balance', QQ::Func('SUM', QQ::Sub(QQN::Project()->Budget, QQN::Project()->Spent))))
408:         ];
409:         $cond = QQ::Equal(QQN::Project()->ProjectStatusTypeId, ProjectStatusType::Open);
410: 
411:         $objProject = Project::QuerySingle($cond, $clauses);
412: 
413:         $amount1 = $objProject->GetVirtualAttribute('Budget Amount');
414:         $this->assertEquals(83000, $amount1);
415:         $amount2 = $objProject->GetVirtualAttribute('Balance');
416:         $this->assertEquals(5599.50, $amount2);
417:     }
418: }
419: 
420: