1. PHP / Говнокод #20250

    0

    1. 01
    2. 02
    3. 03
    4. 04
    5. 05
    6. 06
    7. 07
    8. 08
    9. 09
    10. 10
    11. 11
    12. 12
    13. 13
    14. 14
    15. 15
    16. 16
    17. 17
    18. 18
    19. 19
    20. 20
    21. 21
    22. 22
    23. 23
    24. 24
    25. 25
    26. 26
    27. 27
    28. 28
    29. 29
    30. 30
    31. 31
    32. 32
    33. 33
    34. 34
    35. 35
    36. 36
    37. 37
    38. 38
    public function getOrdersByMonth($year)
        {
    
            $data = array();
    
            $overallquanities = self::$DBH->query('SELECT SUM(quanity) as quanity, DATE_FORMAT(timestamp,\'%m\') as mdata,phone,comment FROM statistics WHERE DATE_FORMAT(timestamp,\'%Y\')=\'' . $year . '\' AND phone!=(\'test\') AND phone!=(\'тест\') AND comment!=(\'test\') AND comment!=(\'тест\') GROUP BY DATE_FORMAT(timestamp,\'%Y %m\')')->fetchAll(PDO::FETCH_ASSOC);
            $overallsumms = self::$DBH->query('SELECT SUM(cost) as cost, DATE_FORMAT(timestamp,\'%Y %m %d\') as mdata FROM statistics WHERE DATE_FORMAT(timestamp,\'%Y\')=\'' . $year . '\' AND phone!=(\'test\') AND phone!=(\'тест\') AND comment!=(\'test\') AND comment!=(\'тест\') GROUP BY DATE_FORMAT(timestamp,\'%Y %m\')')->fetchAll(PDO::FETCH_ASSOC);
            $overallorders = self::$DBH->query('SELECT COUNT(DISTINCT currcount) as count, DATE_FORMAT(timestamp,\'%Y %m %d\') as mdata FROM statistics WHERE DATE_FORMAT(timestamp,\'%Y\')=\'' . $year . '\' AND phone!=(\'test\') AND phone!=(\'тест\') AND comment!=(\'test\') AND comment!=(\'тест\') GROUP BY DATE_FORMAT(timestamp,\'%Y %m\')')->fetchAll(PDO::FETCH_ASSOC);
            $overallcomplex = self::$DBH->query('SELECT COUNT(*) as count, DATE_FORMAT(timestamp,\'%Y %m %d\') as mdata FROM statistics WHERE DATE_FORMAT(timestamp,\'%Y\')=\'' . $year . '\' AND suborder=2 AND phone!=(\'test\') AND phone!=(\'тест\') AND comment!=(\'test\') AND comment!=(\'тест\') GROUP BY DATE_FORMAT(timestamp,\'%Y %m\')')->fetchAll(PDO::FETCH_ASSOC);
            $overall_glnz = self::$DBH->query('SELECT SUM(quanity) as quanity,SUM(cost) as cost, DATE_FORMAT(timestamp,\'%Y %m %d\') as mdata FROM statistics WHERE DATE_FORMAT(timestamp,\'%Y\')=\'' . $year . '\' AND paper=\'glnz\' AND phone!=(\'test\') AND phone!=(\'тест\') AND comment!=(\'test\') AND comment!=(\'тест\') GROUP BY DATE_FORMAT(timestamp,\'%Y %m\')')->fetchAll(PDO::FETCH_ASSOC);
            //TODO: $overall_mat сделать просто рассчет вычитание из общего количества
            $overall_mat = self::$DBH->query('SELECT SUM(quanity) as quanity,SUM(cost) as cost, DATE_FORMAT(timestamp,\'%Y %m %d\') as mdata FROM statistics WHERE DATE_FORMAT(timestamp,\'%Y\')=\'' . $year . '\' AND paper=\'mat\' AND phone!=(\'test\') AND phone!=(\'тест\') AND comment!=(\'test\') AND comment!=(\'тест\') GROUP BY DATE_FORMAT(timestamp,\'%Y %m\')')->fetchAll(PDO::FETCH_ASSOC);
            $overall_autocor = self::$DBH->query('SELECT SUM(quanity) as quanity,SUM(cost) as cost, DATE_FORMAT(timestamp,\'%Y %m %d\') as mdata FROM statistics WHERE DATE_FORMAT(timestamp,\'%Y\')=\'' . $year . '\' AND autocor=1 AND phone!=(\'test\') AND phone!=(\'тест\') AND comment!=(\'test\') AND comment!=(\'тест\') GROUP BY DATE_FORMAT(timestamp,\'%Y %m\')')->fetchAll(PDO::FETCH_ASSOC);
            $formats = self::$DBH->query('SELECT DATE_FORMAT(timestamp,\'%m\') as mdata, format, SUM(quanity) as quanity,SUM(cost) as cost FROM statistics WHERE DATE_FORMAT(timestamp,\'%Y\')=\'' . $year . '\' AND phone!=(\'test\') AND phone!=(\'тест\') AND comment!=(\'test\') AND comment!=(\'тест\') GROUP BY DATE_FORMAT(timestamp,\'%Y %m\'),format')->fetchAll(PDO::FETCH_GROUP | PDO::FETCH_ASSOC);
    
            //************* Собираем итоги **************//
            $whereOption = 'AND phone!=(\'test\') AND phone!=(\'тест\') AND comment!=(\'test\') AND comment!=(\'тест\') AND comment NOT LIKE \'%test%\' AND comment NOT LIKE \'%тест%\'';
            
            $totalOrders = self::$DBH->query('SELECT COUNT(DISTINCT currcount) as totalOrders FROM statistics WHERE DATE_FORMAT(timestamp,\'%Y\') =\''.$year.'\' '.$whereOption)->fetchAll(PDO::FETCH_ASSOC);
            
            $totalSum = self::$DBH->query('SELECT SUM(cost) as totalSum FROM statistics WHERE DATE_FORMAT(timestamp,\'%Y\') =\''.$year.'\' '.$whereOption)->fetchAll(PDO::FETCH_ASSOC);
    
            $totalGLNZ = self::$DBH->query('SELECT SUM(quanity) as paperCount, SUM(cost) as paperCost FROM statistics WHERE DATE_FORMAT(timestamp,\'%Y\') = \''.$year.'\' AND paper=\'glnz\' '.$whereOption)->fetchAll(PDO::FETCH_ASSOC);
    
            $totalMAT = self::$DBH->query('SELECT SUM(quanity) as paperCount, SUM(cost) as paperCost FROM statistics WHERE DATE_FORMAT(timestamp,\'%Y\') = \''.$year.'\' AND paper=\'mat\' '.$whereOption)->fetchAll(PDO::FETCH_ASSOC);
    
            $totalSubsCount = self::$DBH->query('SELECT COUNT(suborder) as totalSubsCount FROM statistics WHERE DATE_FORMAT(timestamp,\'%Y\') =\''.$year.'\' '.$whereOption)->fetchAll(PDO::FETCH_ASSOC);
    
            $totalPhotos = self::$DBH->query('SELECT SUM(quanity) as totalPhotos FROM statistics WHERE DATE_FORMAT(timestamp,\'%Y\') =\''.$year.'\' '.$whereOption)->fetchAll(PDO::FETCH_ASSOC);
    
            //******************************************//
    
           // $this->Logging(self::$DBH->query('SELECT quanity, DATE_FORMAT(timestamp,\'%m\') as mdata,phone,comment FROM statistics WHERE DATE_FORMAT(timestamp,\'%Y\')=\'' . $year . '\'')->fetchAll(PDO::FETCH_ASSOC));
    
            /*$this->Logging($data);*/
    
            return $data;
        }

    Выводим статистику и итоги

    Запостил: phpMASTER666, 22 Июня 2016

    Комментарии (7) RSS

    • >>\''.$year.'\
      хороший пыховец даже при помощи PDO соберет запрос в ручную, похерит execution plan, и устроит sql инъекцию
      Ответить
      • PDO тут вообще ни при чём. PDO — это всего лишь единый интерфейс к нескольким драйверам баз данных. PDO не лучше и не хуже того же mysqli/postgresql/пригоревшего_питуха.

        Другое дело, что пыховцы протаскивают инъекции через всё, даже через фреймворки.
        Ответить
    • Зачем мне параметры, мне некогда с ними ебаться, лучше я еще раз соберу запрос вручную...
      Ответить

    Добавить комментарий