dolibarr  9.0.0
cabyprodserv.php
Go to the documentation of this file.
1 <?php
2 /* Copyright (C) 2013 Antoine Iauch <aiauch@gpcsolutions.fr>
3  * Copyright (C) 2013-2016 Laurent Destailleur <eldy@users.sourceforge.net>
4  * Copyright (C) 2015 Raphaël Doursenaud <rdoursenaud@gpcsolutions.fr>
5  * Copyright (C) 2018 Frédéric France <frederic.france@netlogic.fr>
6  *
7  * This program is free software; you can redistribute it and/or modify
8  * it under the terms of the GNU General Public License as published by
9  * the Free Software Foundation; either version 3 of the License, or
10  * (at your option) any later version.
11  *
12  * This program is distributed in the hope that it will be useful,
13  * but WITHOUT ANY WARRANTY; without even the implied warranty of
14  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
15  * GNU General Public License for more details.
16  *
17  * You should have received a copy of the GNU General Public License
18  * along with this program. If not, see <http://www.gnu.org/licenses/>.
19  */
20 
26 require '../../main.inc.php';
27 require_once DOL_DOCUMENT_ROOT.'/core/lib/report.lib.php';
28 require_once DOL_DOCUMENT_ROOT.'/core/lib/tax.lib.php';
29 require_once DOL_DOCUMENT_ROOT.'/core/lib/date.lib.php';
30 require_once DOL_DOCUMENT_ROOT.'/core/class/html.formother.class.php';
31 require_once DOL_DOCUMENT_ROOT.'/categories/class/categorie.class.php';
32 
33 // Load translation files required by the page
34 $langs->loadLangs(array("products","categories","errors",'accountancy'));
35 
36 // Security pack (data & check)
37 $socid = GETPOST('socid','int');
38 
39 if ($user->societe_id > 0) $socid = $user->societe_id;
40 if (! empty($conf->comptabilite->enabled)) $result=restrictedArea($user,'compta','','','resultat');
41 if (! empty($conf->accounting->enabled)) $result=restrictedArea($user,'accounting','','','comptarapport');
42 
43 // Define modecompta ('CREANCES-DETTES' or 'RECETTES-DEPENSES')
44 $modecompta = $conf->global->ACCOUNTING_MODE;
45 if (GETPOST("modecompta")) $modecompta=GETPOST("modecompta");
46 
47 $sortorder=isset($_GET["sortorder"])?$_GET["sortorder"]:$_POST["sortorder"];
48 $sortfield=isset($_GET["sortfield"])?$_GET["sortfield"]:$_POST["sortfield"];
49 if (! $sortorder) $sortorder="asc";
50 if (! $sortfield) $sortfield="ref";
51 
52 // Category
53 $selected_cat = (int) GETPOST('search_categ', 'int');
54 $subcat = false;
55 if (GETPOST('subcat', 'alpha') === 'yes') {
56  $subcat = true;
57 }
58 // product/service
59 $selected_type = GETPOST('search_type', 'int');
60 if ($selected_type =='') $selected_type = -1;
61 
62 // Date range
63 $year=GETPOST("year");
64 $month=GETPOST("month");
65 $date_startyear = GETPOST("date_startyear");
66 $date_startmonth = GETPOST("date_startmonth");
67 $date_startday = GETPOST("date_startday");
68 $date_endyear = GETPOST("date_endyear");
69 $date_endmonth = GETPOST("date_endmonth");
70 $date_endday = GETPOST("date_endday");
71 if (empty($year))
72 {
73  $year_current = strftime("%Y",dol_now());
74  $month_current = strftime("%m",dol_now());
75  $year_start = $year_current;
76 } else {
77  $year_current = $year;
78  $month_current = strftime("%m",dol_now());
79  $year_start = $year;
80 }
81 $date_start=dol_mktime(0,0,0,GETPOST("date_startmonth"),GETPOST("date_startday"),GETPOST("date_startyear"));
82 $date_end=dol_mktime(23,59,59,GETPOST("date_endmonth"),GETPOST("date_endday"),GETPOST("date_endyear"));
83 // Quarter
84 if (empty($date_start) || empty($date_end)) // We define date_start and date_end
85 {
86  $q=GETPOST("q","int");
87  if (empty($q))
88  {
89  // We define date_start and date_end
90  $month_start=GETPOST("month")?GETPOST("month"):($conf->global->SOCIETE_FISCAL_MONTH_START?($conf->global->SOCIETE_FISCAL_MONTH_START):1);
91  $year_end=$year_start;
92  $month_end=$month_start;
93  if (! GETPOST("month")) // If month not forced
94  {
95  if (! GETPOST('year') && $month_start > $month_current)
96  {
97  $year_start--;
98  $year_end--;
99  }
100  $month_end=$month_start-1;
101  if ($month_end < 1) $month_end=12;
102  else $year_end++;
103  }
104  $date_start=dol_get_first_day($year_start,$month_start,false); $date_end=dol_get_last_day($year_end,$month_end,false);
105  }
106  else
107  {
108  if ($q==1) { $date_start=dol_get_first_day($year_start,1,false); $date_end=dol_get_last_day($year_start,3,false); }
109  if ($q==2) { $date_start=dol_get_first_day($year_start,4,false); $date_end=dol_get_last_day($year_start,6,false); }
110  if ($q==3) { $date_start=dol_get_first_day($year_start,7,false); $date_end=dol_get_last_day($year_start,9,false); }
111  if ($q==4) { $date_start=dol_get_first_day($year_start,10,false); $date_end=dol_get_last_day($year_start,12,false); }
112  }
113 } else {
114  // TODO We define q
115 }
116 
117 // $date_start and $date_end are defined. We force $year_start and $nbofyear
118 $tmps=dol_getdate($date_start);
119 $year_start = $tmps['year'];
120 $tmpe=dol_getdate($date_end);
121 $year_end = $tmpe['year'];
122 $nbofyear = ($year_end - $year_start) + 1;
123 
124 $commonparams=array();
125 $commonparams['modecompta']=$modecompta;
126 $commonparams['sortorder'] = $sortorder;
127 $commonparams['sortfield'] = $sortfield;
128 
129 $headerparams = array();
130 $headerparams['date_startyear'] = $date_startyear;
131 $headerparams['date_startmonth'] = $date_startmonth;
132 $headerparams['date_startday'] = $date_startday;
133 $headerparams['date_endyear'] = $date_endyear;
134 $headerparams['date_endmonth'] = $date_endmonth;
135 $headerparams['date_endday'] = $date_endday;
136 $headerparams['q'] = $q;
137 
138 $tableparams = array();
139 $tableparams['search_categ'] = $selected_cat;
140 $tableparams['search_type'] = $selected_type;
141 $tableparams['subcat'] = ($subcat === true)?'yes':'';
142 
143 // Adding common parameters
144 $allparams = array_merge($commonparams, $headerparams, $tableparams);
145 $headerparams = array_merge($commonparams, $headerparams);
146 $tableparams = array_merge($commonparams, $tableparams);
147 
148 foreach($allparams as $key => $value) {
149  $paramslink .= '&' . $key . '=' . $value;
150 }
151 
152 
153 /*
154  * View
155  */
156 
157 llxHeader();
158 
159 $form=new Form($db);
160 $formother = new FormOther($db);
161 
162 // TODO Report from bookkeeping not yet available, so we switch on report on business events
163 if ($modecompta=="BOOKKEEPING") $modecompta="CREANCES-DETTES";
164 if ($modecompta=="BOOKKEEPINGCOLLECTED") $modecompta="RECETTES-DEPENSES";
165 
166 // Show report header
167 if ($modecompta=="CREANCES-DETTES") {
168  $name=$langs->trans("Turnover").', '.$langs->trans("ByProductsAndServices");
169  $calcmode=$langs->trans("CalcModeDebt");
170  //$calcmode.='<br>('.$langs->trans("SeeReportInInputOutputMode",'<a href="'.$_SERVER["PHP_SELF"].'?year='.$year_start.'&modecompta=RECETTES-DEPENSES">','</a>').')';
171 
172  $description=$langs->trans("RulesCADue");
173  if (! empty($conf->global->FACTURE_DEPOSITS_ARE_JUST_PAYMENTS)) {
174  $description.= $langs->trans("DepositsAreNotIncluded");
175  } else {
176  $description.= $langs->trans("DepositsAreIncluded");
177  }
178 
179  $builddate=dol_now();
180 }
181 else if ($modecompta=="RECETTES-DEPENSES")
182 {
183  $name=$langs->trans("TurnoverCollected").', '.$langs->trans("ByProductsAndServices");
184  $calcmode=$langs->trans("CalcModeEngagement");
185  //$calcmode.='<br>('.$langs->trans("SeeReportInDueDebtMode",'<a href="'.$_SERVER["PHP_SELF"].'?year='.$year_start.'&modecompta=CREANCES-DETTES">','</a>').')';
186 
187  $description=$langs->trans("RulesCAIn");
188  $description.= $langs->trans("DepositsAreIncluded");
189 
190  $builddate=dol_now();
191 }
192 else if ($modecompta=="BOOKKEEPING")
193 {
194 
195 
196 }
197 else if ($modecompta=="BOOKKEEPINGCOLLECTED")
198 {
199 
200 
201 }
202 
203 $period=$form->selectDate($date_start, 'date_start', 0, 0, 0, '', 1, 0).' - '.$form->selectDate($date_end, 'date_end', 0, 0, 0, '', 1, 0);
204 if ($date_end == dol_time_plus_duree($date_start, 1, 'y') - 1) $periodlink='<a href="'.$_SERVER["PHP_SELF"].'?year='.($year_start-1).'&modecompta='.$modecompta.'">'.img_previous().'</a> <a href="'.$_SERVER["PHP_SELF"].'?year='.($year_start+1).'&modecompta='.$modecompta.'">'.img_next().'</a>';
205 else $periodlink = '';
206 
207 report_header($name,$namelink,$period,$periodlink,$description,$builddate,$exportlink,$tableparams,$calcmode);
208 
209 if (! empty($conf->accounting->enabled) && $modecompta != 'BOOKKEEPING')
210 {
211  print info_admin($langs->trans("WarningReportNotReliable"), 0, 0, 1);
212 }
213 
214 
215 
216 $name=array();
217 
218 // SQL request
219 $catotal=0;
220 $catotal_ht=0;
221 $qtytotal=0;
222 
223 if ($modecompta == 'CREANCES-DETTES')
224 {
225  $sql = "SELECT DISTINCT p.rowid as rowid, p.ref as ref, p.label as label, p.fk_product_type as product_type,";
226  $sql.= " SUM(l.total_ht) as amount, SUM(l.total_ttc) as amount_ttc,";
227  $sql.= " SUM(CASE WHEN f.type = 2 THEN -l.qty ELSE l.qty END) as qty";
228  $sql.= " FROM ".MAIN_DB_PREFIX."facture as f, ".MAIN_DB_PREFIX."facturedet as l";
229  $sql.= " LEFT JOIN ".MAIN_DB_PREFIX."product as p ON l.fk_product = p.rowid";
230  if ($selected_cat === -2) // Without any category
231  {
232  $sql.= " LEFT JOIN ".MAIN_DB_PREFIX."categorie_product as cp ON p.rowid = cp.fk_product";
233  }
234  else if ($selected_cat) // Into a specific category
235  {
236  $sql.= ", ".MAIN_DB_PREFIX."categorie as c, ".MAIN_DB_PREFIX."categorie_product as cp";
237  }
238  $sql.= " WHERE l.fk_facture = f.rowid";
239  $sql.= " AND f.fk_statut in (1,2)";
240  if (! empty($conf->global->FACTURE_DEPOSITS_ARE_JUST_PAYMENTS)) {
241  $sql.= " AND f.type IN (0,1,2,5)";
242  } else {
243  $sql.= " AND f.type IN (0,1,2,3,5)";
244  }
245  if ($date_start && $date_end) {
246  $sql.= " AND f.datef >= '".$db->idate($date_start)."' AND f.datef <= '".$db->idate($date_end)."'";
247  }
248  if ($selected_type >=0)
249  {
250  $sql.= " AND l.product_type = ".$selected_type;
251  }
252  if ($selected_cat === -2) // Without any category
253  {
254  $sql.=" AND cp.fk_product is null";
255  }
256  else if ($selected_cat) { // Into a specific category
257  $sql.= " AND (c.rowid = ".$selected_cat;
258  if ($subcat) $sql.=" OR c.fk_parent = " . $selected_cat;
259  $sql.= ")";
260  $sql.= " AND cp.fk_categorie = c.rowid AND cp.fk_product = p.rowid";
261  }
262  $sql.= " AND f.entity = ".$conf->entity;
263  $sql.= " GROUP BY p.rowid, p.ref, p.label, p.fk_product_type";
264  $sql.= $db->order($sortfield,$sortorder);
265 
266  dol_syslog("cabyprodserv", LOG_DEBUG);
267  $result = $db->query($sql);
268  if ($result) {
269  $num = $db->num_rows($result);
270  $i=0;
271  while ($i < $num) {
272  $obj = $db->fetch_object($result);
273  $amount_ht[$obj->rowid] = $obj->amount;
274  $amount[$obj->rowid] = $obj->amount_ttc;
275  $qty[$obj->rowid] = $obj->qty;
276  $name[$obj->rowid] = $obj->ref . '&nbsp;-&nbsp;' . $obj->label;
277  $type[$obj->rowid] = $obj->product_type;
278  $catotal_ht+=$obj->amount;
279  $catotal+=$obj->amount_ttc;
280  $qtytotal+=$obj->qty;
281  $i++;
282  }
283  } else {
284  dol_print_error($db);
285  }
286 
287  // Show Array
288  $i=0;
289  print '<form method="POST" action="'.$_SERVER["PHP_SELF"].'">';
290  // Extra parameters management
291  foreach($headerparams as $key => $value)
292  {
293  print '<input type="hidden" name="'.$key.'" value="'.$value.'">';
294  }
295 
296  $moreforfilter='';
297 
298  print '<div class="div-table-responsive">';
299  print '<table class="tagtable liste'.($moreforfilter?" listwithfilterbefore":"").'">'."\n";
300 
301  // Category filter
302  print '<tr class="liste_titre">';
303  print '<td>';
304  print $langs->trans("Category") . ': ' . $formother->select_categories(Categorie::TYPE_PRODUCT, $selected_cat, 'search_categ', true);
305  print ' ';
306  print $langs->trans("SubCats") . '? ';
307  print '<input type="checkbox" name="subcat" value="yes"';
308  if ($subcat) {
309  print ' checked';
310  }
311  print '>';
312  // type filter (produit/service)
313  print ' ';
314  print $langs->trans("Type"). ': ';
315  $form->select_type_of_lines(isset($selected_type)?$selected_type:-1,'search_type',1,1,1);
316  print '</td>';
317 
318  print '<td colspan="5" align="right">';
319  print '<input type="image" class="liste_titre" name="button_search" src="'.img_picto($langs->trans("Search"),'search.png','','',1).'" value="'.dol_escape_htmltag($langs->trans("Search")).'" title="'.dol_escape_htmltag($langs->trans("Search")).'">';
320  print '</td></tr>';
321 
322  // Array header
323  print "<tr class=\"liste_titre\">";
325  $langs->trans("Product"),
326  $_SERVER["PHP_SELF"],
327  "ref",
328  "",
329  $paramslink,
330  "",
331  $sortfield,
332  $sortorder
333  );
335  $langs->trans('Quantity'),
336  $_SERVER["PHP_SELF"],
337  "qty",
338  "",
339  $paramslink,
340  'align="right"',
341  $sortfield,
342  $sortorder
343  );
345  $langs->trans("Percentage"),
346  $_SERVER["PHP_SELF"],
347  "qty",
348  "",
349  $paramslink,
350  'align="right"',
351  $sortfield,
352  $sortorder
353  );
355  $langs->trans('AmountHT'),
356  $_SERVER["PHP_SELF"],
357  "amount",
358  "",
359  $paramslink,
360  'align="right"',
361  $sortfield,
362  $sortorder
363  );
365  $langs->trans("AmountTTC"),
366  $_SERVER["PHP_SELF"],
367  "amount_ttc",
368  "",
369  $paramslink,
370  'align="right"',
371  $sortfield,
372  $sortorder
373  );
375  $langs->trans("Percentage"),
376  $_SERVER["PHP_SELF"],
377  "amount_ttc",
378  "",
379  $paramslink,
380  'align="right"',
381  $sortfield,
382  $sortorder
383  );
384  print "</tr>\n";
385 
386  if (count($name)) {
387  foreach($name as $key=>$value) {
388 
389  print '<tr class="oddeven">';
390 
391  // Product
392  print "<td>";
393  $fullname=$name[$key];
394  if ($key > 0) {
395  $linkname='<a href="'.DOL_URL_ROOT.'/product/card.php?id='.$key.'">'.img_object($langs->trans("ShowProduct"),$type[$key]==0?'product':'service').' '.$fullname.'</a>';
396  } else {
397  $linkname=$langs->trans("PaymentsNotLinkedToProduct");
398  }
399  print $linkname;
400  print "</td>\n";
401 
402  // Quantity
403  print '<td align="right">';
404  print $qty[$key];
405  print '</td>';
406 
407  // Percent;
408  print '<td align="right">'.($qtytotal > 0 ? round(100 * $qty[$key] / $qtytotal, 2).'%' : '&nbsp;').'</td>';
409 
410  // Amount w/o VAT
411  print '<td align="right">';
412  /*if ($key > 0) {
413  print '<a href="'.DOL_URL_ROOT.'/compta/facture/list.php?productid='.$key.'">';
414  } else {
415  print '<a href="#">';
416  }*/
417  print price($amount_ht[$key]);
418  //print '</a>';
419  print '</td>';
420 
421  // Amount with VAT
422  print '<td align="right">';
423  /*if ($key > 0) {
424  print '<a href="'.DOL_URL_ROOT.'/compta/facture/list.php?productid='.$key.'">';
425  } else {
426  print '<a href="#">';
427  }*/
428  print price($amount[$key]);
429  //print '</a>';
430  print '</td>';
431 
432  // Percent;
433  print '<td align="right">'.($catotal > 0 ? round(100 * $amount[$key] / $catotal, 2).'%' : '&nbsp;').'</td>';
434 
435  // TODO: statistics?
436 
437  print "</tr>\n";
438  $i++;
439  }
440 
441  // Total
442  print '<tr class="liste_total">';
443  print '<td>'.$langs->trans("Total").'</td>';
444  print '<td align="right">'.$qtytotal.'</td>';
445  print '<td align="right">100%</td>';
446  print '<td align="right">'.price($catotal_ht).'</td>';
447  print '<td align="right">'.price($catotal).'</td>';
448  print '<td align="right">100%</td>';
449  print '</tr>';
450 
451  $db->free($result);
452  }
453  print "</table>";
454  print '</div>';
455 
456  print '</form>';
457 } else {
458  // $modecompta != 'CREANCES-DETTES'
459  // "Calculation of part of each product for accountancy in this mode is not possible. When a partial payment (for example 5 euros) is done on an
460  // invoice with 2 product (product A for 10 euros and product B for 20 euros), what is part of paiment for product A and part of paiment for product B ?
461  // Because there is no way to know this, this report is not relevant.
462  print '<br>'.$langs->trans("TurnoverPerProductInCommitmentAccountingNotRelevant") . '<br>';
463 }
464 
465 // End of page
466 llxFooter();
467 $db->close();
llxFooter()
Empty footer.
Definition: wrapper.php:56
GETPOST($paramname, $check='none', $method=0, $filter=null, $options=null, $noreplace=0)
Return value of a param into GET or POST supervariable.
print
Draft customers invoices.
Definition: index.php:91
dol_mktime($hour, $minute, $second, $month, $day, $year, $gm=false, $check=1)
Return a timestamp date built from detailed informations (by default a local PHP server timestamp) Re...
dol_get_first_day($year, $month=1, $gm=false)
Return GMT time for first day of a month or year.
Definition: date.lib.php:453
dol_print_error($db='', $error='', $errors=null)
Affiche message erreur system avec toutes les informations pour faciliter le diagnostic et la remonte...
img_next($titlealt='default', $moreatt='')
Show next logo.
dol_escape_htmltag($stringtoescape, $keepb=0, $keepn=0)
Returns text escaped for inclusion in HTML alt or title tags, or into values of HTML input fields...
Class to manage generation of HTML components Only common components must be here.
dol_syslog($message, $level=LOG_INFO, $ident=0, $suffixinfilename='', $restricttologhandler='')
Write log message into outputs.
info_admin($text, $infoonimgalt=0, $nodiv=0, $admin='1', $morecss='')
Show information for admin users or standard users.
Classe permettant la generation de composants html autre Only common components are here...
llxHeader()
Empty header.
Definition: wrapper.php:44
dol_now($mode='gmt')
Return date for now.
price($amount, $form=0, $outlangs='', $trunc=1, $rounding=-1, $forcerounding=-1, $currency_code='')
Function to format a value into an amount for visual output Function used into PDF and HTML pages...
dol_get_last_day($year, $month=12, $gm=false)
Return GMT time for last day of a month or year.
Definition: date.lib.php:467
restrictedArea($user, $features, $objectid=0, $tableandshare='', $feature2='', $dbt_keyfield='fk_soc', $dbt_select='rowid', $isdraft=0)
Check permissions of a user to show a page and an object.
print_liste_field_titre($name, $file="", $field="", $begin="", $moreparam="", $moreattrib="", $sortfield="", $sortorder="", $prefix="", $tooltip="")
Show title line of an array.
dol_getdate($timestamp, $fast=false)
Return an array with locale date info.
img_previous($titlealt='default', $moreatt='')
Show previous logo.
report_header($reportname, $notused, $period, $periodlink, $description, $builddate, $exportlink='', $moreparam=array(), $calcmode='', $varlink='')
Show header of a report.
Definition: report.lib.php:41
dol_time_plus_duree($time, $duration_value, $duration_unit)
Add a delay to a date.
Definition: date.lib.php:116
img_object($titlealt, $picto, $moreatt='', $pictoisfullpath=false, $srconly=0, $notitle=0)
Show a picto called object_picto (generic function)