netresearch · CybotTM · Nov 30, 2023 · Nov 10, 2023 · Nov 10, 2023 · Nov 14, 2023
diff --git a/README.rst b/README.rst
@@ -200,7 +200,7 @@ Using OAuth to transmit work logs to Jira ticket system
    - After new Application is created click on action "edit" (the little pencil at the right to your new application)
       - Select "Incoming Authentication"
       - Consumer Key:
-           timetracker (or chose any other name you like)
+           timetracker (It must be unique among all application links!)
       - Consumer Name:
            TimeTracker (or chose any other name you like)
       - Public Key:

diff --git a/sql/004_ticketproject.sql b/sql/004_ticketproject.sql
@@ -0,0 +1,3 @@
+ALTER TABLE `projects` ADD COLUMN `jira_ticket` VARCHAR(63) NULL AFTER `jira_id`;
+ALTER TABLE `projects` ADD COLUMN `subtickets` TEXT DEFAULT '' AFTER `internal_jira_ticket_system`;
+
diff --git a/sql/full.sql b/sql/full.sql
@@ -158,6 +158,7 @@ CREATE TABLE `projects` (
   `customer_id` int(11) DEFAULT NULL,
   `name` varchar(127) NOT NULL,
   `jira_id` varchar(63) DEFAULT NULL,
+  `jira_ticket` VARCHAR(63) NULL,
   `ticket_system` int(11) NULL DEFAULT NULL,
   `active` tinyint(1) NOT NULL,
   `global` tinyint(1) unsigned NOT NULL DEFAULT '0',
@@ -173,6 +174,7 @@ CREATE TABLE `projects` (
   `additional_information_from_external` tinyint(1) NOT NULL,
   `internal_jira_project_key` VARCHAR(50) NULL,
   `internal_jira_ticket_system` INTEGER(11) NULL,
+  `subtickets` TEXT DEFAULT '',
   PRIMARY KEY (`id`),
   KEY `customer_id` (`customer_id`)
 ) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

diff --git a/src/Netresearch/TimeTrackerBundle/Command/TtSyncSubticketsCommand.php b/src/Netresearch/TimeTrackerBundle/Command/TtSyncSubticketsCommand.php
@@ -0,0 +1,75 @@
+<?php
+
+namespace Netresearch\TimeTrackerBundle\Command;
+
+use Netresearch\TimeTrackerBundle\Services\SubticketSyncService;
+
+use Symfony\Bundle\FrameworkBundle\Command\ContainerAwareCommand;
+use Symfony\Component\Console\Input\InputArgument;
+use Symfony\Component\Console\Input\InputInterface;
+use Symfony\Component\Console\Input\InputOption;
+use Symfony\Component\Console\Output\OutputInterface;
+use Symfony\Component\Console\Style\SymfonyStyle;
+
+class TtSyncSubticketsCommand extends ContainerAwareCommand
+{
+    protected function configure()
+    {
+        $this
+            ->setName('tt:sync-subtickets')
+            ->setDescription('Update project subtickets from Jira')
+            ->addArgument('project', InputArgument::OPTIONAL, 'Single project to update')
+        ;
+    }
+
+    protected function execute(InputInterface $input, OutputInterface $output)
+    {
+        $io = new SymfonyStyle($input, $output);
+
+        $projectId = $input->getArgument('project');
+
+        $projectRepo = $this->getContainer()->get('doctrine')
+            ->getRepository('NetresearchTimeTrackerBundle:Project');
+        if ($projectId) {
+            $project = $projectRepo->find($projectId);
+            if (!$project) {
+                $io->error('Project does not exist');
+                return 1;
+            }
+            $projects = [$project];
+        } else {
+            $projects = $projectRepo->createQueryBuilder('p')
+                ->where('p.ticketSystem IS NOT NULL')
+                ->getQuery()
+                ->getResult();
+        }
+
+        $stss = new SubticketSyncService($this->getContainer());
+
+        $output->writeln(
+            'Found ' . count($projects) . ' projects with ticket system',
+            OutputInterface::VERBOSITY_VERBOSE
+        );
+        foreach ($projects as $project) {
+            $output->writeln(
+                'Syncing ' . $project->getId() . ' ' . $project->getName(),
+                OutputInterface::VERBOSITY_VERBOSE
+            );
+            $subtickets = $stss->syncProjectSubtickets($project);
+
+            $output->writeln(
+                ' ' . count($subtickets) . ' subtickets found',
+                OutputInterface::VERBOSITY_VERBOSE
+            );
+            if (count($subtickets)) {
+                $output->writeln(
+                    ' ' . implode(',', $subtickets),
+                    OutputInterface::VERBOSITY_VERY_VERBOSE
+                );
+            }
+        }
+
+        return 0;
+    }
+
+}
diff --git a/src/Netresearch/TimeTrackerBundle/Controller/AdminController.php b/src/Netresearch/TimeTrackerBundle/Controller/AdminController.php
@@ -17,6 +17,7 @@
 use Netresearch\TimeTrackerBundle\Entity\TicketSystem;
 use Netresearch\TimeTrackerBundle\Entity\Activity;
 use Netresearch\TimeTrackerBundle\Helper\TimeHelper;
+use Netresearch\TimeTrackerBundle\Services\SubticketSyncService;
 
 /**
  * Class AdminController
@@ -173,6 +174,7 @@ public function saveProjectAction(Request $request)
             : null;
 
         $jiraId       = strtoupper($request->get('jiraId'));
+        $jiraTicket   = strtoupper($request->get('jiraTicket'));
         $active       = $request->get('active') ? $request->get('active') : 0;
         $global       = $request->get('global') ? $request->get('global') : 0;
         $estimation   = TimeHelper::readable2minutes($request->get('estimation') ? $request->get('estimation') : '0m');
@@ -238,6 +240,7 @@ public function saveProjectAction(Request $request)
             ->setName($name)
             ->setTicketSystem($ticketSystem)
             ->setJiraId($jiraId)
+            ->setJiraTicket($jiraTicket)
             ->setActive($active)
             ->setGlobal($global)
             ->setEstimation($estimation)
@@ -256,6 +259,18 @@ public function saveProjectAction(Request $request)
 
         $data = array($project->getId(), $name, $project->getCustomer()->getId(), $jiraId);
 
+        if ($ticketSystem) {
+            try {
+                $stss = new SubticketSyncService($this->container);
+                $subtickets = $stss->syncProjectSubtickets($project->getId());
+            } catch (\Exception $e) {
+                //we do not let it fail because creating a new project
+                // would lead to inconsistencies in the frontend
+                // ("project with that name exists already")
+                $data['message'] = $e->getMessage();
+            }
+        }
+
         return new JsonResponse($data);
     }
 
@@ -291,6 +306,66 @@ public function deleteProjectAction(Request $request)
         return new JsonResponse(array('success' => true));
     }
 
+    /**
+     * Update the subtickets for all projects.
+     */
+    public function syncAllProjectSubticketsAction(Request $request)
+    {
+        if (!$this->checkLogin($request)) {
+            return $this->getFailedLoginResponse();
+        }
+
+        $projectRepo = $this->container->get('doctrine')
+            ->getRepository('NetresearchTimeTrackerBundle:Project');
+        $projects = $projectRepo->createQueryBuilder('p')
+            ->where('p.ticketSystem IS NOT NULL')
+            ->getQuery()
+            ->getResult();
+
+        try {
+            $stss = new SubticketSyncService($this->container);
+
+            foreach ($projects as $project) {
+                $subtickets = $stss->syncProjectSubtickets($project->getId());
+            }
+
+            return new JsonResponse(
+                [
+                    'success' => true
+                ]
+            );
+        } catch (\Exception $e) {
+            return new Error($e->getMessage(), $e->getCode());
+        }
+    }
+
+    /**
+     * Fetch subtickets from Jira and update the project record's "subtickets" field.
+     *
+     * The project lead user's Jira tokens are used for access.
+     */
+    public function syncProjectSubticketsAction(Request $request)
+    {
+        if (!$this->checkLogin($request)) {
+            return $this->getFailedLoginResponse();
+        }
+
+        $projectId = (int) $request->get('project');
+
+        try {
+            $stss = new SubticketSyncService($this->container);
+            $subtickets = $stss->syncProjectSubtickets($projectId);
+            return new JsonResponse(
+                [
+                    'success'    => true,
+                    'subtickets' => $subtickets
+                ]
+            );
+        } catch (\Exception $e) {
+            return new Error($e->getMessage(), $e->getCode());
+        }
+    }
+
     /**
      * @param Request $request
      * @return Response

diff --git a/src/Netresearch/TimeTrackerBundle/Controller/CrudController.php b/src/Netresearch/TimeTrackerBundle/Controller/CrudController.php
@@ -10,6 +10,7 @@
 use Netresearch\TimeTrackerBundle\Entity\User;
 use Netresearch\TimeTrackerBundle\Response\Error;
 use Netresearch\TimeTrackerBundle\Helper\JiraApiException;
+use Netresearch\TimeTrackerBundle\Helper\JiraApiUnauthorizedException;
 use Netresearch\TimeTrackerBundle\Helper\JiraOAuthApi;
 use Netresearch\TimeTrackerBundle\Helper\TicketHelper;
 
@@ -37,11 +38,12 @@ public function deleteAction(Request $request)
 
             try {
                 $this->deleteJiraWorklog($entry);
+
+            } catch (JiraApiUnauthorizedException $e) {
+                // Invalid JIRA token
+                return new Error($e->getMessage(), 403, $e->getRedirectUrl());
+
             } catch (JiraApiException $e) {
-                if ($e->g
10000
etRedirectUrl()) {
-                    // Invalid JIRA token
-                    return new Error($e->getMessage(), 403, $e->getRedirectUrl());
-                }
                 $alert = $e->getMessage() . '<br />' .
                     $this->get('translator')->trans("Dataset was modified in Timetracker anyway");
             }
@@ -321,9 +323,8 @@ public function saveAction(Request $request)
                 $em->persist($entry);
                 $em->flush();
             } catch (JiraApiException $e) {
-                if ($e->getRedirectUrl()) {
-                    // Invalid JIRA token
-                    return new Error($e->getMessage(), 403, $e->getRedirectUrl());
+                if ($e instanceof JiraApiUnauthorizedException) {
+                    throw $e;
                 }
                 $alert = $e->getMessage() . '<br />' .
                     $this->get('translator')->trans("Dataset was modified in Timetracker anyway");
@@ -335,10 +336,15 @@ public function saveAction(Request $request)
             );
 
             return new JsonResponse($response);
+
+        } catch (JiraApiUnauthorizedException $e) {
+            // Invalid JIRA token
+            return new Error($e->getMessage(), 403, $e->getRedirectUrl(), $e);
+
         } catch (\Exception $e) {
-            return new Error($this->get('translator')->trans($e->getMessage()), 406);
+            return new Error($this->get('translator')->trans($e->getMessage()), 406, null, $e);
         } catch (\Throwable $exception) {
-            return new Error($exception->getMessage(), 503);
+            return new Error($exception->getMessage(), 503, null, $e);
         }
     }
 

diff --git a/src/Netresearch/TimeTrackerBundle/Controller/DefaultController.php b/src/Netresearch/TimeTrackerBundle/Controller/DefaultController.php
@@ -400,6 +400,32 @@ public function getAllProjectsAction(Request $request)
         return new JsonResponse($data);
     }
 
+    /**
+     * Return projects grouped by customer ID.
+     *
+     * Needed for frontend tracking autocompletion.
+     */
+    public function getProjectStructureAction(Request $request)
+    {
+        if (!$this->checkLogin($request)) {
+            return $this->login($request);
+        }
+
+        $userId = (int) $this->getUserId($request);
+        $doctrine = $this->getDoctrine();
+
+        // Send customers to the frontend for caching
+        $customers = $doctrine
+            ->getRepository('NetresearchTimeTrackerBundle:Customer')
+            ->getCustomersByUser($userId);
+
+        /* @var $projectRepo \Netresearch\TimeTrackerBundle\Repository\ProjectRepository */
+        $projectRepo = $doctrine->getRepository('NetresearchTimeTrackerBundle:Project');
+        $projectStructure = $projectRepo->getProjectStructure($userId, $customers);
+
+        return new JsonResponse($projectStructure);
+    }
+
     /**
      * @return Response
      */

diff --git a/src/Netresearch/TimeTrackerBundle/Entity/Project.php b/src/Netresearch/TimeTrackerBundle/Entity/Project.php
@@ -52,6 +52,20 @@ class Project extends Base
      */
     protected $jiraId;
 
+    /**
+     * @ORM\Column(type="string", name="jira_ticket")
+     */
+    protected $jiraTicket;
+
+    /**
+     * Ticket numbers that are subtickets of $jiraTicket
+     * Gets calculated automatically.
+     * Comma-separated string.
+     *
+     * @ORM\Column(type="string", name="subtickets")
+     */
+    protected $subtickets;
+
     /**
      * @ORM\ManyToOne(targetEntity="TicketSystem", inversedBy="projects")
      * @ORM\JoinColumn(name="ticket_system", referencedColumnName="id")
@@ -353,6 +367,37 @@ public function setJiraId($jiraId)
         return $this;
     }
 
+    public function getJiraTicket()
+    {
+        return $this->jiraTicket;
+    }
+
+    public function setJiraTicket($jiraTicket)
+    {
+        if ($jiraTicket === '') {
+            $jiraTicket = null;
+        }
+        $this->jiraTicket = $jiraTicket;
+        return $this;
+    }
+
+    public function getSubtickets()
+    {
+        if ($this->subtickets == '') {
+            return [];
+        }
+        return explode(',', $this->subtickets);
+    }
+
+    public function setSubtickets($subtickets)
+    {
+        if (is_array($subtickets)) {
+            $subtickets = implode(',', $subtickets);
+        }
+        $this->subtickets = $subtickets;
+        return $this;
+    }
+
     /**
      * @return TicketSystem $ticketSystem
      */

diff --git a/src/Netresearch/TimeTrackerBundle/Helper/JiraApiException.php b/src/Netresearch/TimeTrackerBundle/Helper/JiraApiException.php
@@ -21,11 +21,11 @@ class JiraApiException extends \Exception
      * @param $code
      * @param null $redirectUrl
      */
-    public function __construct($message, $code, $redirectUrl = null)
+    public function __construct($message, $code, $redirectUrl = null, \Throwable $previous = null)
     {
         $this->redirectUrl = $redirectUrl;
         $message = 'JiraApi: '. $message;
-        parent::__construct($message, $code, null);
+        parent::__construct($message, $code, $previous);
     }
 
     /**

diff --git a/src/Netresearch/TimeTrackerBundle/Helper/JiraApiUnauthorizedException.php b/src/Netresearch/TimeTrackerBundle/Helper/JiraApiUnauthorizedException.php
@@ -0,0 +1,10 @@
+<?php
+
+namespace Netresearch\TimeTrackerBundle\Helper;
+
+/**
+ * The user needs to authorize in Jira first and get an OAuth token
+ */
+class JiraApiUnauthorizedException extends JiraApiException
+{
+}
Original file line number	Diff line number	Diff line change
		@@ -0,0 +1,3 @@
		ALTER TABLE `projects` ADD COLUMN `jira_ticket` VARCHAR(63) NULL AFTER `jira_id`;
		ALTER TABLE `projects` ADD COLUMN `subtickets` TEXT DEFAULT '' AFTER `internal_jira_ticket_system`;