Skip to content

Instantly share code, notes, and snippets.

@EvgenyOrekhov

EvgenyOrekhov/Data manipulation exercise.md

Last active May 17, 2020 09:01
Show Gist options
  • Save EvgenyOrekhov/9411b5a7f10eeb31b4e85bff5a6d0c19 to your computer and use it in GitHub Desktop.
Save EvgenyOrekhov/9411b5a7f10eeb31b4e85bff5a6d0c19 to your computer and use it in GitHub Desktop.
Download ZIP
Data manipulation exercise
Raw
clojure.clj
(ns tasks-overview.core
(:gen-class)
(:require [clojure.data.json :as json])
(:use [clojure.pprint :only (pprint)]))
(defn get-report [expoints statuses tasks]
(map (fn [expoint]
(reduce (fn [carry status]
(assoc
carry
(:title status)
(count (filter (fn [task]
(and
(= (:expoint_id task) (:id expoint))
(= (:status_id task) (:id status))))
tasks))))
{:title (:title expoint)}
statuses))
expoints))
(defn -main []
(pprint (get-report
[{:id 2, :title "Сырье - Цеха №51, 52"} {:id 3, :title "Плановое ТО - Цеха №51, 52"} {:id 1, :title "Ремонт - Цеха №51, 52"}]
[{:id 7, :title "Повторная", :_links {:self {:href "/dispatch/tasks/all?TaskSearch%5Bstatus_id%5D=7"}}} {:id 1, :title "Новая", :_links {:self {:href "/dispatch/tasks/all?TaskSearch%5Bstatus_id%5D=1"}}} {:id 2, :title "В работе", :_links {:self {:href "/dispatch/tasks/all?TaskSearch%5Bstatus_id%5D=2"}}} {:id 3, :title "Решена", :_links {:self {:href "/dispatch/tasks/all?TaskSearch%5Bstatus_id%5D=3"}}} {:id 6, :title "Приостановлена", :_links {:self {:href "/dispatch/tasks/all?TaskSearch%5Bstatus_id%5D=6"}}}]
[{:expoint_id 2, :status_id 2} {:expoint_id 2, :status_id 1} {:expoint_id 2, :status_id 1} {:expoint_id 1, :status_id 1} {:expoint_id 2, :status_id 1} {:expoint_id 1, :status_id 2} {:expoint_id 1, :status_id 1} {:expoint_id 1, :status_id 7} {:expoint_id 1, :status_id 2} {:expoint_id 1, :status_id 1} {:expoint_id 1, :status_id 1} {:expoint_id 1, :status_id 1} {:expoint_id 3, :status_id 1} {:expoint_id 1, :status_id 7} {:expoint_id 1, :status_id 1} {:expoint_id 1, :status_id 6} {:expoint_id 1, :status_id 3} {:expoint_id 2, :status_id 2} {:expoint_id 1, :status_id 7} {:expoint_id 1, :status_id 2} {:expoint_id 1, :status_id 2} {:expoint_id 1, :status_id 7} {:expoint_id 1, :status_id 7} {:expoint_id 1, :status_id 7} {:expoint_id 1, :status_id 7} {:expoint_id 3, :status_id 7} {:expoint_id 1, :status_id 2} {:expoint_id 3, :status_id 1} {:expoint_id 2, :status_id 1} {:expoint_id 1, :status_id 2} {:expoint_id 1, :status_id 1} {:expoint_id 3, :status_id 1} {:expoint_id 3, :status_id 2} {:expoint_id 1, :status_id 1} {:expoint_id 1, :status_id 1} {:expoint_id 1, :status_id 1} {:expoint_id 2, :status_id 1} {:expoint_id 1, :status_id 1} {:expoint_id 1, :status_id 1} {:expoint_id 3, :status_id 1} {:expoint_id 1, :status_id 2} {:expoint_id 1, :status_id 1} {:expoint_id 3, :status_id 1} {:expoint_id 1, :status_id 6} {:expoint_id 1, :status_id 3} {:expoint_id 1, :status_id 1} {:expoint_id 1, :status_id 1} {:expoint_id 1, :status_id 7} {:expoint_id 1, :status_id 6} {:expoint_id 3, :status_id 1} {:expoint_id 1, :status_id 1} {:expoint_id 1, :status_id 2} {:expoint_id 1, :status_id 7} {:expoint_id 3, :status_id 2} {:expoint_id 1, :status_id 1} {:expoint_id 1, :status_id 6} {:expoint_id 1, :status_id 6} {:expoint_id 2, :status_id 1} {:expoint_id 1, :status_id 2} {:expoint_id 1, :status_id 1} {:expoint_id 1, :status_id 1} {:expoint_id 2, :status_id 1} {:expoint_id 1, :status_id 1} {:expoint_id 2, :status_id 1} {:expoint_id 1, :status_id 7} {:expoint_id 1, :status_id 1} {:expoint_id 2, :status_id 1} {:expoint_id 1, :status_id 7} {:expoint_id 1, :status_id 1} {:expoint_id 1, :status_id 2} {:expoint_id 2, :status_id 1} {:expoint_id 2, :status_id 6} {:expoint_id 2, :status_id 1} {:expoint_id 1, :status_id 7} {:expoint_id 1, :status_id 1} {:expoint_id 2, :status_id 1} {:expoint_id 1, :status_id 2} {:expoint_id 1, :status_id 2} {:expoint_id 2, :status_id 1} {:expoint_id 1, :status_id 7}])))
Raw
Data manipulation exercise.md

