Своего рода сводная таблица с использованием Symfony2 и Doctrine

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

Сначала пример отчета:

В этом отчете округ (связанный с адресом домохозяйства) — это столбец, а уровень дохода субъекта домохозяйства — запись в строке. Ячейки — это количество домохозяйств, с которыми связались в данный период времени с характеристиками строки и столбца.

Контроллер (есть три штуки):

public function incomeProfileAction(Request $request)
{
$form = $this->createForm(new ReportCriteriaType());
$criteria = $request->request->get('report_criteria');
$form->handleRequest($request);
if ($form->isValid()) {
$response = new Response();
$reportData = $this->income($criteria);
$content = $this->profiler($reportData);
$response->setContent($content);

return $response;
}

return $this->render('ManaClientBundle:Statistics:report_criteria.html.twig', array(
'form' => $form->createView(),
'extra' => 'profile',
'formPath' => "income_profile",
'title' => 'Report criteria',
'criteriaHeader' => 'Select income profile reporting criteria',
));
}

private function income($criteria)
{
$em = $this->getDoctrine()->getManager();
$xp = $this->container->get('mana.crosstab');
$dateCriteria = $xp->setDateCriteria($criteria);
$columnType = $criteria['columnType'];
$rowLabels = $em->getRepository('ManaClientBundle:Income')->rowLabels($dateCriteria);
$colLabels = $em->getRepository('ManaClientBundle:' . $columnType)->colLabels($dateCriteria);
$data = $em->getRepository('ManaClientBundle:Income')->crossTabData($dateCriteria, $columnType);

$reportData = [
'reportTitle' => 'Household Income',
'reportSubTitle' => 'For the period ',
'criteria' => $criteria,
'rowHeader' => 'Income bracket',
'rowLabels' => $rowLabels,
'colLabels' => $colLabels,
'data' => $data,
];

return $reportData;
}

private function profiler($reportData)
{
$xp = $this->container->get('mana.crosstab');
$profile = $xp->crosstabQuery($reportData['data'], $reportData['rowLabels'], $reportData['colLabels']);
$reports = $this->get('reports');
$specs = $reports->getSpecs($reportData['criteria']);return $this->renderView("ManaClientBundle:Statistics:profile.html.twig", ['profile' => $profile,
'rowHeader' => $reportData['rowHeader'],
'rowLabels' => $reportData['rowLabels'],
'colLabels' => $reportData['colLabels'],
'reportTitle' => $reportData['reportTitle'],
'reportSubTitle' => $reportData['reportSubTitle'],
'date' => new \DateTime(),
'specs' => $specs,
]);
}

Шаблон:

{% set start = specs.startDate|date('F, Y') %}
{% set end = specs.endDate|date('F, Y') %}
<h3 style="text-align: center;">{{ reportTitle }}</h3>
<h4 style="text-align: center;">{{ reportSubTitle }} {{ start }}{% if (start != end) %} through {{ end }}{% endif %}</h4>
<table class="table table-striped table-condensed">
<thead>
<tr>
<th>{{ rowHeader }}
{%- for colLabel in colLabels -%}
<th style="text-align: right;">{{ colLabel }} </th>
{%- endfor -%}
<th style="text-align: right;">Total</th>
</tr>
<tfoot>
<tr>
<td>Total
{% for total in profile.total %}
<td style="text-align: right">{{ total|number_format }}
{% endfor %}
<tbody>
{% for rowLabel in rowLabels -%}
<tr>
<td>{{ rowLabel }}
{%- for colLabel in colLabels -%}
<td style="text-align: right">
{%- if profile[rowLabel][colLabel] is defined -%}
{{ profile[rowLabel][colLabel]|number_format }}{%- else -%}
0{%- endif -%}

{% endfor %}
<td style="text-align: right">{{ profile[rowLabel]['total']|number_format }}
</tr>
{%- endfor -%}
</table>

Служба кросс-таблицы:

