Ich arbeite gerade an einer Archiv erweiterung für mein Aktivitätsscript.
Nun möchte ich in einer Tabelle für jedes jahr eine zeile, in dieser zeile für jeden monat eine spalte und in den Monatsspalten das ergebnis für den betreffenden monat haben.
ich linke mal die sqlfrage und den aufbau der view datei.
<?php
/**
* @copyright Ilch 2.0
* @package ilch
*/
namespace Modules\Activities\Mappers;
use Modules\Activities\Models\Archiv as ArchivModel;
use Modules\Activities\Models\Clans as ClansModel;
class Archiv extends \Ilch\Mapper
{
public function getArchiv($clan_id){
$sql="
Select
[prefix]_activities_punkte_archiv.jahr,
[prefix]_activities_punkte_archiv.monat,
erg.member_name,
(Case
When isnull((erg.anzahl_cw + erg.anzahl_vs + erg.anzahl_sh) /
erg.activitie_gesamt)
Then 0
Else ((erg.anzahl_cw + erg.anzahl_vs + erg.anzahl_sh) /
erg.activitie_gesamt)
End) As activitie_gesamt,
Sum([prefix]_activities_punkte_archiv.auszahlung) As auszahlung
From
(Select
[prefix]_wargaming_clans_members.member_name,
(Sum(Case
When [prefix]_activities_entries.modus = 'cw'
Then 1
Else 0
End)) As anzahl_cw,
(Sum(Case
When [prefix]_activities_entries.activitie = 'leitend' And
[prefix]_activities_entries.modus = 'cw'
Then 1
Else 0
End)) As leitend_cw,
(Sum(Case
When [prefix]_activities_entries.activitie = 'gespielt' And
[prefix]_activities_entries.modus = 'cw'
Then 1
Else 0
End)) As gespielt_cw,
(Sum(Case
When [prefix]_activities_entries.activitie = 'bereit' And
[prefix]_activities_entries.modus = 'cw'
Then 1
Else 0
End)) As bereit_cw,
(Select
count(*)
From
[prefix]_activities_identifier
Where
[prefix]_activities_identifier.modus = 'cw' And
[prefix]_activities_identifier.clan_id = '".$clan_id."') As activitie_cw,
(Sum(Case
When [prefix]_activities_entries.modus = 'vs'
Then 1
Else 0
End)) As anzahl_vs,
(Sum(Case
When [prefix]_activities_entries.activitie = 'leitend' And
[prefix]_activities_entries.modus = 'vs'
Then 1
Else 0
End)) As leitend_vs,
(Sum(Case
When [prefix]_activities_entries.activitie = 'gespielt' And
[prefix]_activities_entries.modus = 'vs'
Then 1
Else 0
End)) As gespielt_vs,
(Sum(Case
When [prefix]_activities_entries.activitie = 'bereit' And
[prefix]_activities_entries.modus = 'vs'
Then 1
Else 0
End)) As bereit_vs,
(Select
count(*) As anzahl
From
[prefix]_activities_identifier
Where
[prefix]_activities_identifier.modus = 'vs' And
[prefix]_activities_identifier.clan_id = '".$clan_id."') As activitie_vs,
(Sum(Case
When [prefix]_activities_entries.modus = 'sh'
Then 1
Else 0
End)) As anzahl_sh,
(Sum(Case
When [prefix]_activities_entries.activitie = 'leitend' And
[prefix]_activities_entries.modus = 'sh'
Then 1
Else 0
End)) As leitend_sh,
(Sum(Case
When [prefix]_activities_entries.activitie = 'gespielt' And
[prefix]_activities_entries.modus = 'sh'
Then 1
Else 0
End)) As gespielt_sh,
(Sum(Case
When [prefix]_activities_entries.activitie = 'bereit' And
[prefix]_activities_entries.modus = 'sh'
Then 1
Else 0
End)) As bereit_sh,
(Select
count(*)
From
[prefix]_activities_identifier
Where
[prefix]_activities_identifier.modus = 'sh' And
[prefix]_activities_identifier.clan_id = '".$clan_id."') As activitie_sh,
(Select
count(*)
From
[prefix]_activities_identifier
Where
[prefix]_activities_identifier.clan_id = '".$clan_id."') As activitie_gesamt,
[prefix]_wargaming_clans_members.member_account_id
From
[prefix]_wargaming_clans_members Left Join
[prefix]_activities_entries On [prefix]_activities_entries.account_id =
[prefix]_wargaming_clans_members.member_account_id And
[prefix]_activities_entries.clan_id = [prefix]_wargaming_clans_members.clan_id
Left Join
[prefix]_activities_identifier On [prefix]_activities_entries.identifier =
[prefix]_activities_identifier.identifier
Where
[prefix]_wargaming_clans_members.clan_id = '".$clan_id."' And
[prefix]_wargaming_clans_members.Aktiv = 'J'
Group By
[prefix]_wargaming_clans_members.member_name,
[prefix]_wargaming_clans_members.member_account_id) As erg Inner Join
[prefix]_activities_punkte_archiv On [prefix]_activities_punkte_archiv.account_id =
erg.member_account_id
Group By
[prefix]_activities_punkte_archiv.jahr, [prefix]_activities_punkte_archiv.monat,
erg.member_name, (Case
When isnull((erg.anzahl_cw + erg.anzahl_vs + erg.anzahl_sh) /
erg.activitie_gesamt)
Then 0
Else ((erg.anzahl_cw + erg.anzahl_vs + erg.anzahl_sh) /
erg.activitie_gesamt)
End)
Order By
[prefix]_activities_punkte_archiv.jahr,
[prefix]_activities_punkte_archiv.monat,
activitie_gesamt Desc";
$entryArray = $this->db()->query($sql);
$activities = [];
if (empty($entryArray)) {
return $activities;
}
foreach ($entryArray as $entries) {
$entryModel = new ArchivModel();
$entryModel->setJahr($entries['jahr']);
$entryModel->setMonat($entries['monat']);
$entryModel->setMember_name($entries['member_name']);
$entryModel->setActivitie_gesamt($entries['activitie_gesamt']);
$entryModel->setAuszahlung($entries['auszahlung']);
$activities[] = $entryModel;
}
return $activities;
}
public function getJahr($clan_id) {
$sql = "
Select
ilch_activities_punkte_archiv.jahr
From
ilch_activities_punkte_archiv
Where
ilch_activities_punkte_archiv.clan_id = ".$clan_id."
Group By
ilch_activities_punkte_archiv.jahr
Order By
ilch_activities_punkte_archiv.jahr
";
$entryArray = $this->db()->query($sql);
$jahr = [];
foreach ($entryArray as $entries) {
$entryModel = new ArchivModel();
$entryModel->setJahr($entries['jahr']);
$jahr[] = $entryModel;
}
return $jahr;
}
public function getMonat($clan_id) {
$sql = "
Select
ilch_activities_punkte_archiv.jahr,
ilch_activities_punkte_archiv.monat
From
ilch_activities_punkte_archiv
Where
ilch_activities_punkte_archiv.clan_id = ".$clan_id."
Group By
ilch_activities_punkte_archiv.jahr, ilch_activities_punkte_archiv.monat
Order By
ilch_activities_punkte_archiv.jahr, ilch_activities_punkte_archiv.monat
";
$entryArray = $this->db()->query($sql);
$monat = [];
foreach ($entryArray as $entries) {
$entryModel = new ArchivModel();
$entryModel->setJahr($entries['jahr']);
$entryModel->setMonat($entries['monat']);
$monat[] = $entryModel;
}
return $monat;
}
public function getCountCW($clan_id){
$sql = "SELECT COUNT(*) FROM `[prefix]_activities_identifier` where `modus` = 'cw' AND `clan_id` = '".$clan_id."'";
$count = $this->db()->queryCell($sql);
return $count;
}
public function getCountVS($clan_id){
$sql = "SELECT COUNT(*) FROM `[prefix]_activities_identifier` where `modus` = 'vs' AND `clan_id` = '".$clan_id."'";
$count = $this->db()->queryCell($sql);
return $count;
}
public function getCountSH($clan_id){
$sql = "SELECT COUNT(*) FROM `[prefix]_activities_identifier` where `modus` = 'sh' AND `clan_id` = '".$clan_id."'";
$count = $this->db()->queryCell($sql);
return $count;
}
}
<style>
/* Just common table stuff. */
table { border-collapse: collapse; width: 100%; }
th, td { padding: 8px 16px; background: rgba(9, 11, 12, 0.8);}
th { background: rgba(9, 11, 12, 0.8);
right: 0;
border-top: 1px solid #292929;
border-bottom: 1px solid #292929;}
</style>
<?php foreach ($this->get('wguser') as $wguser) : ?>
<?php $clantag = $wguser->getClantag(); ?>
<div align="center" style="color: #89c790;"><h1><?=$this->getTrans('menuArchiv')?> | <?php echo $clantag?></h1></div>
<br>
<br>
<?php endforeach; ?>
<div class="teams">
<?php if ($this->get('archiv')): ?>
<div class="row">
<?php
//var_dump($this->get('activities')); die();
if (!function_exists('colorByActivitie')) {
function colorByActivitie($activitiepercent) {
if ($activitiepercent <= 9.99) { $farbe = "#CD3333";} // rot
if ($activitiepercent >= 10) { $farbe = "#D7B600";} // gelb
if ($activitiepercent > 25) { $farbe = "#4C762E";} // gruen
if ($activitiepercent > 50) { $farbe = "#4A92B7";} // blau
if ($activitiepercent > 75) { $farbe = "#5A3175";} // lila
return $farbe;
}
}
?>
<div class='table-responsive'>
<!--Table-->
<table class="table">
<?php foreach ($this->get('jahr') as $jahr):?>
<tr><td>
<!--Table-->
<table class="table">
<!--Table head-->
<thead>
<tr>
<th colspan="12" class="text-center" style="color: #89c790;"><?php echo $jahr->getJahr();?></th>
</tr>
</thead>
<!--Table head-->
<!--Table body-->
<tbody>
<tr>
<?php foreach ($this->get('monat') as $monat):?>
<td>
<table class="table table-bordered table-hover">
<!--Table head-->
<thead>
<tr>
<th class="text-center" style="color: #89c790;"> <?php echo $monat->getMonat();?> </th>
</tr>
</thead>
<!--Table head-->
<!--Table body-->
<tbody>
<tr>
<td>
<table class="table table-bordered table-hover">
<!--Table head-->
<thead>
<tr>
<th > </th>
<th class="text-center" style="color: #89c790;"><?=$this->getTrans('member_name') ?></th>
<th > </th>
<th class="text-center" style="color: #89c790;"><?=$this->getTrans('aktivitie_gesamt') ?></th>
<th> </th>
<th class="text-center" style="color: #89c790;"><?=$this->getTrans('auszahl_gesamt') ?></th>
<th> </th>
</tr>
</thead>
<!--Table head-->
<!--Table body-->
<tbody>
<?php foreach ($this->get('archiv') as $archiv):?>
<tr>
<td> </td>
<td scope="row" align="center" style="color:white;"><?=$this->escape($archiv->getMember_Name())?></td>
<td> </td>
<?php
$colorActivitie_gesamt = colorByActivitie(round($this->escape($archiv->getActivitie_gesamt()) * 100,2));
?>
<td align="center" style="background-color:<?=$colorActivitie_gesamt?>;color:white;"><?php if($archiv->getActivitie_gesamt() != NULL) { echo round($this->escape($archiv->getActivitie_gesamt()) * 100,2);} else { echo "0";}?></td>
<td> </td>
<td align="center" style="color: #89c790;"><?=$this->escape($archiv->getAuszahlung())?></td>
<td> </td>
</tr>
<?php endforeach;?>
</tbody>
</table>
</td>
</tr>
</tbody>
</table>
</td>
<?php endforeach;?>
</tr>
</tbody>
</table>
</td>
</tr>
<?php endforeach;?>
</table>
</div>
<!-- Ace Responsive Menu -->
<nav>
<!-- Menu Toggle btn-->
<div class="menu-toggle">
<button type="button" id="menu-btn">
<span class="icon-bar"></span>
<span class="icon-bar"></span>
<span class="icon-bar"></span>
</button>
</div>
<!-- Responsive Menu Structure-->
<!--Note: declare the Menu style in the data-menu-style="horizontal" (options: horizontal, vertical, accordion) -->
<ul id="respMenu" class="ace-responsive-menu" data-menu-style="horizontal">
<li>
<a href="<?=$this->getUrl(['module' => 'activities', 'controller' => 'index', 'action' => 'index']) ?>">
<i class="fa fa-home" aria-hidden="true"></i>
<span class="title"><?=$this->getTrans('menuTeams')?></span>
</a>
</li>
<li>
<a href="<?=$this->getUrl(['module' => 'activities', 'controller' => 'index', 'action' => 'info']) ?>">
<i class="fa fa-heart" aria-hidden="true"></i>
<span class="title"><?=$this->getTrans('menuInfo')?></span>
</a>
</li>
<li>
<a href="<?=$this->getUrl(['module' => 'activities', 'controller' => 'index', 'action' => 'archiv']) ?>">
<i class="fa fa-heart" aria-hidden="true"></i>
<span class="title"><?=$this->getTrans('menuArchiv')?></span>
</a>
</li>
</ul>
</nav>
</div>
<?php else: ?>
<?=$this->getTrans('noTeams') ?>
<?php endif; ?>
</div>
<script>
var $th = $('.tableFixHead').find('thead th')
$('.tableFixHead').on('scroll', function() {
$th.css('transform', 'translateY('+ this.scrollTop +'px)');
});
$(document).ready(function () {
$("#respMenu").aceResponsiveMenu({
resizeWidth: '768', // Set the same in Media query
animationSpeed: 'fast', //slow, medium, fast
accoridonExpAll: false //Expands all the accordion menu on click
});
});
/*
Ace Responsive Menu Plugin
Version: 1.0
Author: Samson.Onna
Email : samson3d@gmail.com
----------------------------------------*/
(function ($) {
$.fn.aceResponsiveMenu = function (options) {
//plugin's default options
var defaults = {
resizeWidth: '768',
animationSpeed: 'fast',
accoridonExpAll: false
};
//Variables
var options = $.extend(defaults, options),
opt = options,
$resizeWidth = opt.resizeWidth,
$animationSpeed = opt.animationSpeed,
$expandAll = opt.accoridonExpAll,
$aceMenu = $(this),
$menuStyle = $(this).attr('data-menu-style');
// Initilizing
$aceMenu.find('ul').addClass("sub-menu");
$aceMenu.find('ul').siblings('a').append('<span class="arrow "></span>');
if ($menuStyle == 'accordion') { $(this).addClass('collapse'); }
// Window resize on menu breakpoint
if ($(window).innerWidth() <= $resizeWidth) {
menuCollapse();
}
$(window).resize(function () {
menuCollapse();
});
// Menu Toggle
function menuCollapse() {
var w = $(window).innerWidth();
if (w <= $resizeWidth) {
$aceMenu.find('li.menu-active').removeClass('menu-active');
$aceMenu.find('ul.slide').removeClass('slide').removeAttr('style');
$aceMenu.addClass('collapse hide-menu');
$aceMenu.attr('data-menu-style', '');
$('.menu-toggle').show();
} else {
$aceMenu.attr('data-menu-style', $menuStyle);
$aceMenu.removeClass('collapse hide-menu').removeAttr('style');
$('.menu-toggle').hide();
if ($aceMenu.attr('data-menu-style') == 'accordion') {
$aceMenu.addClass('collapse');
return;
}
$aceMenu.find('li.menu-active').removeClass('menu-active');
$aceMenu.find('ul.slide').removeClass('slide').removeAttr('style');
}
}
//ToggleBtn Click
$('#menu-btn').click(function () {
$aceMenu.slideToggle().toggleClass('hide-menu');
});
// Main function
return this.each(function () {
// Function for Horizontal menu on mouseenter
$aceMenu.on('mouseover', '> li a', function () {
if ($aceMenu.hasClass('collapse') === true) {
return false;
}
$(this).off('click', '> li a');
$(this).parent('li').siblings().children('.sub-menu').stop(true, true).slideUp($animationSpeed).removeClass('slide').removeAttr('style').stop();
$(this).parent().addClass('menu-active').children('.sub-menu').slideDown($animationSpeed).addClass('slide');
return;
});
$aceMenu.on('mouseleave', 'li', function () {
if ($aceMenu.hasClass('collapse') === true) {
return false;
}
$(this).off('click', '> li a');
$(this).removeClass('menu-active');
$(this).children('ul.sub-menu').stop(true, true).slideUp($animationSpeed).removeClass('slide').removeAttr('style');
return;
});
//End of Horizontal menu function
// Function for Vertical/Responsive Menu on mouse click
$aceMenu.on('click', '> li a', function () {
if ($aceMenu.hasClass('collapse') === false) {
//return false;
}
$(this).off('mouseover', '> li a');
if ($(this).parent().hasClass('menu-active')) {
$(this).parent().children('.sub-menu').slideUp().removeClass('slide');
$(this).parent().removeClass('menu-active');
} else {
if ($expandAll == true) {
$(this).parent().addClass('menu-active').children('.sub-menu').slideDown($animationSpeed).addClass('slide');
return;
}
$(this).parent().siblings().removeClass('menu-active');
$(this).parent('li').siblings().children('.sub-menu').slideUp().removeClass('slide');
$(this).parent().addClass('menu-active').children('.sub-menu').slideDown($animationSpeed).addClass('slide');
}
});
//End of responsive menu function
});
//End of Main function
}
})(jQuery);
</script>
<?php
/**
* @copyright Ilch 2.0
* @package ilch
*/
namespace Modules\Activities\Controllers;
use Modules\Activities\Mappers\Activities as ActivitiesMapper;
use Modules\Activities\Mappers\Archiv as ArchivMapper;
use Modules\wargamingauth\Mappers\User as WGUserMapper;
use Modules\Activitiesconfig\Mappers\Factor as FactorMapper;
use Ilch\Validation;
class Index extends \Ilch\Controller\Frontend
{
public function indexAction()
{
$ActivtieMapper = new ActivitiesMapper();
$wargamingUserMapper = new WGUserMapper();
$this->getLayout()->getHmenu()
->add($this->getTranslator()->trans('menuActivities'), ['action' => 'index']);
if (isset ($_SESSION['user_id'])) {
$user_id = $_SESSION['user_id'];
} else {
$user_id = '';
}
$where = $wargamingUserMapper->getWGUserbyID($user_id);
foreach ($where as $userparm) {
//var_dump($userparm->getClantag());die();
$clan_id = $userparm->getClan_id();
}
$this->getView()->set('wguser', $wargamingUserMapper->getWGUserbyID($user_id));
$this->getView()->set('activities', $ActivtieMapper->getActivitieIndex($clan_id));
$this->getView()->set('count_cw', $ActivtieMapper->getCountCW($clan_id));
$this->getView()->set('count_vs', $ActivtieMapper->getCountVS($clan_id));
$this->getView()->set('count_sh', $ActivtieMapper->getCountSH($clan_id));
}
public function infoAction()
{
$FactorMapper = new FactorMapper();
$wargamingUserMapper = new WGUserMapper();
if (isset ($_SESSION['user_id'])) {
$user_id = $_SESSION['user_id'];
} else {
$user_id = '';
}
$where = $wargamingUserMapper->getWGUserbyID($user_id);
foreach ($where as $userparm) {
//var_dump($userparm->getClantag());die();
$clan_id = $userparm->getClan_id();
}
//echo "<hr>";var_dump($wargamingUserMapper->getWGUserbyID($user_id)); die();
$this->getView()->set('wguser', $wargamingUserMapper->getWGUserbyID($user_id));
$this->getView()->set('factors_activitie', $FactorMapper->getFactors_Activitie($clan_id));
$this->getView()->set('factors_rank', $FactorMapper->getFactors_Rank($clan_id));
$this->getView()->set('prozent', $FactorMapper->getProzentgrundlage($clan_id));
}
public function archivAction()
{
$ArchivMapper = new ArchivMapper();
$wargamingUserMapper = new WGUserMapper();
$this->getLayout()->getHmenu()
->add($this->getTranslator()->trans('menuActivitiesArchiv'), ['action' => 'archiv']);
if (isset ($_SESSION['user_id'])) {
$user_id = $_SESSION['user_id'];
} else {
$user_id = '';
}
$where = $wargamingUserMapper->getWGUserbyID($user_id);
foreach ($where as $userparm) {
//var_dump($userparm->getClantag());die();
$clan_id = $userparm->getClan_id();
}
$this->getView()->set('wguser', $wargamingUserMapper->getWGUserbyID($user_id));
$this->getView()->set('jahr', $ArchivMapper->getJahr($clan_id));
$this->getView()->set('monat', $ArchivMapper->getMonat($clan_id));
$this->getView()->set('archiv', $ArchivMapper->getArchiv($clan_id));
}
}
vielleicht kann mir ja jemand einen tip geben, oder helfen.
Hab viel schon versucht. aber bin ratlos
verwendete ilch Version: 2.1.x
betroffene Homepage: www.r1sing.de
Zitat