Data manipulation exercise

На любом языке программирования написать функцию, которая высчитывает количество задач по каждому статусу для каждого проекта.

Пример входных данных:

projects = [
    {"id": 2, "title": "Canape CRM"},
    {"id": 3, "title": "Мастер"},
    {"id": 1, "title": "Canape CMS"}
]
statuses = [
    {"id": 7, "title": "Площадка разворачивается"},
    {"id": 1, "title": "Площадка обновляется"},
    {"id": 2, "title": "Выполняется резервное копирование"},
    {"id": 3, "title": "ОК"},
    {"id": 6, "title": "Ошибка"}
]
tasks = [{"project_id": 2, "status_id": 2}, {"project_id": 2, "status_id": 1}, {"project_id": 2, "status_id": 1}, {"project_id": 1, "status_id": 1}, {"project_id": 2, "status_id": 1}, {"project_id": 1, "status_id": 2}, {"project_id": 1, "status_id": 1}, {"project_id": 1, "status_id": 7}, {"project_id": 1, "status_id": 2}, {"project_id": 1, "status_id": 1}, {"project_id": 1, "status_id": 1}, {"project_id": 1, "status_id": 1}, {"project_id": 3, "status_id": 1}, {"project_id": 1, "status_id": 7}, {"project_id": 1, "status_id": 1}, {"project_id": 1, "status_id": 6}, {"project_id": 1, "status_id": 3}, {"project_id": 2, "status_id": 2}, {"project_id": 1, "status_id": 7}, {"project_id": 1, "status_id": 2}, {"project_id": 1, "status_id": 2}, {"project_id": 1, "status_id": 7}, {"project_id": 1, "status_id": 7}, {"project_id": 1, "status_id": 7}, {"project_id": 1, "status_id": 7}, {"project_id": 3, "status_id": 7}, {"project_id": 1, "status_id": 2}, {"project_id": 3, "status_id": 1}, {"project_id": 2, "status_id": 1}, {"project_id": 1, "status_id": 2}, {"project_id": 1, "status_id": 1}, {"project_id": 3, "status_id": 1}, {"project_id": 3, "status_id": 2}, {"project_id": 1, "status_id": 1}, {"project_id": 1, "status_id": 1}, {"project_id": 1, "status_id": 1}, {"project_id": 2, "status_id": 1}, {"project_id": 1, "status_id": 1}, {"project_id": 1, "status_id": 1}, {"project_id": 3, "status_id": 1}, {"project_id": 1, "status_id": 2}, {"project_id": 1, "status_id": 1}, {"project_id": 3, "status_id": 1}, {"project_id": 1, "status_id": 6}, {"project_id": 1, "status_id": 3}, {"project_id": 1, "status_id": 1}, {"project_id": 1, "status_id": 1}, {"project_id": 1, "status_id": 7}, {"project_id": 1, "status_id": 6}, {"project_id": 3, "status_id": 1}, {"project_id": 1, "status_id": 1}, {"project_id": 1, "status_id": 2}, {"project_id": 1, "status_id": 7}, {"project_id": 3, "status_id": 2}, {"project_id": 1, "status_id": 1}, {"project_id": 1, "status_id": 6}, {"project_id": 1, "status_id": 6}, {"project_id": 2, "status_id": 1}, {"project_id": 1, "status_id": 2}, {"project_id": 1, "status_id": 1}, {"project_id": 1, "status_id": 1}, {"project_id": 2, "status_id": 1}, {"project_id": 1, "status_id": 1}, {"project_id": 2, "status_id": 1}, {"project_id": 1, "status_id": 7}, {"project_id": 1, "status_id": 1}, {"project_id": 2, "status_id": 1}, {"project_id": 1, "status_id": 7}, {"project_id": 1, "status_id": 1}, {"project_id": 1, "status_id": 2}, {"project_id": 2, "status_id": 1}, {"project_id": 2, "status_id": 6}, {"project_id": 2, "status_id": 1}, {"project_id": 1, "status_id": 7}, {"project_id": 1, "status_id": 1}, {"project_id": 2, "status_id": 1}, {"project_id": 1, "status_id": 2}, {"project_id": 1, "status_id": 2}, {"project_id": 2, "status_id": 1}, {"project_id": 1, "status_id": 7}]