class Crosstab
{
private $em;

public function __construct(EntityManager $em) {
$this->em = $em;
}

/**
*
* @param array $data = data array
* @param array $rowLabels
* @param array $colLabels
* @return array
*/
public function crosstabQuery($data, $rowLabels, $colLabels)
{
$profile = $this->profileArray( $rowLabels, $colLabels);
foreach ($data as $array) {
if (!array_key_exists('total', $profile[$array['rowLabel']])) {
$profile[$array['rowLabel']]['total'] = 0;
}
$profile[$array['rowLabel']][$array['colLabel']] = $array['N'];
$profile[$array['rowLabel']]['total'] += $array['N'];
}
foreach ($profile as $key => $array) {
if (!array_key_exists('total', $array)) {
$profile[$key]['total'] = 0;
}
}
$profile['total'] = [];
foreach ($profile as $row => $array) {
foreach ($array as $key => $value) {
if (!array_key_exists($key, $profile['total'])) {
$profile['total'][$key] = 0;
}
$profile['total'][$key] += $value;
}
}

return $profile;
}

private function profileArray($rows, $cols)
{
$colKeys = [];
foreach ($cols as $col) {
$colKeys[$col] = 0;
}
$profile = [];
foreach ($rows as $row) {
$profile[$row] = $colKeys;
}

return $profile;
}

/**
*
* @param string $query
* @param array $criteria ['startMonth', 'startYear', 'endMonth', 'endYear']
* @return string
*/
public function setDateCriteria($criteria)
{
$startMonth = $criteria['startMonth'];
$startYear = $criteria['startYear'];
$startText = $startYear . '-' . $startMonth . '-' . '01';
$endMonth = $criteria['endMonth'];
$endYear = $criteria['endYear'];
$endDate = new \DateTime($endMonth . '/01/' . $endYear);
$endText = $endDate->format('Y-m-t');

return "'$startText' AND '$endText' ";

}
}

Репозиторий округа:

class CountyRepository extends EntityRepository
{
public function colLabels($dateCriteria)
{
$str = "select distinct cty.county from county cty
join contact c on c.county_id = cty.id
where c.contact_date BETWEEN __DATE_CRITERIA__
order by county";
$sql = str_replace('__DATE_CRITERIA__', $dateCriteria, $str);
$conn = $this->getEntityManager()->getConnection();
$stmt = $conn->executeQuery($sql);
$colArray = $stmt->fetchAll();
$colLabels = [];
foreach ($colArray as $array) {
$colLabels[] = $array['county'];
}

return $colLabels;
}

Доходное хранилище:

class IncomeRepository extends EntityRepository
{

public function rowLabels($dateCriteria)
{
$str = "select distinct i.income
from income i
join household h on h.income_id = i.id
join contact c on c.household_id = h.id
WHERE c.contact_date BETWEEN __DATE_CRITERIA__
AND i.enabled = TRUE order by i.id ";
$sql = str_replace('__DATE_CRITERIA__', $dateCriteria, $str);
$conn = $this->getEntityManager()->getConnection();
$stmt = $conn->executeQuery($sql);
$rowArray = $stmt->fetchAll();
$rowLabels = [];
foreach ($rowArray as $array) {
$rowLabels[] = $array['income'];
}

return $rowLabels;
}

public function crossTabData($dateCriteria, $profileType)
{
$str = "SELECT r.__TYPE__ colLabel, i.income rowLabel, COUNT(DISTINCT h.id) N " .
"FROM household h " .
"JOIN contact c ON c.household_id = h.id " .
"LEFT JOIN __TYPE__ r ON r.id = c.__TYPE___id " .
"LEFT JOIN income i ON h.income_id = i.id " .
"WHERE c.contact_date BETWEEN __DATE_CRITERIA__ " .
"AND i.enabled = TRUE " .
"GROUP BY colLabel, rowLabel";
$sql1 = str_replace('__DATE_CRITERIA__', $dateCriteria, $str);
$sql = str_replace('__TYPE__', $profileType, $sql1);
$conn = $this->getEntityManager()->getConnection();
$stmt = $conn->executeQuery($sql);

return $stmt->fetchAll();
}