StatisticService.php (7000B)
1 <?php 2 3 namespace PartKeepr\StatisticBundle\Services; 4 5 use Doctrine\ORM\EntityManager; 6 use PartKeepr\PartBundle\Entity\PartMeasurementUnit; 7 use PartKeepr\StatisticBundle\Entity\StatisticSnapshot; 8 use PartKeepr\StatisticBundle\Entity\StatisticSnapshotUnit; 9 10 class StatisticService 11 { 12 /** 13 * @var EntityManager 14 */ 15 private $entityManager; 16 17 public function __construct(EntityManager $entityManager) 18 { 19 $this->entityManager = $entityManager; 20 } 21 22 /** 23 * Returns the part count. 24 * 25 * @param bool|false $withoutPrice Set to true to retrieve all parts where the average price is null 26 * 27 * @return mixed 28 */ 29 public function getPartCount($withoutPrice = false) 30 { 31 $dql = "SELECT COUNT(p.id) FROM PartKeepr\PartBundle\Entity\Part p"; 32 33 if ($withoutPrice === true) { 34 $dql .= ' WHERE p.averagePrice > 0'; 35 } 36 37 return $this->entityManager->createQuery($dql)->getSingleScalarResult(); 38 } 39 40 /** 41 * Returns the part category count. 42 * 43 * @return int 44 */ 45 public function getPartCategoryCount() 46 { 47 $dql = "SELECT COUNT(c.id) FROM PartKeepr\PartBundle\Entity\PartCategory c"; 48 49 return $this->entityManager->createQuery($dql)->getSingleScalarResult(); 50 } 51 52 /** 53 * Returns the total price for all parts. Only parts with a price are calculated. 54 * 55 * @return float The total price 56 */ 57 public function getTotalPrice() 58 { 59 $dql = "SELECT SUM(p.averagePrice * p.stockLevel) FROM PartKeepr\PartBundle\Entity\Part p"; 60 61 return $this->entityManager->createQuery($dql)->getSingleScalarResult(); 62 } 63 64 /** 65 * Returns the average price for all parts. Only parts with a price are calculated. 66 * 67 * @return float The average price 68 */ 69 public function getAveragePrice() 70 { 71 $dql = "SELECT AVG(p.averagePrice) FROM PartKeepr\PartBundle\Entity\Part p"; 72 73 return $this->entityManager->createQuery($dql)->getSingleScalarResult(); 74 } 75 76 /** 77 * Returns the part counts per part unit. 78 * 79 * @return array An array of arrays with the keys "name" and "stockLevel" 80 */ 81 public function getUnitCounts() 82 { 83 $dql = 'SELECT SUM(p.stockLevel) AS stockLevel, pu AS partMeasurementUnit FROM '; 84 $dql .= 'PartKeepr\PartBundle\Entity\PartMeasurementUnit pu LEFT JOIN pu.parts p GROUP BY pu.id'; 85 86 return $this->entityManager->createQuery($dql)->getArrayResult(); 87 } 88 89 /** 90 * Returns the range of all recorded statistic snapshots. 91 */ 92 public function getStatisticRange() 93 { 94 $dql = 'SELECT MIN(sts.dateTime) AS startDate, MAX(sts.dateTime) AS endDate FROM PartKeepr\\StatisticBundle\\Entity\\StatisticSnapshot sts'; 95 $query = $this->entityManager->createQuery($dql); 96 97 return $query->getSingleResult(); 98 } 99 100 /** 101 * Gets the sampled statistics. 102 * 103 * @param \DateTime $startDate 104 * @param \DateTime $endDate 105 * @param int $sampleSize 106 * 107 * @return array 108 */ 109 public function getSampledStatistics(\DateTime $startDate, \DateTime $endDate, $sampleSize = 25) 110 { 111 if ($startDate->getTimestamp() > $endDate->getTimestamp()) { 112 // Swap both times 113 list($startDate, $endDate) = [$endDate, $startDate]; 114 } 115 116 $intervalSize = intval(($endDate->getTimestamp() - $startDate->getTimestamp()) / $sampleSize); 117 118 $queryStartTime = clone $startDate; 119 $queryEndTime = clone $startDate; 120 $queryEndTime->add(new \DateInterval('PT'.$intervalSize.'S')); 121 122 $partUnitQuery = "SELECT pu FROM PartKeepr\PartBundle\Entity\PartMeasurementUnit pu"; 123 $query = $this->entityManager->createQuery($partUnitQuery); 124 125 $aPartUnits = $query->getResult(); 126 127 $dql = "SELECT AVG(sts.parts) AS parts, AVG(sts.categories) AS categories FROM PartKeepr\StatisticBundle\Entity\StatisticSnapshot sts WHERE sts.dateTime >= :start AND sts.dateTime <= :end"; 128 $mainQuery = $this->entityManager->createQuery($dql); 129 130 $dql = "SELECT AVG(stsu.stockLevel) AS stockLevel FROM PartKeepr\StatisticBundle\Entity\StatisticSnapshotUnit stsu JOIN stsu.statisticSnapshot sts WHERE sts.dateTime >= :start AND sts.dateTime <= :end AND stsu.partUnit = :partUnit"; 131 $subQuery = $this->entityManager->createQuery($dql); 132 133 $aRecords = []; 134 135 for ($i = 0; $i < $sampleSize; $i++) { 136 $mainQuery->setParameter('start', $queryStartTime); 137 $mainQuery->setParameter('end', $queryEndTime); 138 139 $result = $mainQuery->getResult(); 140 141 $record = $result[0]; 142 143 if ($record['parts'] !== null) { 144 $record['parts'] = floatval($record['parts']); 145 } 146 147 if ($record['categories'] !== null) { 148 $record['categories'] = floatval($record['categories']); 149 } 150 151 foreach ($aPartUnits as $partUnit) { 152 /* 153 * @var $partUnit PartMeasurementUnit 154 */ 155 $subQuery->setParameter('start', $queryStartTime); 156 $subQuery->setParameter('end', $queryEndTime); 157 $subQuery->setParameter('partUnit', $partUnit); 158 159 $aResult = $subQuery->getResult(); 160 161 if ($aResult[0]['stockLevel'] !== null) { 162 $record['units'][$partUnit->getName()] = floatval($aResult[0]['stockLevel']); 163 } else { 164 $record['units'][$partUnit->getName()] = null; 165 } 166 } 167 168 $record['start'] = $queryStartTime->format('Y-m-d H:i:s'); 169 170 if ($record['parts'] !== null) { 171 $aRecords[] = $record; 172 } 173 174 $queryStartTime->add(new \DateInterval('PT'.$intervalSize.'S')); 175 $queryEndTime->add(new \DateInterval('PT'.$intervalSize.'S')); 176 } 177 178 return $aRecords; 179 } 180 181 public function createStatisticSnapshot() 182 { 183 $snapshot = new StatisticSnapshot(); 184 $snapshot->setParts($this->getPartCount()); 185 $snapshot->setCategories($this->getPartCategoryCount()); 186 187 $unitCounts = $this->getUnitCounts(); 188 $partUnitRepository = $this->entityManager->getRepository('PartKeeprPartBundle:PartMeasurementUnit'); 189 190 foreach ($unitCounts as $unitCount) { 191 $snapshotUnit = new StatisticSnapshotUnit(); 192 $snapshotUnit->setPartUnit($partUnitRepository->findOneBy(['id' => $unitCount['partMeasurementUnit']['id']])); 193 $snapshotUnit->setStatisticSnapshot($snapshot); 194 195 if ($unitCount['stockLevel'] !== null) { 196 $snapshotUnit->setStockLevel($unitCount['stockLevel']); 197 } else { 198 $snapshotUnit->setStockLevel(0); 199 } 200 201 $snapshot->getUnits()[] = $snapshotUnit; 202 } 203 204 $this->entityManager->persist($snapshot); 205 $this->entityManager->flush(); 206 } 207 }