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

    • ava guestinho 22.06.2016 17:49 # +3

      >>\''.$year.'\
      хороший пыховец даже при помощи PDO соберет запрос в ручную, похерит execution plan, и устроит sql инъекцию
      Ответить

      • ava inkanus-gray 23.06.2016 16:00 # 0

        PDO тут вообще ни при чём. PDO — это всего лишь единый интерфейс к нескольким драйверам баз данных. PDO не лучше и не хуже того же mysqli/postgresql/пригоревшего_питуха.

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

    • ava Vince 23.06.2016 08:33 # 0

      Зачем мне параметры, мне некогда с ними ебаться, лучше я еще раз соберу запрос вручную...
      Ответить

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