{"action":"created","issue":{"url":"https://api.github.com/repos/iGruppi/iGruppi/issues/84","labels_url":"https://api.github.com/repos/iGruppi/iGruppi/issues/84/labels{/name}","comments_url":"https://api.github.com/repos/iGruppi/iGruppi/issues/84/comments","events_url":"https://api.github.com/repos/iGruppi/iGruppi/issues/84/events","html_url":"https://github.com/iGruppi/iGruppi/issues/84","id":106989761,"number":84,"title":"Gestione cassa - visualizzazione riepilogo del gruppo","user":{"login":"monicapgiare","id":11527743,"avatar_url":"https://avatars.githubusercontent.com/u/11527743?v=3","gravatar_id":"","url":"https://api.github.com/users/monicapgiare","html_url":"https://github.com/monicapgiare","followers_url":"https://api.github.com/users/monicapgiare/followers","following_url":"https://api.github.com/users/monicapgiare/following{/other_user}","gists_url":"https://api.github.com/users/monicapgiare/gists{/gist_id}","starred_url":"https://api.github.com/users/monicapgiare/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/monicapgiare/subscriptions","organizations_url":"https://api.github.com/users/monicapgiare/orgs","repos_url":"https://api.github.com/users/monicapgiare/repos","events_url":"https://api.github.com/users/monicapgiare/events{/privacy}","received_events_url":"https://api.github.com/users/monicapgiare/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":1,"created_at":"2015-09-17T13:51:14Z","updated_at":"2015-10-10T12:35:57Z","closed_at":null,"body":"Sarebbe molto comoda nella gestione cassa la possibilità di vedere un riepilogo di tutti gli utenti del gruppo, con totali e saldi dei loro conti.\r\n\r\nPer esempio, una tabella così:\r\n\r\n![schermata 2015-09-17 alle 15 48 08](https://cloud.githubusercontent.com/assets/11527743/9934779/a83a3140-5d53-11e5-9e3e-30f364b81be0.png)\r\n\r\nQuesta vista dovrebbe essere accessibile solo per amministratore e contabile, se possibile.\r\n"},"comment":{"url":"https://api.github.com/repos/iGruppi/iGruppi/issues/comments/147085464","html_url":"https://github.com/iGruppi/iGruppi/issues/84#issuecomment-147085464","issue_url":"https://api.github.com/repos/iGruppi/iGruppi/issues/84","id":147085464,"user":{"login":"claudiogiare","id":7717889,"avatar_url":"https://avatars.githubusercontent.com/u/7717889?v=3","gravatar_id":"","url":"https://api.github.com/users/claudiogiare","html_url":"https://github.com/claudiogiare","followers_url":"https://api.github.com/users/claudiogiare/followers","following_url":"https://api.github.com/users/claudiogiare/following{/other_user}","gists_url":"https://api.github.com/users/claudiogiare/gists{/gist_id}","starred_url":"https://api.github.com/users/claudiogiare/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/claudiogiare/subscriptions","organizations_url":"https://api.github.com/users/claudiogiare/orgs","repos_url":"https://api.github.com/users/claudiogiare/repos","events_url":"https://api.github.com/users/claudiogiare/events{/privacy}","received_events_url":"https://api.github.com/users/claudiogiare/received_events","type":"User","site_admin":false},"created_at":"2015-10-10T12:35:57Z","updated_at":"2015-10-10T12:35:57Z","body":"Ecco la query che ci serve. Fa anche la proiezione del Saldo, calcolando per ogni utente (di un gruppo) quello che si prevede dovrà pagare (somma degli importi di tutti gli ordini non archiviati) \r\n\r\nSELECT groups.idgroup, groups.nome AS Gruppo, concat( cognome, ' ', users.nome ) AS Utente, coalesce(TotAttivi, 0) as TotaleVersamenti, coalesce(TotPassivi, 0) as TotaleOrdiniPagati, coalesce(Num_ordini, 0) as NumeroOrdiniArchiviati, coalesce(Saldo,0) as SaldoConfermatoUtente, coalesce(Num_ordini_attivi,0) as NumeroOrdiniInCorso, -1*coalesce(StimaProxSpese,0) as StimaSpeseProxOrdini, coalesce(Saldo,0)-coalesce(StimaProxSpese,0) as ProiezioneSaldo\r\nFROM users\r\nJOIN users_group ON users.iduser = users_group.iduser\r\nJOIN groups ON users_group.idgroup = groups.idgroup\r\nLEFT JOIN \r\n(select iduser, coalesce( sum( case when idordine is null then importo else 0 end ) , 0 ) AS TotAttivi, coalesce( sum( case when idordine is not null then importo else 0 end ) , 0 ) AS TotPassivi, \r\n coalesce( count( DISTINCT cassa.idordine ) , 0 ) AS Num_ordini, coalesce( sum( importo ) , 0 ) AS Saldo from `cassa` group by cassa.iduser) cassa1 \r\nON cassa1.iduser = users.iduser\r\nLEFT JOIN \r\n(select iduser, coalesce( count( DISTINCT ordini.idordine ) , 0 ) AS Num_ordini_attivi, ROUND(coalesce( sum( costo_ordine * qta_reale ) , 0 ),2) AS StimaProxSpese\r\nfrom ordini_user_prodotti \r\nLEFT JOIN ordini_prodotti ON ordini_prodotti.idordine = ordini_user_prodotti.idordine and ordini_prodotti.idprodotto = ordini_user_prodotti.idprodotto\r\nLEFT JOIN ordini ON ordini_prodotti.idordine = ordini.idordine\r\nwhere ordini.archiviato = 'N'\r\ngroup by iduser) ordini_user_prodotti1\r\nON ordini_user_prodotti1.iduser = users.iduser\r\nWHERE groups.idgroup =5\r\nGROUP BY groups.idgroup, groups.nome, cognome, users.nome\r\nORDER BY groups.nome, cognome\r\n\r\n"}}