Source of file SDLTReports.php
Size: 19,503 Bytes - Last Modified: 2021-12-23T10:08:54+00:00
/var/www/docs.ssmods.com/process/src/src/Reports/SDLTReports.php
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672 | <?php /** * This file contains the "SDLTReports" class. * * @category SilverStripe_Project * @package SDLT * @author Catalyst I.T. SilverStripe Team 2021 <silverstripedev@catalyst.net.nz> * @copyright NZ Transport Agency * @license BSD-3 * @link https://www.catalyst.net.nz */ namespace NZTA\SDLT\Reports; use SilverStripe\Reports\Report; use NZTA\SDLT\Model\QuestionnaireSubmission; use NZTA\SDLT\Model\TaskSubmission; use SilverStripe\ORM\ArrayList; use DateTime; /** * A custom report that generates the questionnaire submissions by pillar per year */ class SDLTReports_SubmissionsByYear extends Report { /** * @return string */ public function title() { return 'Submissions by pillar per year'; } /** * @param mixed|null $params * @return ArrayList */ public function sourceRecords($params = null) { $submissions = QuestionnaireSubmission::get()->filter([ 'QuestionnaireStatus:Not' => ['expired', 'in_progress', 'denied'], ]); $grouped = []; foreach ($submissions as $submission) { $key = substr($submission->Created, 0, 4). '_' . $submission->Questionnaire()->Name; if (array_key_exists($key, $grouped)) { $grouped[$key]['Num']++; } else { $grouped[$key] = [ 'Name' => $submission->Questionnaire()->Name, 'Year' => substr($submission->Created, 0, 4), 'Num' => 1, ]; } } $year = array_column($grouped, 'Year'); $name = array_column($grouped, 'Name'); array_multisort($year, SORT_DESC, $name, SORT_ASC, $grouped); return ArrayList::create($grouped); } /** * @return string[] $fields */ public function columns() { $fields = [ 'Name' => 'Pillar Name', 'Year' => 'Created Date', 'Num' => 'Submission Count', ]; return $fields; } } /** * A custom report that generates the questionnaire submissions by pillar per month per year */ class SDLTReports_SubmissionsByMonth extends Report { /** * @return string */ public function title() { return 'Submissions by pillar per month'; } /** * @param mixed|null $params * @return ArrayList */ public function sourceRecords($params = null) { $submissions = QuestionnaireSubmission::get()->filter([ 'QuestionnaireStatus:Not' => ['expired', 'in_progress', 'denied'], ]); $grouped = []; foreach ($submissions as $submission) { $key = substr($submission->Created, 0, 4). '_' . $submission->Questionnaire()->Name . substr($submission->Created, 5, 2); if (array_key_exists($key, $grouped)) { $grouped[$key]['Num']++; } else { $grouped[$key] = [ 'Name' => $submission->Questionnaire()->Name, 'Year' => substr($submission->Created, 0, 4), 'Month' => substr($submission->Created, 5, 2), 'Num' => 1, ]; } } $name = array_column($grouped, 'Name'); $year = array_column($grouped, 'Year'); $month = array_column($grouped, 'Month'); array_multisort($name, SORT_ASC, $year, SORT_DESC, $month, SORT_DESC, $grouped); return ArrayList::create($grouped); } /** * @return string[] $fields */ public function columns() { $fields = [ 'Name' => 'Pillar Name', 'Year' => 'Created Year', 'Month' => 'Created Month', 'Num' => 'Submission Count', ]; return $fields; } } /** * A custom report that generates the approvals by security architect per year */ class SDLTReports_ApprovalsBySAPerYear extends Report { /** * @return string */ public function title() { return 'Approvals by security architect per year'; } /** * @param mixed|null $params * @return ArrayList */ public function sourceRecords($params = null) { $submissions = QuestionnaireSubmission::get()->filter([ 'SecurityArchitectApprovalStatus' => ['approved'], ]); $grouped = []; foreach ($submissions as $submission) { $key = $submission->SecurityArchitectApprover->FirstName . '_' . $submission->SecurityArchitectApprover->Surname. substr($submission->Created, 0, 4); if (array_key_exists($key, $grouped)) { $grouped[$key]['Num']++; } else { $grouped[$key] = [ 'FirstName' => $submission->SecurityArchitectApprover->FirstName, 'LastName' => $submission->SecurityArchitectApprover->Surname, 'Year' => substr($submission->Created, 0, 4), 'Num' => 1, ]; } } $year = array_column($grouped, 'Year'); $count = array_column($grouped, 'Num'); array_multisort($year, SORT_DESC, $count, SORT_DESC, $grouped); return ArrayList::create($grouped); } /** * @return string[] $fields */ public function columns() { $fields = [ 'FirstName' => 'First Name', 'LastName' => 'Last Name', 'Year' => 'Year', 'Num' => 'Submission Count', ]; return $fields; } } /** * A custom report that generates the approvals by security architect per month per year */ class SDLTReports_ApprovalsBySAPerMonthPerYear extends Report { /** * @return string */ public function title() { return 'Approvals by security architect per month per year'; } /** * @param mixed|null $params * @return ArrayList */ public function sourceRecords($params = null) { $submissions = QuestionnaireSubmission::get()->filter([ 'SecurityArchitectApprovalStatus' => ['approved'], ]); $grouped = []; foreach ($submissions as $submission) { $key = $submission->SecurityArchitectApprover->FirstName . '_' . $submission->SecurityArchitectApprover->Surname. substr($submission->Created, 0, 4).substr($submission->Created, 5, 2); if (array_key_exists($key, $grouped)) { $grouped[$key]['Num']++; } else { $grouped[$key] = [ 'ArchitectName' => $submission->SecurityArchitectApprover->FirstName." ".$submission->SecurityArchitectApprover->Surname, 'Year' => substr($submission->Created, 0, 4), 'Month' => substr($submission->Created, 5, 2), 'Num' => 1, ]; } } $year = array_column($grouped, 'Year'); $month = array_column($grouped, 'Month'); $count = array_column($grouped, 'Num'); array_multisort($year, SORT_DESC, $month, SORT_DESC, $count, SORT_DESC, $grouped); return ArrayList::create($grouped); } /** * @return string[] $fields */ public function columns() { $fields = [ 'ArchitectName' => 'Architect', 'Year' => 'Year', 'Month' => 'Month', 'Num' => 'Submission Count', ]; return $fields; } } /** * A custom report that generates the approvals by security architect per pillar per year */ class SDLTReports_ApprovalsBySAPerPillarPerYear extends Report { /** * @return string */ public function title() { return 'Approvals by security architect per pillar per year'; } /** * @param mixed|null $params * @return ArrayList */ public function sourceRecords($params = null) { $submissions = QuestionnaireSubmission::get()->filter([ 'SecurityArchitectApprovalStatus' => ['approved'], ]); $grouped = []; foreach ($submissions as $submission) { $key = $submission->SecurityArchitectApprover->FirstName . '_' . $submission->SecurityArchitectApprover->Surname. substr($submission->Created, 0, 4). $submission->Questionnaire()->Name; if (array_key_exists($key, $grouped)) { $grouped[$key]['Num']++; } else { $grouped[$key] = [ 'ArchitectName' => $submission->SecurityArchitectApprover->FirstName." ".$submission->SecurityArchitectApprover->Surname, 'Name' => $submission->Questionnaire()->Name, 'Year' => substr($submission->Created, 0, 4), 'Num' => 1, ]; } } $year = array_column($grouped, 'Year');; $count = array_column($grouped, 'Num'); array_multisort($year, SORT_DESC, $count, SORT_DESC, $grouped); return ArrayList::create($grouped); } /** * @return string[] $fields */ public function columns() { $fields = [ 'Name' => 'Pillar', 'ArchitectName' => 'Architect', 'Year' => 'Year', 'Num' => 'Submission Count', ]; return $fields; } } /** * A custom report that generates the number of taks per year (task type) */ class SDLTReports_TasksPerYear extends Report { /** * @return string */ public function title() { return 'Number of tasks per year (task type)'; } /** * @param mixed|null $params * @return ArrayList */ public function sourceRecords($params = null) { $submissions = TaskSubmission::get()->filterAny([ 'Status' => ['approved', 'complete'] ]); $grouped = []; foreach ($submissions as $submission) { $key = $submission->Task->Name . substr($submission->Created, 0, 4) ; if (array_key_exists($key, $grouped)) { $grouped[$key]['Num']++; } else { $grouped[$key] = [ 'TaskName' => $submission->Task->Name, 'Year' => substr($submission->Created, 0, 4), 'Num' => 1, ]; } } $year = array_column($grouped, 'Year'); $taskName = array_column($grouped, 'TaskName'); $count = array_column($grouped, 'Num');\ array_multisort($year, SORT_DESC, $taskName, SORT_ASC, $count, SORT_DESC, $grouped); return ArrayList::create($grouped); } /** * @return string[] $fields */ public function columns() { $fields = [ 'TaskName' => 'Task Name', 'Year' => 'Year', 'Num' => 'Task Count', ]; return $fields; } } /** * A custom report that generates the number of tasks per month per year (task type) */ class SDLTReports_TasksPerMonthPerYear extends Report { /** * @return string */ public function title() { return 'Number of tasks per month per year (task type)'; } /** * @param mixed|null $params * @return ArrayList */ public function sourceRecords($params = null) { $submissions = TaskSubmission::get()->filterAny([ 'Status' => ['approved', 'complete'] ]); $grouped = []; foreach ($submissions as $submission) { $key = $submission->Task->Name . substr($submission->Created, 0, 4) . substr($submission->Created, 5, 2); if (array_key_exists($key, $grouped)) { $grouped[$key]['Num']++; } else { $grouped[$key] = [ 'TaskName' => $submission->Task->Name, 'Year' => substr($submission->Created, 0, 4), 'Month' => substr($submission->Created, 5, 2), 'Num' => 1, ]; } } $year = array_column($grouped, 'Year'); $month = array_column($grouped, 'Month'); $taskName = array_column($grouped, 'TaskName'); $count = array_column($grouped, 'Num'); array_multisort($year, SORT_DESC, $month, SORT_DESC, $taskName, SORT_ASC, $count, SORT_DESC, $grouped); return ArrayList::create($grouped); } /** * @return string[] $fields */ public function columns() { $fields = [ 'TaskName' => 'Task Name', 'Year' => 'Year', 'Month' => 'Month', 'Num' => 'Task Count', ]; return $fields; } } /** * A custom report that generates the time between creation and approval */ class SDLTReports_TimeBetweenCreationAndApproval extends Report { /** * @return string */ public function title() { return 'Time between creation and approval'; } /** * @param mixed|null $params * @return ArrayList */ public function sourceRecords($params = null) { $submissions = QuestionnaireSubmission::get()->filter([ 'QuestionnaireStatus' => ['approved'] ]); $grouped = []; foreach ($submissions as $submission) { $createdDate = new DateTime(substr($submission->Created, 0, 10)); $lastEdited = new DateTime (substr($submission->LastEdited, 0, 10)); $dateDifference = $lastEdited->diff($createdDate)->format("%a"); $key = $dateDifference; if (array_key_exists($key, $grouped)) { $grouped[$key]['Num']++; } else { $grouped[$key] = [ 'DaysElasped' => $dateDifference, 'Num' => 1, ]; } } ksort($grouped); return ArrayList::create($grouped); } /** * @return string[] $fields */ public function columns() { $fields = [ 'DaysElasped' => 'Number of Days', 'Num' => 'Number of Submissions', ]; return $fields; } } /** * A custom report that generates the time between submitted for approval and fully approved */ class SDLTReports_TimeBetweenSubmittedAndApproval extends Report { /** * @return string */ public function title() { return 'Time between submitted for approval and fully approved'; } /** * @param mixed|null $params * @return ArrayList */ public function sourceRecords($params = null) { $submissions = QuestionnaireSubmission::get()->filter([ 'QuestionnaireStatus' => ['approved'], 'SubmittedForApprovalDate:not' => [null], 'LastEdited:not' => [null], ]); $grouped = []; foreach ($submissions as $submission) { $lastEditedDate = new DateTime(substr($submission->LastEdited, 0, 10)); $submittedApprovalDate = new DateTime(substr($submission->SubmittedForApprovalDate, 0, 10)); $dateDifference = $lastEditedDate->diff($submittedApprovalDate)->format("%a"); $key = $dateDifference; if (array_key_exists($key, $grouped)) { $grouped[$key]['Num']++; } else { $grouped[$key] = [ 'DaysElasped' => $dateDifference, 'Num' => 1, ]; } } ksort($grouped); return ArrayList::create($grouped); } /** * @return string[] $fields */ public function columns() { $fields = [ 'DaysElasped' => 'Number of Days', 'Num' => 'Number of Submissions', ]; return $fields; } } /** * A custom report that generates the time between awaiting approval and security architect approval */ class SDLTReports_TimeBetweenApprovalAndSAApproval extends Report { /** * @return string */ public function title() { return 'Time between "awaiting approval" and security architect approval'; } /** * @param mixed|null $params * @return ArrayList */ public function sourceRecords($params = null) { $submissions = QuestionnaireSubmission::get()->filter([ 'SecurityArchitectApprovalStatus' => ['approved'], 'SecurityArchitectStatusUpdateDate:not' => [null], 'SubmittedForApprovalDate:not' => [null], ]); $grouped = []; foreach ($submissions as $submission) { $SAStatusUpdateDate = new DateTime(substr($submission->SecurityArchitectStatusUpdateDate, 0, 10)); $submittedApprovalDate = new DateTime(substr($submission->SubmittedForApprovalDate, 0, 10)); $dateDifference = $SAStatusUpdateDate->diff($submittedApprovalDate)->format("%a"); $key = $dateDifference; if (array_key_exists($key, $grouped)) { $grouped[$key]['Num']++; } else { $grouped[$key] = [ 'DaysElasped' => $dateDifference, 'Num' => 1, ]; } } ksort($grouped); return ArrayList::create($grouped); } /** * @return string[] $fields */ public function columns() { $fields = [ 'DaysElasped' => 'Number of Days', 'Num' => 'Number of Submissions', ]; return $fields; } } /** * A custom report that generates all penetration tasks that have been completed */ class SDLTReports_CompletedPenetrationTests extends Report { /** * @return string */ public function title() { return 'Show all penetration tests that have been completed'; } /** * @param mixed|null $params * @return ArrayList */ public function sourceRecords($params = null) { $submissions = TaskSubmission::get()->filter([ 'Task.Name' => ['Penetration Test'], 'Status' => ['approved', 'complete'], 'QuestionnaireSubmission.ProductName:not' => [null] ])->sort('CompletedAt DESC'); $records = []; foreach ($submissions as $submission) { $records[] = [ 'ProductName' => $submission->QuestionnaireSubmission->ProductName, 'Submitter' => $submission->QuestionnaireSubmission->SubmitterName, 'SubmitterEmail' => $submission->QuestionnaireSubmission->SubmitterEmail, 'BusinessOwner' => $submission->QuestionnaireSubmission->BusinessOwnerEmailAddress, 'Date' => $submission->CompletedAt, 'Link' => $submission->QuestionnaireSubmission->getSummaryPageLink(), ]; } return ArrayList::create($records); } /** * @return string[] $fields */ public function columns() { $fields = [ 'ProductName' => 'Product Name', 'Submitter' => 'Submitter', 'SubmitterEmail' => 'Submitter Email', 'BusinessOwner' => 'Business Owner Contact', 'Date' => 'Date', 'Link' => 'Link', ]; return $fields; } } |