Пример выходных данных:

[
    {
        "title": "Canape CRM",
        "Площадка разворачивается": 0,
        "Площадка обновляется": 13,
        "Выполняется резервное копирование": 2,
        "ОК": 0,
        "Ошибка": 1
    },
    {
        "title": "Мастер",
        "Площадка разворачивается": 1,
        "Площадка обновляется": 6,
        "Выполняется резервное копирование": 2,
        "ОК": 0,
        "Ошибка": 0
    },
    {
        "title": "Canape CMS",
        "Площадка разворачивается": 13,
        "Площадка обновляется": 23,
        "Выполняется резервное копирование": 12,
        "ОК": 2,
        "Ошибка": 5
    }
]

Главный критерий -- элегантность решения.

Raw
javascript-optimized.js
"use strict";
/**
* Высчитывает количество задач по каждому статусу для каждого проекта.
*
* @param projects {Array}
* @param statuses {Array}
* @param tasks {Array}
*
* @returns {Array}
*/
function getReport(projects, statuses, tasks) {
function index(array) {
return array.reduce(function (acc, item) {
acc[item.id] = item.title;
return acc;
}, {});
}
var indexedProjects = index(projects);
var indexedStatuses = index(statuses);
function createInitialValue(projectId) {
return statuses.reduce(function (statusesAcc, status) {
statusesAcc[status.title] = 0;
return statusesAcc;
}, {title: indexedProjects[projectId]});
}
var result = tasks.reduce(function (acc, task) {
if (acc[task.project_id] === undefined) {
acc[task.project_id] = createInitialValue(task.project_id);
}
var statusTitle = indexedStatuses[task.status_id];
acc[task.project_id][statusTitle] += 1;
return acc;
}, {});
return Object.values(result);
}
var validResult = [
{
"title": "Canape CRM",
"Площадка разворачивается": 0,
"Площадка обновляется": 13,
"Выполняется резервное копирование": 2,
"ОК": 0,
"Ошибка": 1
},
{
"title": "Мастер",
"Площадка разворачивается": 1,
"Площадка обновляется": 6,
"Выполняется резервное копирование": 2,
"ОК": 0,
"Ошибка": 0
},
{
"title": "Canape CMS",
"Площадка разворачивается": 13,
"Площадка обновляется": 23,
"Выполняется резервное копирование": 12,
"ОК": 2,
"Ошибка": 5
}
];
var result = getReport(
[
{"id": 2, "title": "Canape CRM"},
{"id": 3, "title": "Мастер"},
{"id": 1, "title": "Canape CMS"}
],
[
{"id": 7, "title": "Площадка разворачивается"},
{"id": 1, "title": "Площадка обновляется"},
{"id": 2, "title": "Выполняется резервное копирование"},
{"id": 3, "title": "ОК"},
{"id": 6, "title": "Ошибка"}
],
[{"project_id": 2, "status_id": 2}, {"project_id": 2, "status_id": 1}, {"project_id": 2, "status_id": 1}, {"project_id": 1, "status_id": 1}, {"project_id": 2, "status_id": 1}, {"project_id": 1, "status_id": 2}, {"project_id": 1, "status_id": 1}, {"project_id": 1, "status_id": 7}, {"project_id": 1, "status_id": 2}, {"project_id": 1, "status_id": 1}, {"project_id": 1, "status_id": 1}, {"project_id": 1, "status_id": 1}, {"project_id": 3, "status_id": 1}, {"project_id": 1, "status_id": 7}, {"project_id": 1, "status_id": 1}, {"project_id": 1, "status_id": 6}, {"project_id": 1, "status_id": 3}, {"project_id": 2, "status_id": 2}, {"project_id": 1, "status_id": 7}, {"project_id": 1, "status_id": 2}, {"project_id": 1, "status_id": 2}, {"project_id": 1, "status_id": 7}, {"project_id": 1, "status_id": 7}, {"project_id": 1, "status_id": 7}, {"project_id": 1, "status_id": 7}, {"project_id": 3, "status_id": 7}, {"project_id": 1, "status_id": 2}, {"project_id": 3, "status_id": 1}, {"project_id": 2, "status_id": 1}, {"project_id": 1, "status_id": 2}, {"project_id": 1, "status_id": 1}, {"project_id": 3, "status_id": 1}, {"project_id": 3, "status_id": 2}, {"project_id": 1, "status_id": 1}, {"project_id": 1, "status_id": 1}, {"project_id": 1, "status_id": 1}, {"project_id": 2, "status_id": 1}, {"project_id": 1, "status_id": 1}, {"project_id": 1, "status_id": 1}, {"project_id": 3, "status_id": 1}, {"project_id": 1, "status_id": 2}, {"project_id": 1, "status_id": 1}, {"project_id": 3, "status_id": 1}, {"project_id": 1, "status_id": 6}, {"project_id": 1, "status_id": 3}, {"project_id": 1, "status_id": 1}, {"project_id": 1, "status_id": 1}, {"project_id": 1, "status_id": 7}, {"project_id": 1, "status_id": 6}, {"project_id": 3, "status_id": 1}, {"project_id": 1, "status_id": 1}, {"project_id": 1, "status_id": 2}, {"project_id": 1, "status_id": 7}, {"project_id": 3, "status_id": 2}, {"project_id": 1, "status_id": 1}, {"project_id": 1, "status_id": 6}, {"project_id": 1, "status_id": 6}, {"project_id": 2, "status_id": 1}, {"project_id": 1, "status_id": 2}, {"project_id": 1, "status_id": 1}, {"project_id": 1, "status_id": 1}, {"project_id": 2, "status_id": 1}, {"project_id": 1, "status_id": 1}, {"project_id": 2, "status_id": 1}, {"project_id": 1, "status_id": 7}, {"project_id": 1, "status_id": 1}, {"project_id": 2, "status_id": 1}, {"project_id": 1, "status_id": 7}, {"project_id": 1, "status_id": 1}, {"project_id": 1, "status_id": 2}, {"project_id": 2, "status_id": 1}, {"project_id": 2, "status_id": 6}, {"project_id": 2, "status_id": 1}, {"project_id": 1, "status_id": 7}, {"project_id": 1, "status_id": 1}, {"project_id": 2, "status_id": 1}, {"project_id": 1, "status_id": 2}, {"project_id": 1, "status_id": 2}, {"project_id": 2, "status_id": 1}, {"project_id": 1, "status_id": 7}]
);
console.log(validResult, result);
Raw
javascript.js
"use strict";
/**
* Высчитывает количество задач по каждому статусу для каждого проекта.
*
* @param projects {Array}
* @param statuses {Array}
* @param tasks {Array}
*
* @returns {Array}
*/
function getReport(projects, statuses, tasks) {
return projects.map(function (project) {
return statuses.reduce(function (carry, status) {
carry[status.title] = tasks
.filter(function (task) {
return task.project_id === project.id
&& task.status_id === status.id;
})
.length;
return carry;
}, {title: project.title});
});
}
var validResult = [
{
"title": "Canape CRM",
"Площадка разворачивается": 0,
"Площадка обновляется": 13,
"Выполняется резервное копирование": 2,
"ОК": 0,
"Ошибка": 1
},
{
"title": "Мастер",
"Площадка разворачивается": 1,
"Площадка обновляется": 6,
"Выполняется резервное копирование": 2,
"ОК": 0,
"Ошибка": 0
},
{
"title": "Canape CMS",
"Площадка разворачивается": 13,
"Площадка обновляется": 23,
"Выполняется резервное копирование": 12,
"ОК": 2,
"Ошибка": 5
}
];
var result = getReport(
[
{"id": 2, "title": "Canape CRM"},
{"id": 3, "title": "Мастер"},
{"id": 1, "title": "Canape CMS"}
],
[
{"id": 7, "title": "Площадка разворачивается"},
{"id": 1, "title": "Площадка обновляется"},
{"id": 2, "title": "Выполняется резервное копирование"},
{"id": 3, "title": "ОК"},
{"id": 6, "title": "Ошибка"}
],
[{"project_id": 2, "status_id": 2}, {"project_id": 2, "status_id": 1}, {"project_id": 2, "status_id": 1}, {"project_id": 1, "status_id": 1}, {"project_id": 2, "status_id": 1}, {"project_id": 1, "status_id": 2}, {"project_id": 1, "status_id": 1}, {"project_id": 1, "status_id": 7}, {"project_id": 1, "status_id": 2}, {"project_id": 1, "status_id": 1}, {"project_id": 1, "status_id": 1}, {"project_id": 1, "status_id": 1}, {"project_id": 3, "status_id": 1}, {"project_id": 1, "status_id": 7}, {"project_id": 1, "status_id": 1}, {"project_id": 1, "status_id": 6}, {"project_id": 1, "status_id": 3}, {"project_id": 2, "status_id": 2}, {"project_id": 1, "status_id": 7}, {"project_id": 1, "status_id": 2}, {"project_id": 1, "status_id": 2}, {"project_id": 1, "status_id": 7}, {"project_id": 1, "status_id": 7}, {"project_id": 1, "status_id": 7}, {"project_id": 1, "status_id": 7}, {"project_id": 3, "status_id": 7}, {"project_id": 1, "status_id": 2}, {"project_id": 3, "status_id": 1}, {"project_id": 2, "status_id": 1}, {"project_id": 1, "status_id": 2}, {"project_id": 1, "status_id": 1}, {"project_id": 3, "status_id": 1}, {"project_id": 3, "status_id": 2}, {"project_id": 1, "status_id": 1}, {"project_id": 1, "status_id": 1}, {"project_id": 1, "status_id": 1}, {"project_id": 2, "status_id": 1}, {"project_id": 1, "status_id": 1}, {"project_id": 1, "status_id": 1}, {"project_id": 3, "status_id": 1}, {"project_id": 1, "status_id": 2}, {"project_id": 1, "status_id": 1}, {"project_id": 3, "status_id": 1}, {"project_id": 1, "status_id": 6}, {"project_id": 1, "status_id": 3}, {"project_id": 1, "status_id": 1}, {"project_id": 1, "status_id": 1}, {"project_id": 1, "status_id": 7}, {"project_id": 1, "status_id": 6}, {"project_id": 3, "status_id": 1}, {"project_id": 1, "status_id": 1}, {"project_id": 1, "status_id": 2}, {"project_id": 1, "status_id": 7}, {"project_id": 3, "status_id": 2}, {"project_id": 1, "status_id": 1}, {"project_id": 1, "status_id": 6}, {"project_id": 1, "status_id": 6}, {"project_id": 2, "status_id": 1}, {"project_id": 1, "status_id": 2}, {"project_id": 1, "status_id": 1}, {"project_id": 1, "status_id": 1}, {"project_id": 2, "status_id": 1}, {"project_id": 1, "status_id": 1}, {"project_id": 2, "status_id": 1}, {"project_id": 1, "status_id": 7}, {"project_id": 1, "status_id": 1}, {"project_id": 2, "status_id": 1}, {"project_id": 1, "status_id": 7}, {"project_id": 1, "status_id": 1}, {"project_id": 1, "status_id": 2}, {"project_id": 2, "status_id": 1}, {"project_id": 2, "status_id": 6}, {"project_id": 2, "status_id": 1}, {"project_id": 1, "status_id": 7}, {"project_id": 1, "status_id": 1}, {"project_id": 2, "status_id": 1}, {"project_id": 1, "status_id": 2}, {"project_id": 1, "status_id": 2}, {"project_id": 2, "status_id": 1}, {"project_id": 1, "status_id": 7}]
);
console.log(validResult, result);
Raw
php-optimized.php
<?php
function getReport(
array $projects = [],
array $statuses = [],
array $tasks = []
): array {
function getTitlesMap($array) {
return array_reduce(
$array,
function ($carry, $item) {
return array_replace($carry, [$item['id'] => $item['title']]);
},
[]
);
}
$projectTitles = getTitlesMap($projects);
$statusTitles = getTitlesMap($statuses);
$initialValue = array_fill_keys(
array_values($statusTitles),
0
);
return array_reduce(
$tasks,
function ($carry, $task) use (
$projectTitles,
$statusTitles,
$initialValue
) {
extract($task);
if (!array_key_exists($project_id, $projectTitles)) {
return $carry;
}
if (!array_key_exists($project_id, $carry)) {
$carry[$project_id] = array_replace(
$initialValue,
['title' => $projectTitles[$project_id]]
);
}
$statusTitle = $statusTitles[$status_id];
if (array_key_exists($statusTitle, $carry[$project_id])) {
$carry[$project_id][$statusTitle] += 1;
}
return $carry;
},
[]
);
}
$report = getReport(
[
['id' => 2, 'title' => 'Canape CRM'],
['id' => 3, 'title' => 'Мастер'],
['id' => 1, 'title' => 'Canape CMS']
],
[
['id' => 7, 'title' => 'Площадка разворачивается'],
['id' => 1, 'title' => 'Площадка обновляется'],
['id' => 2, 'title' => 'Выполняется резервное копирование'],
['id' => 3, 'title' => 'ОК'],
['id' => 6, 'title' => 'Ошибка']
],
[['project_id' => 2, 'status_id' => 2], ['project_id' => 2, 'status_id' => 1], ['project_id' => 2, 'status_id' => 1], ['project_id' => 1, 'status_id' => 1], ['project_id' => 2, 'status_id' => 1], ['project_id' => 1, 'status_id' => 2], ['project_id' => 1, 'status_id' => 1], ['project_id' => 1, 'status_id' => 7], ['project_id' => 1, 'status_id' => 2], ['project_id' => 1, 'status_id' => 1], ['project_id' => 1, 'status_id' => 1], ['project_id' => 1, 'status_id' => 1], ['project_id' => 3, 'status_id' => 1], ['project_id' => 1, 'status_id' => 7], ['project_id' => 1, 'status_id' => 1], ['project_id' => 1, 'status_id' => 6], ['project_id' => 1, 'status_id' => 3], ['project_id' => 2, 'status_id' => 2], ['project_id' => 1, 'status_id' => 7], ['project_id' => 1, 'status_id' => 2], ['project_id' => 1, 'status_id' => 2], ['project_id' => 1, 'status_id' => 7], ['project_id' => 1, 'status_id' => 7], ['project_id' => 1, 'status_id' => 7], ['project_id' => 1, 'status_id' => 7], ['project_id' => 3, 'status_id' => 7], ['project_id' => 1, 'status_id' => 2], ['project_id' => 3, 'status_id' => 1], ['project_id' => 2, 'status_id' => 1], ['project_id' => 1, 'status_id' => 2], ['project_id' => 1, 'status_id' => 1], ['project_id' => 3, 'status_id' => 1], ['project_id' => 3, 'status_id' => 2], ['project_id' => 1, 'status_id' => 1], ['project_id' => 1, 'status_id' => 1], ['project_id' => 1, 'status_id' => 1], ['project_id' => 2, 'status_id' => 1], ['project_id' => 1, 'status_id' => 1], ['project_id' => 1, 'status_id' => 1], ['project_id' => 3, 'status_id' => 1], ['project_id' => 1, 'status_id' => 2], ['project_id' => 1, 'status_id' => 1], ['project_id' => 3, 'status_id' => 1], ['project_id' => 1, 'status_id' => 6], ['project_id' => 1, 'status_id' => 3], ['project_id' => 1, 'status_id' => 1], ['project_id' => 1, 'status_id' => 1], ['project_id' => 1, 'status_id' => 7], ['project_id' => 1, 'status_id' => 6], ['project_id' => 3, 'status_id' => 1], ['project_id' => 1, 'status_id' => 1], ['project_id' => 1, 'status_id' => 2], ['project_id' => 1, 'status_id' => 7], ['project_id' => 3, 'status_id' => 2], ['project_id' => 1, 'status_id' => 1], ['project_id' => 1, 'status_id' => 6], ['project_id' => 1, 'status_id' => 6], ['project_id' => 2, 'status_id' => 1], ['project_id' => 1, 'status_id' => 2], ['project_id' => 1, 'status_id' => 1], ['project_id' => 1, 'status_id' => 1], ['project_id' => 2, 'status_id' => 1], ['project_id' => 1, 'status_id' => 1], ['project_id' => 2, 'status_id' => 1], ['project_id' => 1, 'status_id' => 7], ['project_id' => 1, 'status_id' => 1], ['project_id' => 2, 'status_id' => 1], ['project_id' => 1, 'status_id' => 7], ['project_id' => 1, 'status_id' => 1], ['project_id' => 1, 'status_id' => 2], ['project_id' => 2, 'status_id' => 1], ['project_id' => 2, 'status_id' => 6], ['project_id' => 2, 'status_id' => 1], ['project_id' => 1, 'status_id' => 7], ['project_id' => 1, 'status_id' => 1], ['project_id' => 2, 'status_id' => 1], ['project_id' => 1, 'status_id' => 2], ['project_id' => 1, 'status_id' => 2], ['project_id' => 2, 'status_id' => 1], ['project_id' => 1, 'status_id' => 7]]
);
print_r($report);
Raw
php.php
<?php
function getReport(
array $projects = [],
array $statuses = [],
array $tasks = []
): array {
return array_map(function ($project) use ($statuses, $tasks) {
return array_reduce(
$statuses,
function ($carry, $status) use ($project, $tasks) {
$filteredTasks = array_filter(
$tasks,
function ($task) use ($project, $status) {
return $task['project_id'] === $project['id']
&& $task['status_id'] === $status['id'];
}
);
$carry[$status['title']] = count($filteredTasks);
return $carry;
},
['title' => $project['title']]
);
}, $projects);
}
$report = getReport(
[
['id' => 2, 'title' => 'Canape CRM'],
['id' => 3, 'title' => 'Мастер'],
['id' => 1, 'title' => 'Canape CMS']
],
[
['id' => 7, 'title' => 'Площадка разворачивается'],
['id' => 1, 'title' => 'Площадка обновляется'],
['id' => 2, 'title' => 'Выполняется резервное копирование'],
['id' => 3, 'title' => 'ОК'],
['id' => 6, 'title' => 'Ошибка']
],
[['project_id' => 2, 'status_id' => 2], ['project_id' => 2, 'status_id' => 1], ['project_id' => 2, 'status_id' => 1], ['project_id' => 1, 'status_id' => 1], ['project_id' => 2, 'status_id' => 1], ['project_id' => 1, 'status_id' => 2], ['project_id' => 1, 'status_id' => 1], ['project_id' => 1, 'status_id' => 7], ['project_id' => 1, 'status_id' => 2], ['project_id' => 1, 'status_id' => 1], ['project_id' => 1, 'status_id' => 1], ['project_id' => 1, 'status_id' => 1], ['project_id' => 3, 'status_id' => 1], ['project_id' => 1, 'status_id' => 7], ['project_id' => 1, 'status_id' => 1], ['project_id' => 1, 'status_id' => 6], ['project_id' => 1, 'status_id' => 3], ['project_id' => 2, 'status_id' => 2], ['project_id' => 1, 'status_id' => 7], ['project_id' => 1, 'status_id' => 2], ['project_id' => 1, 'status_id' => 2], ['project_id' => 1, 'status_id' => 7], ['project_id' => 1, 'status_id' => 7], ['project_id' => 1, 'status_id' => 7], ['project_id' => 1, 'status_id' => 7], ['project_id' => 3, 'status_id' => 7], ['project_id' => 1, 'status_id' => 2], ['project_id' => 3, 'status_id' => 1], ['project_id' => 2, 'status_id' => 1], ['project_id' => 1, 'status_id' => 2], ['project_id' => 1, 'status_id' => 1], ['project_id' => 3, 'status_id' => 1], ['project_id' => 3, 'status_id' => 2], ['project_id' => 1, 'status_id' => 1], ['project_id' => 1, 'status_id' => 1], ['project_id' => 1, 'status_id' => 1], ['project_id' => 2, 'status_id' => 1], ['project_id' => 1, 'status_id' => 1], ['project_id' => 1, 'status_id' => 1], ['project_id' => 3, 'status_id' => 1], ['project_id' => 1, 'status_id' => 2], ['project_id' => 1, 'status_id' => 1], ['project_id' => 3, 'status_id' => 1], ['project_id' => 1, 'status_id' => 6], ['project_id' => 1, 'status_id' => 3], ['project_id' => 1, 'status_id' => 1], ['project_id' => 1, 'status_id' => 1], ['project_id' => 1, 'status_id' => 7], ['project_id' => 1, 'status_id' => 6], ['project_id' => 3, 'status_id' => 1], ['project_id' => 1, 'status_id' => 1], ['project_id' => 1, 'status_id' => 2], ['project_id' => 1, 'status_id' => 7], ['project_id' => 3, 'status_id' => 2], ['project_id' => 1, 'status_id' => 1], ['project_id' => 1, 'status_id' => 6], ['project_id' => 1, 'status_id' => 6], ['project_id' => 2, 'status_id' => 1], ['project_id' => 1, 'status_id' => 2], ['project_id' => 1, 'status_id' => 1], ['project_id' => 1, 'status_id' => 1], ['project_id' => 2, 'status_id' => 1], ['project_id' => 1, 'status_id' => 1], ['project_id' => 2, 'status_id' => 1], ['project_id' => 1, 'status_id' => 7], ['project_id' => 1, 'status_id' => 1], ['project_id' => 2, 'status_id' => 1], ['project_id' => 1, 'status_id' => 7], ['project_id' => 1, 'status_id' => 1], ['project_id' => 1, 'status_id' => 2], ['project_id' => 2, 'status_id' => 1], ['project_id' => 2, 'status_id' => 6], ['project_id' => 2, 'status_id' => 1], ['project_id' => 1, 'status_id' => 7], ['project_id' => 1, 'status_id' => 1], ['project_id' => 2, 'status_id' => 1], ['project_id' => 1, 'status_id' => 2], ['project_id' => 1, 'status_id' => 2], ['project_id' => 2, 'status_id' => 1], ['project_id' => 1, 'status_id' => 7]]
);
print_r($report);
Raw
postgres.sql
CREATE TABLE projects (
id INT PRIMARY KEY,
title VARCHAR
);
INSERT INTO projects VALUES
(2, 'Canape CRM'),
(3, 'Master'),
(1, 'Canape CMS');
CREATE TABLE statuses (
id INT PRIMARY KEY,
title VARCHAR
);
INSERT INTO statuses VALUES
(7, 'Deploying'),
(1, 'Updating'),
(2, 'Backup'),
(3, 'OK'),
(6, 'Error');
CREATE TABLE tasks (
id serial PRIMARY KEY,
project_id INT REFERENCES projects (id),
status_id INT REFERENCES statuses (id)
);
INSERT INTO tasks (project_id, status_id) VALUES
(2, 2),
(2, 1),
(2, 1),
(1, 1),
(2, 1),
(1, 2),
(1, 1),
(1, 7),
(1, 2),
(1, 1),
(1, 1),
(1, 1),
(3, 1),
(1, 7),
(1, 1),
(1, 6),
(1, 3),
(2, 2),
(1, 7),
(1, 2),
(1, 2),
(1, 7),
(1, 7),
(1, 7),
(1, 7),
(3, 7),
(1, 2),
(3, 1),
(2, 1),
(1, 2),
(1, 1),
(3, 1),
(3, 2),
(1, 1),
(1, 1),
(1, 1),
(2, 1),
(1, 1),
(1, 1),
(3, 1),
(1, 2),
(1, 1),
(3, 1),
(1, 6),
(1, 3),
(1, 1),
(1, 1),
(1, 7),
(1, 6),
(3, 1),
(1, 1),
(1, 2),
(1, 7),
(3, 2),
(1, 1),
(1, 6),
(1, 6),
(2, 1),
(1, 2),
(1, 1),
(1, 1),
(2, 1),
(1, 1),
(2, 1),
(1, 7),
(1, 1),
(2, 1),
(1, 7),
(1, 1),
(1, 2),
(2, 1),
(2, 6),
(2, 1),
(1, 7),
(1, 1),
(2, 1),
(1, 2),
(1, 2),
(2, 1),
(1, 7);
SELECT
projects.title,
statuses.title,
COUNT(
CASE
WHEN tasks.project_id = projects.id
AND tasks.status_id = statuses.id
THEN 1
END
)
FROM projects
JOIN tasks ON projects.id = tasks.project_id
JOIN statuses ON statuses.id = tasks.status_id
GROUP BY projects.title, statuses.title
ORDER BY projects.title;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment