{"id":192,"date":"2014-08-18T13:06:57","date_gmt":"2014-08-18T13:06:57","guid":{"rendered":"https:\/\/www.dafap.fr\/blog\/?p=192"},"modified":"2016-05-02T12:22:20","modified_gmt":"2016-05-02T12:22:20","slug":"creation-dun-etat-avec-niveau-de-regroupement","status":"publish","type":"post","link":"https:\/\/www.dafap.fr\/blog\/developpement-php\/creation-dun-etat-avec-niveau-de-regroupement","title":{"rendered":"Cr\u00e9ation d&rsquo;un \u00e9tat avec niveau de regroupement"},"content":{"rendered":"<p><em>Dans un rapport Microsoft Access, il est possible de cr\u00e9er des niveaux de regroupement sur des champs d'une table ou d'une requ\u00eate. Comment r\u00e9aliser ces m\u00eames \u00e9tats sous ZF2 \/\u00a0MySql avec TcPdf ?<\/em><\/p>\n<h1>Exemple concret<\/h1>\n<p>Soit 2 tables, `eleves` et `tarifs` d\u00e9finies de la mani\u00e8re suivante :<\/p>\n<p>Table `eleves` :<\/p>\n<pre>CREATE TABLE IF NOT EXISTS `t_eleves` (\r\n `eleveId` int(11) NOT NULL AUTO_INCREMENT,\r\n `tarifId` int(11) NOT NULL DEFAULT \"0\"',\r\n `nom` varchar(30) COLLATE utf8_unicode_ci NOT NULL,\r\n `prenom` varchar(30) COLLATE utf8_unicode_ci NOT NULL,\r\n `adresseL1` varchar(38) COLLATE utf8_unicode_ci NOT NULL,\r\n `adresseL2` varchar(38) COLLATE utf8_unicode_ci NOT NULL,\r\n `codePostal` varchar(5) COLLATE utf8_unicode_ci NOT NULL,\r\n `commune` varchar(32) COLLATE utf8_unicode_ci NOT NULL,\r\n `dateN` date NOT NULL,\r\n `dateCreation` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,\r\n `dateModification` datetime NOT NULL DEFAULT '1900-01-01 00:00:00',\r\n `dateInscription` datetime NOT NULL DEFAULT '1900-01-01 00:00:00',\r\n `inscrit` tinyint(1) unsigned NOT NULL DEFAULT '1',\r\n `selection` tinyint(1) unsigned NOT NULL DEFAULT '0',\r\n PRIMARY KEY (`eleveId`)\r\n) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;\r\n<\/pre>\n<p>Table `tarifs` :<\/p>\n<pre>CREATE TABLE IF NOT EXISTS `t_tarifs` (\r\n `tarifId` int(11) NOT NULL AUTO_INCREMENT,\r\n `montant` decimal(10,2) NOT NULL DEFAULT '0.00',\r\n `nom` varchar(48) COLLATE utf8_unicode_ci NOT NULL,\r\n `rythme` int(4) NOT NULL DEFAULT '1',\r\n `grille` int(4) NOT NULL DEFAULT '1',\r\n PRIMARY KEY (`tarifId`)\r\n) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;<\/pre>\n<p><strong><em>On souhaite cr\u00e9er un \u00e9tat qui affiche la liste des \u00e9l\u00e8ves par tarif, avec un sous-total \u00e0 la fin de chaque groupe de tarifs,\u00a0et le total g\u00e9n\u00e9ral \u00e0 la fin de l'\u00e9tat.<\/em><\/strong><\/p>\n<h2>\u00a0Cr\u00e9ation de la requ\u00eate<\/h2>\n<p>On a 3 parties dans la requ\u00eate :<\/p>\n<ul>\n<li>le d\u00e9tail de chaque groupe contenant les \u00e9l\u00e8ves<\/li>\n<li>le pied de chaque groupe contenant le nombre d'\u00e9l\u00e8ves du groupe et le montant total du groupe<\/li>\n<li>la marge du bas contenant le nombre total d'\u00e9l\u00e8ves et le\u00a0total g\u00e9n\u00e9ral<\/li>\n<\/ul>\n<p>On codera 'd' \u00a0pour 'd\u00e9tail', 'p' pour 'pied', 'g' pour 'groupe', 'm' pour 'marge'. On remarquera que le codage respecte l'ordre d'affichage :<\/p>\n<ul>\n<li>'g' avant 'm'<\/li>\n<li>'d' avant 'p'<\/li>\n<\/ul>\n<p>On obtient un r\u00e9sultat convenable par :<\/p>\n<pre>SELECT nom, prenom, montant\r\nFROM (\r\n SELECT 'g'\u00a0c1, t.tarifId c2, 'd'\u00a0c3, e.nom, e.prenom, t.montant \r\n FROM t_eleves e INNER JOIN t_tarifs t ON e.tarifId=t.tarifId\r\nUNION\r\n SELECT 'g'\u00a0c1, t.tarifId c2,\u00a0'p' c3, concat('Pour ', t.nom), count(eleveId), sum(t.montant) \r\n FROM t_eleves e INNER JOIN t_tarifs t ON e.tarifId=t.tarifId GROUP BY t.tarifId\r\nUNION\r\n SELECT 'm'\u00a0c1, ''\u00a0c2, '' c3, 'Total g\u00e9n\u00e9ral', count(eleveId), sum(t.montant) \r\n FROM t_eleves e INNER JOIN t_tarifs t ON e.tarifId=t.tarifId\r\n) u \r\nORDER BY c1,c2,c3,1,2<\/pre>\n<p>Pour la mise en page, on aura sans doute int\u00e9r\u00eat \u00e0 afficher les colonnes c1, c2 et c3.<\/p>\n<div id=\"attachment_199\" style=\"width: 160px\" class=\"wp-caption alignleft\"><a href=\"https:\/\/www.dafap.fr\/blog\/wp-content\/uploads\/2014\/08\/etat-niveau-regroup1.png\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-199\" class=\"wp-image-199 size-thumbnail colorbox-192\" src=\"https:\/\/www.dafap.fr\/blog\/wp-content\/uploads\/2014\/08\/etat-niveau-regroup1-150x150.png\" alt=\"etat-niveau-regroup1\" width=\"150\" height=\"150\" \/><\/a><p id=\"caption-attachment-199\" class=\"wp-caption-text\">avec PhpMyAdmin<\/p><\/div>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Dans un rapport Microsoft Access, il est possible de cr\u00e9er des niveaux de regroupement sur des champs d&rsquo;une table ou d&rsquo;une requ\u00eate. Comment r\u00e9aliser ces m\u00eames \u00e9tats sous ZF2 \/\u00a0MySql avec TcPdf ? Exemple concret Soit 2 tables, `eleves` et `tarifs` d\u00e9finies de la mani\u00e8re suivante : Table `eleves` : CREATE TABLE IF NOT EXISTS [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9,15],"tags":[19,31,23],"class_list":["post-192","post","type-post","status-publish","format-standard","hentry","category-developpement-php","category-zendframework2","tag-mysql","tag-sql","tag-zf2"],"_links":{"self":[{"href":"https:\/\/www.dafap.fr\/blog\/wp-json\/wp\/v2\/posts\/192","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dafap.fr\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dafap.fr\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dafap.fr\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dafap.fr\/blog\/wp-json\/wp\/v2\/comments?post=192"}],"version-history":[{"count":9,"href":"https:\/\/www.dafap.fr\/blog\/wp-json\/wp\/v2\/posts\/192\/revisions"}],"predecessor-version":[{"id":387,"href":"https:\/\/www.dafap.fr\/blog\/wp-json\/wp\/v2\/posts\/192\/revisions\/387"}],"wp:attachment":[{"href":"https:\/\/www.dafap.fr\/blog\/wp-json\/wp\/v2\/media?parent=192"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dafap.fr\/blog\/wp-json\/wp\/v2\/categories?post=192"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dafap.fr\/blog\/wp-json\/wp\/v2\/tags?post=192"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}