/htdocs/compta/stats/cabyuser.php

https://bitbucket.org/speedealing/speedealing · PHP · 308 lines · 226 code · 35 blank · 47 comment · 75 complexity · 08e7048140fadf42773fa88365d68b04 MD5 · raw file

  1. <?php
  2. /* Copyright (C) 2001-2003 Rodolphe Quiedeville <rodolphe@quiedeville.org>
  3. * Copyright (C) 2004-2011 Laurent Destailleur <eldy@users.sourceforge.net>
  4. * Copyright (C) 2005-2009 Regis Houssin <regis.houssin@capnetworks.com>
  5. *
  6. * This program is free software; you can redistribute it and/or modify
  7. * it under the terms of the GNU General Public License as published by
  8. * the Free Software Foundation; either version 3 of the License, or
  9. * (at your option) any later version.
  10. *
  11. * This program is distributed in the hope that it will be useful,
  12. * but WITHOUT ANY WARRANTY; without even the implied warranty of
  13. * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
  14. * GNU General Public License for more details.
  15. *
  16. * You should have received a copy of the GNU General Public License
  17. * along with this program. If not, see <http://www.gnu.org/licenses/>.
  18. */
  19. /**
  20. * \file htdocs/compta/stats/cabyuser.php
  21. * \brief Page reporting Salesover by user
  22. */
  23. require '../../main.inc.php';
  24. require_once DOL_DOCUMENT_ROOT.'/core/lib/report.lib.php';
  25. require_once DOL_DOCUMENT_ROOT.'/core/lib/tax.lib.php';
  26. require_once DOL_DOCUMENT_ROOT.'/core/lib/date.lib.php';
  27. // Security check
  28. $socid = isset($_REQUEST["socid"])?$_REQUEST["socid"]:'';
  29. if ($user->societe_id > 0) $socid = $user->societe_id;
  30. if (!$user->rights->compta->resultat->lire && !$user->rights->accounting->comptarapport->lire)
  31. accessforbidden();
  32. // Define modecompta ('CREANCES-DETTES' or 'RECETTES-DEPENSES')
  33. $modecompta = $conf->global->COMPTA_MODE;
  34. if (GETPOST("modecompta")) $modecompta=GETPOST("modecompta");
  35. $sortorder=isset($_GET["sortorder"])?$_GET["sortorder"]:$_POST["sortorder"];
  36. $sortfield=isset($_GET["sortfield"])?$_GET["sortfield"]:$_POST["sortfield"];
  37. if (! $sortorder) $sortorder="asc";
  38. if (! $sortfield) $sortfield="name";
  39. // Date range
  40. $year=GETPOST("year");
  41. $month=GETPOST("month");
  42. if (empty($year))
  43. {
  44. $year_current = strftime("%Y",dol_now());
  45. $month_current = strftime("%m",dol_now());
  46. $year_start = $year_current;
  47. } else {
  48. $year_current = $year;
  49. $month_current = strftime("%m",dol_now());
  50. $year_start = $year;
  51. }
  52. $date_start=dol_mktime(0,0,0,$_REQUEST["date_startmonth"],$_REQUEST["date_startday"],$_REQUEST["date_startyear"]);
  53. $date_end=dol_mktime(23,59,59,$_REQUEST["date_endmonth"],$_REQUEST["date_endday"],$_REQUEST["date_endyear"]);
  54. // Quarter
  55. if (empty($date_start) || empty($date_end)) // We define date_start and date_end
  56. {
  57. $q=GETPOST("q")?GETPOST("q"):0;
  58. if ($q==0)
  59. {
  60. // We define date_start and date_end
  61. $month_start=GETPOST("month")?GETPOST("month"):($conf->global->SOCIETE_FISCAL_MONTH_START?($conf->global->SOCIETE_FISCAL_MONTH_START):1);
  62. $year_end=$year_start;
  63. $month_end=$month_start;
  64. if (! GETPOST("month")) // If month not forced
  65. {
  66. if (! GETPOST('year') && $month_start > $month_current)
  67. {
  68. $year_start--;
  69. $year_end--;
  70. }
  71. $month_end=$month_start-1;
  72. if ($month_end < 1) $month_end=12;
  73. else $year_end++;
  74. }
  75. $date_start=dol_get_first_day($year_start,$month_start,false); $date_end=dol_get_last_day($year_end,$month_end,false);
  76. }
  77. if ($q==1) { $date_start=dol_get_first_day($year_start,1,false); $date_end=dol_get_last_day($year_start,3,false); }
  78. if ($q==2) { $date_start=dol_get_first_day($year_start,4,false); $date_end=dol_get_last_day($year_start,6,false); }
  79. if ($q==3) { $date_start=dol_get_first_day($year_start,7,false); $date_end=dol_get_last_day($year_start,9,false); }
  80. if ($q==4) { $date_start=dol_get_first_day($year_start,10,false); $date_end=dol_get_last_day($year_start,12,false); }
  81. }
  82. else
  83. {
  84. // TODO We define q
  85. }
  86. /*
  87. * View
  88. */
  89. llxHeader();
  90. $form=new Form($db);
  91. // Affiche en-tete du rapport
  92. if ($modecompta=="CREANCES-DETTES")
  93. {
  94. $nom=$langs->trans("SalesTurnover").', '.$langs->trans("ByUserAuthorOfInvoice");
  95. $nom.='<br>('.$langs->trans("SeeReportInInputOutputMode",'<a href="'.$_SERVER["PHP_SELF"].'?year='.$year.'&modecompta=RECETTES-DEPENSES">','</a>').')';
  96. $period=$form->select_date($date_start,'date_start',0,0,0,'',1,0,1).' - '.$form->select_date($date_end,'date_end',0,0,0,'',1,0,1);
  97. //$periodlink="<a href='".$_SERVER["PHP_SELF"]."?year=".($year-1)."&modecompta=".$modecompta."'>".img_previous()."</a> <a href='".$_SERVER["PHP_SELF"]."?year=".($year+1)."&modecompta=".$modecompta."'>".img_next()."</a>";
  98. $description=$langs->trans("RulesCADue");
  99. if (! empty($conf->global->FACTURE_DEPOSITS_ARE_JUST_PAYMENTS)) $description.= $langs->trans("DepositsAreNotIncluded");
  100. else $description.= $langs->trans("DepositsAreIncluded");
  101. $builddate=time();
  102. //$exportlink=$langs->trans("NotYetAvailable");
  103. }
  104. else {
  105. $nom=$langs->trans("SalesTurnover").', '.$langs->trans("ByUserAuthorOfInvoice");
  106. $nom.='<br>('.$langs->trans("SeeReportInDueDebtMode",'<a href="'.$_SERVER["PHP_SELF"].'?year='.$year.'&modecompta=CREANCES-DETTES">','</a>').')';
  107. $period=$form->select_date($date_start,'date_start',0,0,0,'',1,0,1).' - '.$form->select_date($date_end,'date_end',0,0,0,'',1,0,1);
  108. //$periodlink="<a href='".$_SERVER["PHP_SELF"]."?year=".($year-1)."&modecompta=".$modecompta."'>".img_previous()."</a> <a href='".$_SERVER["PHP_SELF"]."?year=".($year+1)."&modecompta=".$modecompta."'>".img_next()."</a>";
  109. $description=$langs->trans("RulesCAIn");
  110. $description.= $langs->trans("DepositsAreIncluded");
  111. $builddate=time();
  112. //$exportlink=$langs->trans("NotYetAvailable");
  113. }
  114. $moreparam=array();
  115. if (! empty($modecompta)) $moreparam['modecompta']=$modecompta;
  116. report_header($nom,$nomlink,$period,$periodlink,$description,$builddate,$exportlink,$moreparam);
  117. // Charge tableau
  118. $catotal=0;
  119. if ($modecompta == 'CREANCES-DETTES')
  120. {
  121. $sql = "SELECT u.rowid as rowid, u.name as name, u.firstname as firstname, sum(f.total) as amount, sum(f.total_ttc) as amount_ttc";
  122. $sql.= " FROM ".MAIN_DB_PREFIX."user as u";
  123. $sql.= " LEFT JOIN ".MAIN_DB_PREFIX."facture as f ON f.fk_user_author = u.rowid";
  124. $sql.= " WHERE f.fk_statut in (1,2)";
  125. if (! empty($conf->global->FACTURE_DEPOSITS_ARE_JUST_PAYMENTS)) $sql.= " AND f.type IN (0,1,2)";
  126. else $sql.= " AND f.type IN (0,1,2,3)";
  127. if ($date_start && $date_end) $sql.= " AND f.datef >= '".$db->idate($date_start)."' AND f.datef <= '".$db->idate($date_end)."'";
  128. }
  129. else
  130. {
  131. /*
  132. * Liste des paiements (les anciens paiements ne sont pas vus par cette requete car, sur les
  133. * vieilles versions, ils n'etaient pas lies via paiement_facture. On les ajoute plus loin)
  134. */
  135. $sql = "SELECT u.rowid as rowid, u.name as name, u.firstname as firstname, sum(pf.amount) as amount_ttc";
  136. $sql.= " FROM ".MAIN_DB_PREFIX."user as u" ;
  137. $sql.= " LEFT JOIN ".MAIN_DB_PREFIX."facture as f ON f.fk_user_author = u.rowid ";
  138. $sql.= " LEFT JOIN ".MAIN_DB_PREFIX."paiement_facture as pf ON pf.fk_facture = f.rowid";
  139. $sql.= " LEFT JOIN ".MAIN_DB_PREFIX."paiement as p ON p.rowid = pf.fk_paiement";
  140. $sql.= " WHERE 1=1";
  141. if ($date_start && $date_end) $sql.= " AND p.datep >= '".$db->idate($date_start)."' AND p.datep <= '".$db->idate($date_end)."'";
  142. }
  143. $sql.= " AND f.entity = ".$conf->entity;
  144. if ($socid) $sql.= " AND f.fk_soc = ".$socid;
  145. $sql .= " GROUP BY u.rowid, u.name, u.firstname";
  146. $sql .= " ORDER BY u.rowid";
  147. $result = $db->query($sql);
  148. if ($result)
  149. {
  150. $num = $db->num_rows($result);
  151. $i=0;
  152. while ($i < $num)
  153. {
  154. $obj = $db->fetch_object($result);
  155. $amount[$obj->rowid] = $obj->amount_ttc;
  156. $name[$obj->rowid] = $obj->name.' '.$obj->firstname;
  157. $catotal+=$obj->amount_ttc;
  158. $i++;
  159. }
  160. }
  161. else {
  162. dol_print_error($db);
  163. }
  164. // On ajoute les paiements ancienne version, non lies par paiement_facture donc sans user
  165. if ($modecompta != 'CREANCES-DETTES')
  166. {
  167. $sql = "SELECT -1 as rowidx, '' as name, '' as firstname, sum(p.amount) as amount_ttc";
  168. $sql.= " FROM ".MAIN_DB_PREFIX."bank as b";
  169. $sql.= ", ".MAIN_DB_PREFIX."bank_account as ba";
  170. $sql.= ", ".MAIN_DB_PREFIX."paiement as p";
  171. $sql.= " LEFT JOIN ".MAIN_DB_PREFIX."paiement_facture as pf ON p.rowid = pf.fk_paiement";
  172. $sql.= " WHERE pf.rowid IS NULL";
  173. $sql.= " AND p.fk_bank = b.rowid";
  174. $sql.= " AND b.fk_account = ba.rowid";
  175. $sql.= " AND ba.entity = ".$conf->entity;
  176. if ($date_start && $date_end) $sql.= " AND p.datep >= '".$db->idate($date_start)."' AND p.datep <= '".$db->idate($date_end)."'";
  177. $sql.= " GROUP BY rowidx, name, firstname";
  178. $sql.= " ORDER BY rowidx";
  179. $result = $db->query($sql);
  180. if ($result)
  181. {
  182. $num = $db->num_rows($result);
  183. $i=0;
  184. while ($i < $num)
  185. {
  186. $obj = $db->fetch_object($result);
  187. $amount[$obj->rowidx] = $obj->amount_ttc;
  188. $name[$obj->rowidx] = $obj->name.' '.$obj->firstname;
  189. $catotal+=$obj->amount_ttc;
  190. $i++;
  191. }
  192. }
  193. else {
  194. dol_print_error($db);
  195. }
  196. }
  197. $i = 0;
  198. print "<table class=\"noborder\" width=\"100%\">";
  199. print "<tr class=\"liste_titre\">";
  200. print_liste_field_titre($langs->trans("User"),$_SERVER["PHP_SELF"],"name","",'&amp;year='.($year).'&modecompta='.$modecompta,"",$sortfield,$sortorder);
  201. print_liste_field_titre($langs->trans("AmountTTC"),$_SERVER["PHP_SELF"],"amount_ttc","",'&amp;year='.($year).'&modecompta='.$modecompta,'align="right"',$sortfield,$sortorder);
  202. print_liste_field_titre($langs->trans("Percentage"),$_SERVER["PHP_SELF"],"amount_ttc","",'&amp;year='.($year).'&modecompta='.$modecompta,'align="right"',$sortfield,$sortorder);
  203. print_liste_field_titre($langs->trans("OtherStatistics"),$_SERVER["PHP_SELF"],"","","",'align="center" width="20%"');
  204. print "</tr>\n";
  205. $var=true;
  206. if (count($amount))
  207. {
  208. $arrayforsort=$name;
  209. // We define arrayforsort
  210. if ($sortfield == 'name' && $sortorder == 'asc') {
  211. asort($name);
  212. $arrayforsort=$name;
  213. }
  214. if ($sortfield == 'name' && $sortorder == 'desc') {
  215. arsort($name);
  216. $arrayforsort=$name;
  217. }
  218. if ($sortfield == 'amount_ttc' && $sortorder == 'asc') {
  219. asort($amount);
  220. $arrayforsort=$amount;
  221. }
  222. if ($sortfield == 'amount_ttc' && $sortorder == 'desc') {
  223. arsort($amount);
  224. $arrayforsort=$amount;
  225. }
  226. foreach($arrayforsort as $key => $value)
  227. {
  228. $var=!$var;
  229. print "<tr ".$bc[$var].">";
  230. // Third party
  231. $fullname=$name[$key];
  232. if ($key >= 0) {
  233. $linkname='<a href="'.DOL_URL_ROOT.'/user/fiche.php?id='.$key.'">'.img_object($langs->trans("ShowUser"),'user').' '.$fullname.'</a>';
  234. }
  235. else {
  236. $linkname=$langs->trans("PaymentsNotLinkedToUser");
  237. }
  238. print "<td>".$linkname."</td>\n";
  239. // Amount
  240. print '<td align="right">';
  241. if ($modecompta != 'CREANCES-DETTES')
  242. {
  243. if ($key > 0) print '<a href="'.DOL_URL_ROOT.'/compta/paiement/liste.php?userid='.$key.'">';
  244. else print '<a href="'.DOL_URL_ROOT.'/compta/paiement/liste.php?userid=-1">';
  245. }
  246. else
  247. {
  248. if ($key > 0) print '<a href="'.DOL_URL_ROOT.'/compta/facture/list.php?userid='.$key.'">';
  249. else print '<a href="#">';
  250. }
  251. print price($amount[$key]);
  252. print '</td>';
  253. // Percent
  254. print '<td align="right">'.($catotal > 0 ? round(100 * $amount[$key] / $catotal,2).'%' : '&nbsp;').'</td>';
  255. // Other stats
  256. print '<td align="center">';
  257. if (! empty($conf->propal->enabled) && $key>0) print '&nbsp;<a href="'.DOL_URL_ROOT.'/comm/propal/stats/index.php?userid='.$key.'">'.img_picto($langs->trans("ProposalStats"),"stats").'</a>&nbsp;';
  258. if (! empty($conf->commande->enabled) && $key>0) print '&nbsp;<a href="'.DOL_URL_ROOT.'/commande/stats/index.php?userid='.$key.'">'.img_picto($langs->trans("OrderStats"),"stats").'</a>&nbsp;';
  259. if (! empty($conf->facture->enabled) && $key>0) print '&nbsp;<a href="'.DOL_URL_ROOT.'/compta/facture/stats/index.php?userid='.$key.'">'.img_picto($langs->trans("InvoiceStats"),"stats").'</a>&nbsp;';
  260. print '</td>';
  261. print "</tr>\n";
  262. $i++;
  263. }
  264. // Total
  265. print '<tr class="liste_total"><td>'.$langs->trans("Total").'</td><td align="right">'.price($catotal).'</td><td>&nbsp;</td>';
  266. print '<td>&nbsp;</td>';
  267. print '</tr>';
  268. $db->free($result);
  269. }
  270. print "</table>";
  271. llxFooter();
  272. $db->close();
  273. ?>