dolibarr  7.0.0-beta
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  *
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 
25 require '../../main.inc.php';
26 require_once DOL_DOCUMENT_ROOT.'/core/lib/report.lib.php';
27 require_once DOL_DOCUMENT_ROOT.'/core/lib/tax.lib.php';
28 require_once DOL_DOCUMENT_ROOT.'/core/lib/date.lib.php';
29 require_once DOL_DOCUMENT_ROOT.'/core/class/html.formother.class.php';
30 require_once DOL_DOCUMENT_ROOT.'/categories/class/categorie.class.php';
31 
32 $langs->load("products");
33 $langs->load("categories");
34 $langs->load("errors");
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,$_REQUEST["date_startmonth"],$_REQUEST["date_startday"],$_REQUEST["date_startyear"]);
82 $date_end=dol_mktime(23,59,59,$_REQUEST["date_endmonth"],$_REQUEST["date_endday"],$_REQUEST["date_endyear"]);
83 // Quarter
84 if (empty($date_start) || empty($date_end)) // We define date_start and date_end
85 {
86  $q=GETPOST("q")?GETPOST("q"):0;
87  if ($q==0)
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  if ($q==1) { $date_start=dol_get_first_day($year_start,1,false); $date_end=dol_get_last_day($year_start,3,false); }
107  if ($q==2) { $date_start=dol_get_first_day($year_start,4,false); $date_end=dol_get_last_day($year_start,6,false); }
108  if ($q==3) { $date_start=dol_get_first_day($year_start,7,false); $date_end=dol_get_last_day($year_start,9,false); }
109  if ($q==4) { $date_start=dol_get_first_day($year_start,10,false); $date_end=dol_get_last_day($year_start,12,false); }
110 } else {
111  // TODO We define q
112 }
113 
114 $commonparams=array();
115 $commonparams['modecompta']=$modecompta;
116 $commonparams['sortorder'] = $sortorder;
117 $commonparams['sortfield'] = $sortfield;
118 
119 $headerparams = array();
120 $headerparams['date_startyear'] = $date_startyear;
121 $headerparams['date_startmonth'] = $date_startmonth;
122 $headerparams['date_startday'] = $date_startday;
123 $headerparams['date_endyear'] = $date_endyear;
124 $headerparams['date_endmonth'] = $date_endmonth;
125 $headerparams['date_endday'] = $date_endday;
126 $headerparams['q'] = $q;
127 
128 $tableparams = array();
129 $tableparams['search_categ'] = $selected_cat;
130 $tableparams['search_type'] = $selected_type;
131 $tableparams['subcat'] = ($subcat === true)?'yes':'';
132 
133 // Adding common parameters
134 $allparams = array_merge($commonparams, $headerparams, $tableparams);
135 $headerparams = array_merge($commonparams, $headerparams);
136 $tableparams = array_merge($commonparams, $tableparams);
137 
138 foreach($allparams as $key => $value) {
139  $paramslink .= '&' . $key . '=' . $value;
140 }
141 
142 
143 /*
144  * View
145  */
146 
147 llxHeader();
148 
149 $form=new Form($db);
150 $formother = new FormOther($db);
151 
152 // Show report header
153 $name=$langs->trans("SalesTurnover").', '.$langs->trans("ByProductsAndServices");
154 
155 if ($modecompta=="CREANCES-DETTES") {
156  $calcmode=$langs->trans("CalcModeDebt");
157  $calcmode.='<br>('.$langs->trans("SeeReportInInputOutputMode",'<a href="'.$_SERVER["PHP_SELF"].'?year='.$year.'&modecompta=RECETTES-DEPENSES">','</a>').')';
158 
159  $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);
160 
161  $description=$langs->trans("RulesCADue");
162  if (! empty($conf->global->FACTURE_DEPOSITS_ARE_JUST_PAYMENTS)) {
163  $description.= $langs->trans("DepositsAreNotIncluded");
164  } else {
165  $description.= $langs->trans("DepositsAreIncluded");
166  }
167 
168  $builddate=dol_now();
169 } else {
170  $calcmode=$langs->trans("CalcModeEngagement");
171  $calcmode.='<br>('.$langs->trans("SeeReportInDueDebtMode",'<a href="'.$_SERVER["PHP_SELF"].'?year='.$year.'&modecompta=CREANCES-DETTES">','</a>').')';
172 
173  $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);
174 
175  $description=$langs->trans("RulesCAIn");
176  $description.= $langs->trans("DepositsAreIncluded");
177 
178  $builddate=dol_now();
179 }
180 
181 report_header($name,$namelink,$period,$periodlink,$description,$builddate,$exportlink,$tableparams,$calcmode);
182 
183 if (! empty($conf->accounting->enabled) && $modecompta != 'BOOKKEEPING')
184 {
185  print info_admin($langs->trans("WarningReportNotReliable"), 0, 0, 1);
186 }
187 
188 
189 
190 $name=array();
191 
192 // SQL request
193 $catotal=0;
194 $catotal_ht=0;
195 $qtytotal=0;
196 
197 if ($modecompta == 'CREANCES-DETTES')
198 {
199  $sql = "SELECT DISTINCT p.rowid as rowid, p.ref as ref, p.label as label, p.fk_product_type as product_type,";
200  $sql.= " SUM(l.total_ht) as amount, SUM(l.total_ttc) as amount_ttc,";
201  $sql.= " SUM(CASE WHEN f.type = 2 THEN -l.qty ELSE l.qty END) as qty";
202  $sql.= " FROM ".MAIN_DB_PREFIX."facture as f, ".MAIN_DB_PREFIX."facturedet as l, ".MAIN_DB_PREFIX."product as p";
203  if ($selected_cat === -2) // Without any category
204  {
205  $sql.= " LEFT OUTER JOIN ".MAIN_DB_PREFIX."categorie_product as cp ON p.rowid = cp.fk_product";
206  }
207  else if ($selected_cat) // Into a specific category
208  {
209  $sql.= ", ".MAIN_DB_PREFIX."categorie as c, ".MAIN_DB_PREFIX."categorie_product as cp";
210  }
211  $sql.= " WHERE l.fk_product = p.rowid";
212  $sql.= " AND l.fk_facture = f.rowid";
213  $sql.= " AND f.fk_statut in (1,2)";
214  if (! empty($conf->global->FACTURE_DEPOSITS_ARE_JUST_PAYMENTS)) {
215  $sql.= " AND f.type IN (0,1,2,5)";
216  } else {
217  $sql.= " AND f.type IN (0,1,2,3,5)";
218  }
219  if ($date_start && $date_end) {
220  $sql.= " AND f.datef >= '".$db->idate($date_start)."' AND f.datef <= '".$db->idate($date_end)."'";
221  }
222  if ($selected_type >=0)
223  {
224  $sql.= " AND l.product_type = ".$selected_type;
225  }
226  if ($selected_cat === -2) // Without any category
227  {
228  $sql.=" AND cp.fk_product is null";
229  }
230  else if ($selected_cat) { // Into a specific category
231  $sql.= " AND (c.rowid = ".$selected_cat;
232  if ($subcat) $sql.=" OR c.fk_parent = " . $selected_cat;
233  $sql.= ")";
234  $sql.= " AND cp.fk_categorie = c.rowid AND cp.fk_product = p.rowid";
235  }
236  $sql.= " AND f.entity = ".$conf->entity;
237  $sql.= " GROUP BY p.rowid, p.ref, p.label, p.fk_product_type";
238  $sql.= $db->order($sortfield,$sortorder);
239 
240  dol_syslog("cabyprodserv", LOG_DEBUG);
241  $result = $db->query($sql);
242  if ($result) {
243  $num = $db->num_rows($result);
244  $i=0;
245  while ($i < $num) {
246  $obj = $db->fetch_object($result);
247  $amount_ht[$obj->rowid] = $obj->amount;
248  $amount[$obj->rowid] = $obj->amount_ttc;
249  $qty[$obj->rowid] = $obj->qty;
250  $name[$obj->rowid] = $obj->ref . '&nbsp;-&nbsp;' . $obj->label;
251  $type[$obj->rowid] = $obj->product_type;
252  $catotal_ht+=$obj->amount;
253  $catotal+=$obj->amount_ttc;
254  $qtytotal+=$obj->qty;
255  $i++;
256  }
257  } else {
258  dol_print_error($db);
259  }
260 
261  // Show Array
262  $i=0;
263  print '<form method="POST" action="'.$_SERVER["PHP_SELF"].'">';
264  // Extra parameters management
265  foreach($headerparams as $key => $value)
266  {
267  print '<input type="hidden" name="'.$key.'" value="'.$value.'">';
268  }
269 
270  $moreforfilter='';
271 
272  print '<div class="div-table-responsive">';
273  print '<table class="tagtable liste'.($moreforfilter?" listwithfilterbefore":"").'">'."\n";
274 
275  // Category filter
276  print '<tr class="liste_titre">';
277  print '<td>';
278  print $langs->trans("Category") . ': ' . $formother->select_categories(Categorie::TYPE_PRODUCT, $selected_cat, 'search_categ', true);
279  print ' ';
280  print $langs->trans("SubCats") . '? ';
281  print '<input type="checkbox" name="subcat" value="yes"';
282  if ($subcat) {
283  print ' checked';
284  }
285  print '>';
286  // type filter (produit/service)
287  print ' ';
288  print $langs->trans("Type"). ': ';
289  $form->select_type_of_lines(isset($selected_type)?$selected_type:-1,'search_type',1,1,1);
290  print '</td>';
291 
292  print '<td colspan="5" align="right">';
293  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")).'">';
294  print '</td></tr>';
295 
296  // Array header
297  print "<tr class=\"liste_titre\">";
299  $langs->trans("Product"),
300  $_SERVER["PHP_SELF"],
301  "ref",
302  "",
303  $paramslink,
304  "",
305  $sortfield,
306  $sortorder
307  );
309  $langs->trans('Quantity'),
310  $_SERVER["PHP_SELF"],
311  "qty",
312  "",
313  $paramslink,
314  'align="right"',
315  $sortfield,
316  $sortorder
317  );
319  $langs->trans("Percentage"),
320  $_SERVER["PHP_SELF"],
321  "qty",
322  "",
323  $paramslink,
324  'align="right"',
325  $sortfield,
326  $sortorder
327  );
329  $langs->trans('AmountHT'),
330  $_SERVER["PHP_SELF"],
331  "amount",
332  "",
333  $paramslink,
334  'align="right"',
335  $sortfield,
336  $sortorder
337  );
339  $langs->trans("AmountTTC"),
340  $_SERVER["PHP_SELF"],
341  "amount_ttc",
342  "",
343  $paramslink,
344  'align="right"',
345  $sortfield,
346  $sortorder
347  );
349  $langs->trans("Percentage"),
350  $_SERVER["PHP_SELF"],
351  "amount_ttc",
352  "",
353  $paramslink,
354  'align="right"',
355  $sortfield,
356  $sortorder
357  );
358  print "</tr>\n";
359 
360  // Array Data
361  $var=true;
362 
363  if (count($name)) {
364  foreach($name as $key=>$value) {
365 
366  print '<tr class="oddeven">';
367 
368  // Product
369  $fullname=$name[$key];
370  if ($key >= 0) {
371  $linkname='<a href="'.DOL_URL_ROOT.'/product/card.php?id='.$key.'">'.img_object($langs->trans("ShowProduct"),$type[$key]==0?'product':'service').' '.$fullname.'</a>';
372  } else {
373  $linkname=$langs->trans("PaymentsNotLinkedToProduct");
374  }
375 
376  print "<td>".$linkname."</td>\n";
377 
378  // Quantity
379  print '<td align="right">';
380  print $qty[$key];
381  print '</td>';
382 
383  // Percent;
384  print '<td align="right">'.($qtytotal > 0 ? round(100 * $qty[$key] / $qtytotal, 2).'%' : '&nbsp;').'</td>';
385 
386  // Amount w/o VAT
387  print '<td align="right">';
388  /*if ($key > 0) {
389  print '<a href="'.DOL_URL_ROOT.'/compta/facture/list.php?productid='.$key.'">';
390  } else {
391  print '<a href="#">';
392  }*/
393  print price($amount_ht[$key]);
394  //print '</a>';
395  print '</td>';
396 
397  // Amount with VAT
398  print '<td align="right">';
399  /*if ($key > 0) {
400  print '<a href="'.DOL_URL_ROOT.'/compta/facture/list.php?productid='.$key.'">';
401  } else {
402  print '<a href="#">';
403  }*/
404  print price($amount[$key]);
405  //print '</a>';
406  print '</td>';
407 
408  // Percent;
409  print '<td align="right">'.($catotal > 0 ? round(100 * $amount[$key] / $catotal, 2).'%' : '&nbsp;').'</td>';
410 
411  // TODO: statistics?
412 
413  print "</tr>\n";
414  $i++;
415  }
416 
417  // Total
418  print '<tr class="liste_total">';
419  print '<td>'.$langs->trans("Total").'</td>';
420  print '<td align="right">'.price($qtytotal).'</td>';
421  print '<td>&nbsp;</td>';
422  print '<td align="right">'.price($catotal_ht).'</td>';
423  print '<td align="right">'.price($catotal).'</td>';
424  print '<td>&nbsp;</td>';
425  print '</tr>';
426 
427  $db->free($result);
428  }
429  print "</table>";
430  print '</div>';
431 
432  print '</form>';
433 } else {
434  // $modecompta != 'CREANCES-DETTES'
435  // "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
436  // 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 ?
437  // Because there is no way to know this, this report is not relevant.
438  print '<br>'.$langs->trans("TurnoverPerProductInCommitmentAccountingNotRelevant") . '<br>';
439 }
440 
441 llxFooter();
442 $db->close();
llxFooter()
Empty footer.
Definition: wrapper.php:58
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:445
dol_print_error($db='', $error='', $errors=null)
Affiche message erreur system avec toutes les informations pour faciliter le diagnostic et la remonte...
if(empty($reshook)) $form
View.
Definition: perms.php:103
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...
GETPOST($paramname, $check='none', $method=0, $filter=NULL, $options=NULL, $noreplace=0)
Return value of a param into GET or POST supervariable.
Class to manage generation of HTML components Only common components must be here.
info_admin($text, $infoonimgalt=0, $nodiv=0, $admin='1')
Show information for admin users or standard users.
dol_syslog($message, $level=LOG_INFO, $ident=0, $suffixinfilename='', $restricttologhandler='')
Write log message into outputs.
Classe permettant la generation de composants html autre Only common components are here...
llxHeader()
Empty header.
Definition: wrapper.php:46
img_object($titlealt, $picto, $moreatt= '', $pictoisfullpath=false, $srconly=0, $notitle=0)
Show a picto called object_picto (generic function)
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:459
print
Draft customers invoices.
Definition: index.php:91
print_liste_field_titre($name, $file="", $field="", $begin="", $moreparam="", $moreattrib="", $sortfield="", $sortorder="", $prefix="", $tooltip="")
Show title line of an array.
report_header($reportname, $notused, $period, $periodlink, $description, $builddate, $exportlink='', $moreparam=array(), $calcmode='', $varlink='')
Show header of a report.
Definition: report.lib.php:41
restrictedArea($user, $features, $objectid=0, $tableandshare='', $feature2='', $dbt_keyfield='fk_soc', $dbt_select='rowid', $objcanvas=null)
Check permissions of a user to show a page and an object.