/**
* Return shipment number by month for a year
*
* @param int $year Year to scan
* @return array Array with number by month
*/
function getNbByMonth($year)
{
global $user;
$sql = "SELECT date_format(d.datedon,'%m') as dm, COUNT(*) as nb";
$sql .= " FROM " . $this->from;
$sql .= " WHERE d.datedon BETWEEN '" . $this->db->idate(dol_get_first_day($year)) . "' AND '" . $this->db->idate(dol_get_last_day($year)) . "'";
$sql .= " AND " . $this->where;
$sql .= " GROUP BY dm";
$sql .= $this->db->order('dm', 'DESC');
$res = $this->_getNbByMonth($year, $sql);
return $res;
}
/**
* Load data for box to show them later
*
* @param int $max Maximum number of records to load
* @return void
*/
function loadBox($max = 5)
{
global $conf, $user, $langs, $db;
$this->max = $max;
$totalMnt = 0;
$totalnb = 0;
$totalDuree = 0;
include_once DOL_DOCUMENT_ROOT . "/projet/class/task.class.php";
$taskstatic = new Task($db);
$textHead = $langs->trans("Tasks") . " " . date("Y");
$this->info_box_head = array('text' => $textHead, 'limit' => dol_strlen($textHead));
// list the summary of the orders
if ($user->rights->projet->lire) {
// FIXME fk_statut on a task is not be used. We use the percent. This means this box is useless.
$sql = "SELECT pt.fk_statut, count(DISTINCT pt.rowid) as nb, sum(ptt.task_duration) as durationtot, sum(pt.planned_workload) as plannedtot";
$sql .= " FROM " . MAIN_DB_PREFIX . "projet_task as pt, " . MAIN_DB_PREFIX . "projet_task_time as ptt";
$sql .= " WHERE pt.datec BETWEEN '" . $this->db->idate(dol_get_first_day(date("Y"), 1)) . "' AND '" . $this->db->idate(dol_get_last_day(date("Y"), 12)) . "'";
$sql .= " AND pt.rowid = ptt.fk_task";
$sql .= " GROUP BY pt.fk_statut ";
$sql .= " ORDER BY pt.fk_statut DESC";
$sql .= $db->plimit($max, 0);
$result = $db->query($sql);
if ($result) {
$num = $db->num_rows($result);
$i = 0;
while ($i < $num) {
$objp = $db->fetch_object($result);
$this->info_box_contents[$i][] = array('td' => 'align="left"', 'text' => $langs->trans("Task") . " " . $taskstatic->LibStatut($objp->fk_statut, 0));
$this->info_box_contents[$i][] = array('td' => 'align="right"', 'text' => $objp->nb . " " . $langs->trans("Tasks"), 'url' => DOL_URL_ROOT . "/projet/tasks/list.php?leftmenu=projects&viewstatut=" . $objp->fk_statut);
$totalnb += $objp->nb;
$this->info_box_contents[$i][] = array('td' => 'align="right"', 'text' => ConvertSecondToTime($objp->plannedtot, 'all', 25200, 5));
$totalplannedtot += $objp->plannedtot;
$this->info_box_contents[$i][] = array('td' => 'align="right"', 'text' => ConvertSecondToTime($objp->durationtot, 'all', 25200, 5));
$totaldurationtot += $objp->durationtot;
$this->info_box_contents[$i][] = array('td' => 'align="right" width="18"', 'text' => $taskstatic->LibStatut($objp->fk_statut, 3));
$i++;
}
} else {
dol_print_error($this->db);
}
}
// Add the sum à the bottom of the boxes
$this->info_box_contents[$i][] = array('tr' => 'class="liste_total"', 'td' => 'align="left"', 'text' => $langs->trans("Total") . " " . $textHead);
$this->info_box_contents[$i][] = array('td' => 'align="right" ', 'text' => number_format($totalnb, 0, ',', ' ') . " " . $langs->trans("Tasks"));
$this->info_box_contents[$i][] = array('td' => 'align="right" ', 'text' => ConvertSecondToTime($totalplannedtot, 'all', 25200, 5));
$this->info_box_contents[$i][] = array('td' => 'align="right" ', 'text' => ConvertSecondToTime($totaldurationtot, 'all', 25200, 5));
$this->info_box_contents[$i][] = array('td' => '', 'text' => "");
}
});
});
</script>
_END;
// Add number of product when there is a filter on period
if (count($arrayofuniqueproduct) == 1 && is_numeric($year)) {
$productidselected = 0;
foreach ($arrayofuniqueproduct as $key => $val) {
$productidselected = $key;
$productlabelselected = $val;
}
$datebefore = dol_get_first_day($year ? $year : strftime("%Y", time()), $month ? $month : 1, true);
$dateafter = dol_get_last_day($year ? $year : strftime("%Y", time()), $month ? $month : 12, true);
$balancebefore = $movement->calculateBalanceForProductBefore($productidselected, $datebefore);
$balanceafter = $movement->calculateBalanceForProductBefore($productidselected, $dateafter);
//print '<tr class="total"><td class="liste_total">';
print $langs->trans("NbOfProductBeforePeriod", $productlabelselected, dol_print_date($datebefore, 'day', 'gmt'));
//print '</td>';
//print '<td class="liste_total" colspan="6" align="right">';
print ': ' . $balancebefore;
print "<br>\n";
//print '</td></tr>';
//print '<tr class="total"><td class="liste_total">';
print $langs->trans("NbOfProductAfterPeriod", $productlabelselected, dol_print_date($dateafter, 'day', 'gmt'));
//print '</td>';
//print '<td class="liste_total" colspan="6" align="right">';
print ': ' . $balanceafter;
if ($search_amount) {
$sql .= " AND bc.amount='" . $db->escape(price2num(trim($search_amount))) . "'";
}
if ($month > 0) {
if ($year > 0 && empty($day)) {
$sql .= " AND bc.date_bordereau BETWEEN '" . $db->idate(dol_get_first_day($year, $month, false)) . "' AND '" . $db->idate(dol_get_last_day($year, $month, false)) . "'";
} else {
if ($year > 0 && !empty($day)) {
$sql .= " AND bc.date_bordereau BETWEEN '" . $db->idate(dol_mktime(0, 0, 0, $month, $day, $year)) . "' AND '" . $db->idate(dol_mktime(23, 59, 59, $month, $day, $year)) . "'";
} else {
$sql .= " AND date_format(bc.date_bordereau, '%m') = '" . $month . "'";
}
}
} else {
if ($year > 0) {
$sql .= " AND bc.date_bordereau BETWEEN '" . $db->idate(dol_get_first_day($year, 1, false)) . "' AND '" . $db->idate(dol_get_last_day($year, 12, false)) . "'";
}
}
$sql .= " ORDER BY {$sortfield} {$sortorder}";
$sql .= $db->plimit($limit + 1, $offset);
//print "$sql";
$resql = $db->query($sql);
if ($resql) {
$num = $db->num_rows($resql);
$i = 0;
$params = '';
print_barre_liste($langs->trans("MenuChequeDeposits"), $page, $_SERVER["PHP_SELF"], $params, $sortfield, $sortorder, '', $num);
print '<form method="GET" action="' . $_SERVER["PHP_SELF"] . '">';
print '<table class="liste" width="100%">';
print '<tr class="liste_titre">';
print_liste_field_titre($langs->trans("Ref"), $_SERVER["PHP_SELF"], "bc.number", "", $params, "", $sortfield, $sortorder);
exit;
}
}
// Formulaire saisie salaire
if ($action == 'create') {
$year_current = strftime("%Y", dol_now());
$pastmonth = strftime("%m", dol_now()) - 1;
$pastmonthyear = $year_current;
if ($pastmonth == 0) {
$pastmonth = 12;
$pastmonthyear--;
}
$datesp = dol_mktime(0, 0, 0, $datespmonth, $datespday, $datespyear);
$dateep = dol_mktime(23, 59, 59, $dateepmonth, $dateepday, $dateepyear);
if (empty($datesp) || empty($dateep)) {
$datesp = dol_get_first_day($pastmonthyear, $pastmonth, false);
$dateep = dol_get_last_day($pastmonthyear, $pastmonth, false);
}
print '<form name="salary" action="' . $_SERVER["PHP_SELF"] . '" method="post">';
print '<input type="hidden" name="token" value="' . $_SESSION['newtoken'] . '">';
print '<input type="hidden" name="action" value="add">';
print_fiche_titre($langs->trans("NewSalaryPayment"), '', 'title_accountancy.png');
dol_fiche_head('', '');
print '<table class="border" width="100%">';
print "<tr>";
print '<td class="fieldrequired">' . $langs->trans("DatePayment") . '</td><td>';
print $form->select_date(empty($datep) ? -1 : $datep, "datep", '', '', '', 'add', 1, 1);
print '</td></tr>';
print '<tr><td>' . $langs->trans("DateValue") . '</td><td>';
print $form->select_date(empty($datev) ? -1 : $datev, "datev", '', '', '', 'add', 1, 1);
print '</td></tr>';
$sql .= " ROUND(SUM(IF(MONTH(ff.datef)=1,ffd.total_ht,0)),2) AS 'Janvier',";
$sql .= " ROUND(SUM(IF(MONTH(ff.datef)=2,ffd.total_ht,0)),2) AS 'Fevrier',";
$sql .= " ROUND(SUM(IF(MONTH(ff.datef)=3,ffd.total_ht,0)),2) AS 'Mars',";
$sql .= " ROUND(SUM(IF(MONTH(ff.datef)=4,ffd.total_ht,0)),2) AS 'Avril',";
$sql .= " ROUND(SUM(IF(MONTH(ff.datef)=5,ffd.total_ht,0)),2) AS 'Mai',";
$sql .= " ROUND(SUM(IF(MONTH(ff.datef)=6,ffd.total_ht,0)),2) AS 'Juin',";
$sql .= " ROUND(SUM(IF(MONTH(ff.datef)=7,ffd.total_ht,0)),2) AS 'Juillet',";
$sql .= " ROUND(SUM(IF(MONTH(ff.datef)=8,ffd.total_ht,0)),2) AS 'Aout',";
$sql .= " ROUND(SUM(IF(MONTH(ff.datef)=9,ffd.total_ht,0)),2) AS 'Septembre',";
$sql .= " ROUND(SUM(IF(MONTH(ff.datef)=10,ffd.total_ht,0)),2) AS 'Octobre',";
$sql .= " ROUND(SUM(IF(MONTH(ff.datef)=11,ffd.total_ht,0)),2) AS 'Novembre',";
$sql .= " ROUND(SUM(IF(MONTH(ff.datef)=12,ffd.total_ht,0)),2) AS 'Decembre',";
$sql .= " ROUND(SUM(ffd.total_ht),2) as 'Total'";
$sql .= " FROM " . MAIN_DB_PREFIX . "facture_fourn_det as ffd";
$sql .= " LEFT JOIN " . MAIN_DB_PREFIX . "facture_fourn as ff ON ff.rowid = ffd.fk_facture_fourn";
$sql .= " WHERE ff.datef >= '" . $db->idate(dol_get_first_day($y, 1, false)) . "'";
$sql .= " AND ff.datef <= '" . $db->idate(dol_get_last_day($y, 12, false)) . "'";
$sql .= " AND ff.fk_statut > 0 ";
if (!empty($conf->multicompany->enabled)) {
$sql .= " AND ff.entity IN (" . getEntity("facture_fourn", 1) . ")";
}
dol_syslog('/accountancy/supplier/index.php:: sql=' . $sql);
$resql = $db->query($sql);
if ($resql) {
$i = 0;
$num = $db->num_rows($resql);
while ($i < $num) {
$row = $db->fetch_row($resql);
print '<tr><td>' . $row[0] . '</td>';
print '<td align="right">' . $row[1] . '</td>';
print '<td align="right">' . price($row[2]) . '</td>';
$sql .= " ROUND(SUM(IF(MONTH(lp.date_paiement)=1,lp.montant,0)),2) AS 'Janvier',";
$sql .= " ROUND(SUM(IF(MONTH(lp.date_paiement)=2,lp.montant,0)),2) AS 'Fevrier',";
$sql .= " ROUND(SUM(IF(MONTH(lp.date_paiement)=3,lp.montant,0)),2) AS 'Mars',";
$sql .= " ROUND(SUM(IF(MONTH(lp.date_paiement)=4,lp.montant,0)),2) AS 'Avril',";
$sql .= " ROUND(SUM(IF(MONTH(lp.date_paiement)=5,lp.montant,0)),2) AS 'Mai',";
$sql .= " ROUND(SUM(IF(MONTH(lp.date_paiement)=6,lp.montant,0)),2) AS 'Juin',";
$sql .= " ROUND(SUM(IF(MONTH(lp.date_paiement)=7,lp.montant,0)),2) AS 'Juillet',";
$sql .= " ROUND(SUM(IF(MONTH(lp.date_paiement)=8,lp.montant,0)),2) AS 'Aout',";
$sql .= " ROUND(SUM(IF(MONTH(lp.date_paiement)=9,lp.montant,0)),2) AS 'Septembre',";
$sql .= " ROUND(SUM(IF(MONTH(lp.date_paiement)=10,lp.montant,0)),2) AS 'Octobre',";
$sql .= " ROUND(SUM(IF(MONTH(lp.date_paiement)=11,lp.montant,0)),2) AS 'Novembre',";
$sql .= " ROUND(SUM(IF(MONTH(lp.date_paiement)=12,lp.montant,0)),2) AS 'Decembre',";
$sql .= " ROUND(SUM(lp.montant),2) as 'Total'";
$sql .= " FROM " . MAIN_DB_PREFIX . "immo_paie as lp";
$sql .= " , " . MAIN_DB_PREFIX . "immo_local as ll";
$sql .= " WHERE lp.date_paiement >= '" . $db->idate(dol_get_first_day($y, 1, false)) . "'";
$sql .= " AND lp.date_paiement <= '" . $db->idate(dol_get_last_day($y, 12, false)) . "'";
$sql .= " AND lp.local_id = ll.rowid ";
if ($user->id != 1) {
$sql .= " AND ll.proprietaire_id=" . $user->id;
}
$resql = $db->query($sql);
if ($resql) {
$i = 0;
$num = $db->num_rows($resql);
while ($i < $num) {
$row = $db->fetch_row($resql);
print '<tr><td>' . $row[0] . '</td>';
print '<td align="right">' . $row[1] . '</td>';
print '<td align="right">' . $row[2] . '</td>';
print '<td align="right">' . $row[3] . '</td>';
} else {
$j = 0;
$numlt = 0;
}
while ($j < $numlt) {
if (empty($_GET["mode"]) || $_GET["mode"] != 'sconly') {
print "<br>";
$tva = new Tva($db);
print_fiche_titre($langs->transcountry($j == 1 ? "LT1Payments" : "LT2Payments", $mysoc->country_code) . ($year ? ' (' . $langs->trans("Year") . ' ' . $year . ')' : ''), '', '');
$sql = "SELECT pv.rowid, pv.amount, pv.label, pv.datev as dm, pv.datep as dp";
$sql .= " FROM " . MAIN_DB_PREFIX . "localtax as pv";
$sql .= " WHERE pv.entity = " . $conf->entity . " AND localtaxtype = " . $j;
if ($year > 0) {
// Si period renseignee on l'utilise comme critere de date, sinon on prend date echeance,
// ceci afin d'etre compatible avec les cas ou la periode n'etait pas obligatoire
$sql .= " AND pv.datev between '" . $db->idate(dol_get_first_day($year, 1, false)) . "' AND '" . $db->idate(dol_get_last_day($year, 12, false)) . "'";
}
if (preg_match('/^pv/', $sortfield)) {
$sql .= $db->order($sortfield, $sortorder);
}
$result = $db->query($sql);
if ($result) {
$num = $db->num_rows($result);
$i = 0;
$total = 0;
print '<table class="noborder" width="100%">';
print '<tr class="liste_titre">';
print_liste_field_titre($langs->trans("PeriodEndDate"), $_SERVER["PHP_SELF"], "pv.datev", "", $param, 'width="120"', $sortfield, $sortorder);
print_liste_field_titre($langs->trans("Label"), $_SERVER["PHP_SELF"], "pv.label", "", $param, '', $sortfield, $sortorder);
print_liste_field_titre($langs->trans("ExpectedToPay"), $_SERVER["PHP_SELF"], "pv.amount", "", $param, 'align="right"', $sortfield, $sortorder);
print_liste_field_titre($langs->trans("RefPayment"), $_SERVER["PHP_SELF"], "pv.rowid", "", $param, '', $sortfield, $sortorder);
function activitytrim($product_type)
{
global $conf, $langs, $db;
// We display the last 3 years
$yearofbegindate = date('Y', dol_time_plus_duree(time(), -3, "y"));
// breakdown by quarter
$sql = "SELECT DATE_FORMAT(p.datep,'%Y') as annee, DATE_FORMAT(p.datep,'%m') as mois, SUM(fd.total_ht) as Mnttot";
$sql .= " FROM " . MAIN_DB_PREFIX . "societe as s," . MAIN_DB_PREFIX . "facture as f, " . MAIN_DB_PREFIX . "facturedet as fd";
$sql .= " , " . MAIN_DB_PREFIX . "paiement as p," . MAIN_DB_PREFIX . "paiement_facture as pf";
$sql .= " WHERE f.fk_soc = s.rowid";
$sql .= " AND f.rowid = fd.fk_facture";
$sql .= " AND pf.fk_facture = f.rowid";
$sql .= " AND pf.fk_paiement= p.rowid";
$sql .= " AND fd.product_type=" . $product_type;
$sql .= " AND s.entity = " . $conf->entity;
$sql .= " AND p.datep >= '" . $db->idate(dol_get_first_day($yearofbegindate), 1) . "'";
$sql .= " GROUP BY annee, mois ";
$sql .= " ORDER BY annee, mois ";
$result = $db->query($sql);
if ($result) {
$tmpyear = $beginyear;
$trim1 = 0;
$trim2 = 0;
$trim3 = 0;
$trim4 = 0;
$lgn = 0;
$num = $db->num_rows($result);
if ($num > 0) {
print '<br>';
print '<table class="noborder" width="75%">';
if ($product_type == 0) {
print '<tr class="liste_titre"><td align=left>' . $langs->trans("ProductSellByQuarterHT") . '</td>';
} else {
print '<tr class="liste_titre"><td align=left>' . $langs->trans("ServiceSellByQuarterHT") . '</td>';
}
print '<td align=right>' . $langs->trans("Quarter1") . '</td>';
print '<td align=right>' . $langs->trans("Quarter2") . '</td>';
print '<td align=right>' . $langs->trans("Quarter3") . '</td>';
print '<td align=right>' . $langs->trans("Quarter4") . '</td>';
print '<td align=right>' . $langs->trans("Total") . '</td>';
print '</tr>';
}
$i = 0;
while ($i < $num) {
$objp = $db->fetch_object($result);
if ($tmpyear != $objp->annee) {
if ($trim1 + $trim2 + $trim3 + $trim4 > 0) {
print '<tr ><td align=left>' . $tmpyear . '</td>';
print '<td align=right>' . price($trim1) . '</td>';
print '<td align=right>' . price($trim2) . '</td>';
print '<td align=right>' . price($trim3) . '</td>';
print '<td align=right>' . price($trim4) . '</td>';
print '<td align=right>' . price($trim1 + $trim2 + $trim3 + $trim4) . '</td>';
print '</tr>';
$lgn++;
}
// We go to the following year
$tmpyear = $objp->annee;
$trim1 = 0;
$trim2 = 0;
$trim3 = 0;
$trim4 = 0;
}
if ($objp->mois == "01" || $objp->mois == "02" || $objp->mois == "03") {
$trim1 += $objp->Mnttot;
}
if ($objp->mois == "04" || $objp->mois == "05" || $objp->mois == "06") {
$trim2 += $objp->Mnttot;
}
if ($objp->mois == "07" || $objp->mois == "08" || $objp->mois == "09") {
$trim3 += $objp->Mnttot;
}
if ($objp->mois == "10" || $objp->mois == "11" || $objp->mois == "12") {
$trim4 += $objp->Mnttot;
}
$i++;
}
if ($trim1 + $trim2 + $trim3 + $trim4 > 0) {
print '<tr ><td align=left>' . $tmpyear . '</td>';
print '<td align=right>' . price($trim1) . '</td>';
print '<td align=right>' . price($trim2) . '</td>';
print '<td align=right>' . price($trim3) . '</td>';
print '<td align=right>' . price($trim4) . '</td>';
print '<td align=right>' . price($trim1 + $trim2 + $trim3 + $trim4) . '</td>';
print '</tr>';
}
if ($num > 0) {
print '</table>';
}
}
}
/**
* \brief Gets VAT to collect for the given year (and given quarter or month)
* The function gets the VAT in split results, as the VAT declaration asks
* to report the amounts for different VAT rates as different lines.
* This function also accounts recurrent invoices
* \param db Database handler object
* \param y Year
* \param q Quarter
* \param date_start Start date
* \param date_end End date
* \param modetax 0 or 1 (option vat on debit)
* \param direction 'sell' (customer invoice) or 'buy' (supplier invoices)
* \param m Month
* \return array List of quarters with vat
*/
function vat_by_date($db, $y, $q, $date_start, $date_end, $modetax, $direction, $m=0)
{
global $conf;
$list=array();
if ($direction == 'sell')
{
$invoicetable='facture';
$invoicedettable='facturedet';
$fk_facture='fk_facture';
$fk_facture2='fk_facture';
$fk_payment='fk_paiement';
$total_tva='total_tva';
$paymenttable='paiement';
$paymentfacturetable='paiement_facture';
}
if ($direction == 'buy')
{
$invoicetable='facture_fourn';
$invoicedettable='facture_fourn_det';
$fk_facture='fk_facture_fourn';
$fk_facture2='fk_facturefourn';
$fk_payment='fk_paiementfourn';
$total_tva='tva';
$paymenttable='paiementfourn';
$paymentfacturetable='paiementfourn_facturefourn';
}
// CAS DES BIENS
// Define sql request
$sql='';
if ($modetax == 1) // Option vat on delivery for goods (payment) and debit invoice for services
{
if ($conf->global->MAIN_MODULE_ACCOUNTING)
{
// \todo a ce jour on se sait pas la compter car le montant tva d'un payment
// n'est pas stocke dans la table des payments.
// Seul le module compta expert peut resoudre ce probleme.
// (Il faut quand un payment a lieu, stocker en plus du montant du paiement le
// detail part tva et part ht).
$sql='TODO';
}
if ($conf->global->MAIN_MODULE_COMPTABILITE)
{
// Count on delivery date (use invoice date as delivery is unknown)
$sql = "SELECT d.rowid, d.product_type as dtype, d.".$fk_facture." as facid, d.tva_tx as rate, d.total_ht as total_ht, d.total_ttc as total_ttc, d.".$total_tva." as total_vat, d.description as descr,";
$sql.= " d.date_start as date_start, d.date_end as date_end,";
$sql.= " f.facnumber as facnum, f.type, f.total_ttc as ftotal_ttc,";
$sql.= " p.rowid as pid, p.ref as pref, p.fk_product_type as ptype,";
$sql.= " 0 as payment_id, 0 as payment_amount";
$sql.= " FROM ".MAIN_DB_PREFIX.$invoicetable." as f,";
$sql.= " ".MAIN_DB_PREFIX.$invoicedettable." as d" ;
$sql.= " LEFT JOIN ".MAIN_DB_PREFIX."product as p on d.fk_product = p.rowid";
$sql.= " WHERE f.entity = " . $conf->entity;
$sql.= " AND f.fk_statut in (1,2)"; // Validated or paid (partially or completely)
$sql.= " AND (f.type = 0"; // Standard
$sql.= " OR f.type = 1"; // Replacement
$sql.= " OR f.type = 2)"; // Credit note
//$sql.= " OR f.type = 3"; // We do not include deposit
$sql.= " AND f.rowid = d.".$fk_facture;
if ($y && $m)
{
$sql.= " AND f.datef >= '".$db->idate(dol_get_first_day($y,$m,false))."'";
$sql.= " AND f.datef <= '".$db->idate(dol_get_last_day($y,$m,false))."'";
}
else if ($y)
{
$sql.= " AND f.datef >= '".$db->idate(dol_get_first_day($y,1,false))."'";
$sql.= " AND f.datef <= '".$db->idate(dol_get_last_day($y,12,false))."'";
}
if ($q) $sql.= " AND (date_format(f.datef,'%m') > ".(($q-1)*3)." AND date_format(f.datef,'%m') <= ".($q*3).")";
if ($date_start && $date_end) $sql.= " AND f.datef >= '".$db->idate($date_start)."' AND f.datef <= '".$db->idate($date_end)."'";
$sql.= " AND (d.product_type = 0"; // Limit to products
$sql.= " AND d.date_start is null AND d.date_end IS NULL)"; // enhance detection of service
$sql.= " ORDER BY d.rowid, d.".$fk_facture;
}
}
else // Option vat on delivery for goods (payments) and payments for services
{
if ($conf->global->MAIN_MODULE_ACCOUNTING)
{
// \todo a ce jour on se sait pas la compter car le montant tva d'un payment
// n'est pas stocke dans la table des payments.
// Seul le module compta expert peut resoudre ce probleme.
// (Il faut quand un payment a lieu, stocker en plus du montant du paiement le
// detail part tva et part ht).
$sql='TODO';
}
if ($conf->global->MAIN_MODULE_COMPTABILITE)
{
// Count on delivery date (use invoice date as delivery is unknown)
$sql = "SELECT d.rowid, d.product_type as dtype, d.".$fk_facture." as facid, d.tva_tx as rate, d.total_ht as total_ht, d.total_ttc as total_ttc, d.".$total_tva." as total_vat, d.description as descr,";
$sql.= " d.date_start as date_start, d.date_end as date_end,";
$sql.= " f.facnumber as facnum, f.type, f.total_ttc as ftotal_ttc,";
$sql.= " p.rowid as pid, p.ref as pref, p.fk_product_type as ptype,";
$sql.= " 0 as payment_id, 0 as payment_amount";
// $sql.= " pf.".$fk_payment." as payment_id, pf.amount as payment_amount";
$sql.= " FROM ".MAIN_DB_PREFIX.$invoicetable." as f,";
// $sql.= " ".MAIN_DB_PREFIX.$paymentfacturetable." as pf,";
// $sql.= " ".MAIN_DB_PREFIX.$paymenttable." as pa,";
$sql.= " ".MAIN_DB_PREFIX.$invoicedettable." as d" ;
$sql.= " LEFT JOIN ".MAIN_DB_PREFIX."product as p on d.fk_product = p.rowid";
$sql.= " WHERE f.entity = " . $conf->entity;
$sql.= " AND f.fk_statut in (1,2)"; // Validated or paid (partially or completely)
$sql.= " AND (f.type = 0"; // Standard
$sql.= " OR f.type = 1"; // Replacement
$sql.= " OR f.type = 2)"; // Credit note
//$sql.= " OR f.type = 3"; // We do not include deposit
$sql.= " AND f.rowid = d.".$fk_facture;
// $sql.= " AND pf.".$fk_facture2." = f.rowid";
// $sql.= " AND pa.rowid = pf.".$fk_payment;
// $sql.= " AND pa.datep >= '".$y."0101000000' AND pa.datep <= '".$y."1231235959'";
// $sql.= " AND (date_format(pa.datep,'%m') > ".(($q-1)*3)." AND date_format(pa.datep,'%m') <= ".($q*3).")";
if ($y && $m)
{
$sql.= " AND f.datef >= '".$db->idate(dol_get_first_day($y,$m,false))."'";
$sql.= " AND f.datef <= '".$db->idate(dol_get_last_day($y,$m,false))."'";
}
else if ($y)
{
$sql.= " AND f.datef >= '".$db->idate(dol_get_first_day($y,1,false))."'";
$sql.= " AND f.datef <= '".$db->idate(dol_get_last_day($y,12,false))."'";
}
if ($q) $sql.= " AND (date_format(f.datef,'%m') > ".(($q-1)*3)." AND date_format(f.datef,'%m') <= ".($q*3).")";
if ($date_start && $date_end) $sql.= " AND f.datef >= '".$db->idate($date_start)."' AND f.datef <= '".$db->idate($date_end)."'";
$sql.= " AND (d.product_type = 0"; // Limit to products
$sql.= " AND d.date_start is null AND d.date_end IS NULL)"; // enhance detection of service
$sql.= " ORDER BY d.rowid, d.".$fk_facture;
//print $sql;
}
}
//print $sql.'<br>';
if (! $sql) return -1;
if ($sql == 'TODO') return -2;
if ($sql != 'TODO')
{
dol_syslog("Tax.lib.php::vat_by_date sql=".$sql);
$resql = $db->query($sql);
if ($resql)
{
$rate = -1;
$oldrowid='';
while($assoc = $db->fetch_array($resql))
{
if (! isset($list[$assoc['rate']]['totalht'])) $list[$assoc['rate']]['totalht']=0;
if (! isset($list[$assoc['rate']]['vat'])) $list[$assoc['rate']]['vat']=0;
if ($assoc['rowid'] != $oldrowid) // Si rupture sur d.rowid
{
$oldrowid=$assoc['rowid'];
$list[$assoc['rate']]['totalht'] += $assoc['total_ht'];
$list[$assoc['rate']]['vat'] += $assoc['total_vat'];
}
$list[$assoc['rate']]['dtotal_ttc'][] = $assoc['total_ttc'];
$list[$assoc['rate']]['dtype'][] = $assoc['dtype'];
$list[$assoc['rate']]['ddate_start'][] = $db->jdate($assoc['date_start']);
$list[$assoc['rate']]['ddate_end'][] = $db->jdate($assoc['date_end']);
$list[$assoc['rate']]['facid'][] = $assoc['facid'];
$list[$assoc['rate']]['facnum'][] = $assoc['facnum'];
$list[$assoc['rate']]['type'][] = $assoc['type'];
$list[$assoc['rate']]['ftotal_ttc'][] = $assoc['ftotal_ttc'];
$list[$assoc['rate']]['descr'][] = $assoc['descr'];
$list[$assoc['rate']]['totalht_list'][] = $assoc['total_ht'];
$list[$assoc['rate']]['vat_list'][] = $assoc['total_vat'];
$list[$assoc['rate']]['pid'][] = $assoc['pid'];
$list[$assoc['rate']]['pref'][] = $assoc['pref'];
$list[$assoc['rate']]['ptype'][] = $assoc['ptype'];
$list[$assoc['rate']]['payment_id'][] = $assoc['payment_id'];
$list[$assoc['rate']]['payment_amount'][] = $assoc['payment_amount'];
$rate = $assoc['rate'];
}
}
else
{
dol_print_error($db);
return -3;
}
}
// CAS DES SERVICES
// Define sql request
$sql='';
if ($modetax == 1) // Option vat on delivery for goods (payment) and debit invoice for services
{
if ($conf->global->MAIN_MODULE_ACCOUNTING)
{
// Count on invoice date
// \todo a ce jour on se sait pas la compter car le montant tva d'un payment
// n'est pas stocke dans la table des payments.
// Seul le module compta expert peut resoudre ce probleme.
// (Il faut quand un payment a lieu, stocker en plus du montant du paiement le
// detail part tva et part ht).
$sql='TODO';
}
if ($conf->global->MAIN_MODULE_COMPTABILITE)
{
// Count on invoice date
$sql = "SELECT d.rowid, d.product_type as dtype, d.".$fk_facture." as facid, d.tva_tx as rate, d.total_ht as total_ht, d.total_ttc as total_ttc, d.".$total_tva." as total_vat, d.description as descr,";
$sql.= " d.date_start as date_start, d.date_end as date_end,";
$sql.= " f.facnumber as facnum, f.type, f.total_ttc as ftotal_ttc,";
$sql.= " p.rowid as pid, p.ref as pref, p.fk_product_type as ptype,";
$sql.= " 0 as payment_id, 0 as payment_amount";
$sql.= " FROM ".MAIN_DB_PREFIX.$invoicetable." as f,";
$sql.= " ".MAIN_DB_PREFIX.$invoicedettable." as d" ;
$sql.= " LEFT JOIN ".MAIN_DB_PREFIX."product as p on d.fk_product = p.rowid";
$sql.= " WHERE f.entity = " . $conf->entity;
$sql.= " AND f.fk_statut in (1,2)"; // Validated or paid (partially or completely)
$sql.= " AND (f.type = 0"; // Standard
$sql.= " OR f.type = 1"; // Replacement
$sql.= " OR f.type = 2)"; // Credit note
//$sql.= " OR f.type = 3"; // We do not include deposit
$sql.= " AND f.rowid = d.".$fk_facture;
if ($y && $m)
{
$sql.= " AND f.datef >= '".$db->idate(dol_get_first_day($y,$m,false))."'";
$sql.= " AND f.datef <= '".$db->idate(dol_get_last_day($y,$m,false))."'";
}
else if ($y)
{
$sql.= " AND f.datef >= '".$db->idate(dol_get_first_day($y,1,false))."'";
$sql.= " AND f.datef <= '".$db->idate(dol_get_last_day($y,12,false))."'";
}
if ($q) $sql.= " AND (date_format(f.datef,'%m') > ".(($q-1)*3)." AND date_format(f.datef,'%m') <= ".($q*3).")";
if ($date_start && $date_end) $sql.= " AND f.datef >= '".$db->idate($date_start)."' AND f.datef <= '".$db->idate($date_end)."'";
$sql.= " AND (d.product_type = 1"; // Limit to services
$sql.= " OR d.date_start is NOT null OR d.date_end IS NOT NULL)"; // enhance detection of service
$sql.= " ORDER BY d.rowid, d.".$fk_facture;
}
}
else // Option vat on delivery for goods (payments) and payments for services
{
if ($conf->global->MAIN_MODULE_ACCOUNTING)
{
// Count on payments date
// \todo a ce jour on se sait pas la compter car le montant tva d'un payment
// n'est pas stocke dans la table des payments.
// Seul le module compta expert peut resoudre ce probleme.
// (Il faut quand un paiement a lieu, stocker en plus du montant du paiement le
// detail part tva et part ht).
$sql='TODO';
}
if ($conf->global->MAIN_MODULE_COMPTABILITE)
{
// Count on payments date
$sql = "SELECT d.rowid, d.product_type as dtype, d.".$fk_facture." as facid, d.tva_tx as rate, d.total_ht as total_ht, d.total_ttc as total_ttc, d.".$total_tva." as total_vat, d.description as descr,";
$sql.= " d.date_start as date_start, d.date_end as date_end,";
$sql.= " f.facnumber as facnum, f.type, f.total_ttc as ftotal_ttc,";
$sql.= " p.rowid as pid, p.ref as pref, p.fk_product_type as ptype,";
$sql.= " pf.".$fk_payment." as payment_id, pf.amount as payment_amount";
$sql.= " FROM ".MAIN_DB_PREFIX.$invoicetable." as f,";
$sql.= " ".MAIN_DB_PREFIX.$paymentfacturetable." as pf,";
$sql.= " ".MAIN_DB_PREFIX.$paymenttable." as pa,";
$sql.= " ".MAIN_DB_PREFIX.$invoicedettable." as d";
$sql.= " LEFT JOIN ".MAIN_DB_PREFIX."product as p on d.fk_product = p.rowid";
$sql.= " WHERE f.entity = " . $conf->entity;
$sql.= " AND f.fk_statut in (1,2)"; // Paid (partially or completely)
$sql.= " AND (f.type = 0"; // Standard
$sql.= " OR f.type = 1"; // Replacement
$sql.= " OR f.type = 2)"; // Credit note
//$sql.= " OR f.type = 3"; // We do not include deposit
$sql.= " AND f.rowid = d.".$fk_facture;;
$sql.= " AND pf.".$fk_facture2." = f.rowid";
$sql.= " AND pa.rowid = pf.".$fk_payment;
if ($y && $m)
{
$sql.= " AND pa.datep >= '".$db->idate(dol_get_first_day($y,$m,false))."'";
$sql.= " AND pa.datep <= '".$db->idate(dol_get_last_day($y,$m,false))."'";
}
else if ($y)
{
$sql.= " AND pa.datep >= '".$db->idate(dol_get_first_day($y,1,false))."'";
$sql.= " AND pa.datep <= '".$db->idate(dol_get_last_day($y,12,false))."'";
}
if ($q) $sql.= " AND (date_format(pa.datep,'%m') > ".(($q-1)*3)." AND date_format(pa.datep,'%m') <= ".($q*3).")";
if ($date_start && $date_end) $sql.= " AND pa.datep >= ".$db->idate($date_start)." AND pa.datep <= ".$db->idate($date_end);
$sql.= " AND (d.product_type = 1"; // Limit to services
$sql.= " OR d.date_start is NOT null OR d.date_end IS NOT NULL)"; // enhance detection of service
$sql.= " ORDER BY d.rowid, d.".$fk_facture.", pf.rowid";
}
}
if (! $sql)
{
dol_syslog("Tax.lib.php::vat_by_date no accountancy module enabled".$sql,LOG_ERR);
return -1; // -1 = Not accountancy module enabled
}
if ($sql == 'TODO') return -2; // -2 = Feature not yet available
if ($sql != 'TODO')
{
dol_syslog("Tax.lib.php::vat_by_date sql=".$sql);
$resql = $db->query($sql);
if ($resql)
{
$rate = -1;
$oldrowid='';
while($assoc = $db->fetch_array($resql))
{
if (! isset($list[$assoc['rate']]['totalht'])) $list[$assoc['rate']]['totalht']=0;
if (! isset($list[$assoc['rate']]['vat'])) $list[$assoc['rate']]['vat']=0;
if ($assoc['rowid'] != $oldrowid) // Si rupture sur d.rowid
{
$oldrowid=$assoc['rowid'];
$list[$assoc['rate']]['totalht'] += $assoc['total_ht'];
$list[$assoc['rate']]['vat'] += $assoc['total_vat'];
}
$list[$assoc['rate']]['dtotal_ttc'][] = $assoc['total_ttc'];
$list[$assoc['rate']]['dtype'][] = $assoc['dtype'];
$list[$assoc['rate']]['ddate_start'][] = $db->jdate($assoc['date_start']);
$list[$assoc['rate']]['ddate_end'][] = $db->jdate($assoc['date_end']);
$list[$assoc['rate']]['facid'][] = $assoc['facid'];
$list[$assoc['rate']]['facnum'][] = $assoc['facnum'];
$list[$assoc['rate']]['type'][] = $assoc['type'];
$list[$assoc['rate']]['ftotal_ttc'][] = $assoc['ftotal_ttc'];
$list[$assoc['rate']]['descr'][] = $assoc['descr'];
$list[$assoc['rate']]['totalht_list'][] = $assoc['total_ht'];
$list[$assoc['rate']]['vat_list'][] = $assoc['total_vat'];
$list[$assoc['rate']]['pid'][] = $assoc['pid'];
$list[$assoc['rate']]['pref'][] = $assoc['pref'];
$list[$assoc['rate']]['ptype'][] = $assoc['ptype'];
$list[$assoc['rate']]['payment_id'][] = $assoc['payment_id'];
$list[$assoc['rate']]['payment_amount'][] = $assoc['payment_amount'];
$rate = $assoc['rate'];
}
}
else
{
dol_print_error($db);
return -3;
}
}
return $list;
}
/**
* Build the where part
*
* @return string
*/
public function buildWhere()
{
$sqlwhere_str = '';
$sqlwhere = array();
$sqlwhere[] = ' t.entity IN (' . getEntity('project') . ')';
if (! empty($this->userid))
$sqlwhere[] = ' t.fk_user_resp=' . $this->userid;
if (! empty($this->socid))
$sqlwhere[] = ' t.fk_soc=' . $this->socid;
if (! empty($this->year) && empty($this->yearmonth))
$sqlwhere[] = " date_format(t.datec,'%Y')='" . $this->year . "'";
if (! empty($this->yearmonth))
$sqlwhere[] = " t.datec BETWEEN '" . $this->db->idate(dol_get_first_day($this->yearmonth)) . "' AND '" . $this->db->idate(dol_get_last_day($this->yearmonth)) . "'";
if (! empty($this->status))
$sqlwhere[] = " t.fk_opp_status IN (" . $this->status . ")";
if (count($sqlwhere) > 0) {
$sqlwhere_str = ' WHERE ' . implode(' AND ', $sqlwhere);
}
return $sqlwhere_str;
}
/**
* Fonction generant la rapport sur le disque
*
* @param string $_dir repertoire
* @param int $month mois du rapport
* @param int $year annee du rapport
* @param string $outputlangs Lang output object
* @return int <0 if KO, >0 if OK
*/
function write_file($_dir, $month, $year, $outputlangs)
{
include_once DOL_DOCUMENT_ROOT . '/core/lib/date.lib.php';
global $user, $langs, $conf;
$socid = 0;
if ($user->societe_id) {
$socid = $user->societe_id;
}
if (!is_object($outputlangs)) {
$outputlangs = $langs;
}
// For backward compatibility with FPDF, force output charset to ISO, because FPDF expect text to be encoded in ISO
if (!empty($conf->global->MAIN_USE_FPDF)) {
$outputlangs->charset_output = 'ISO-8859-1';
}
$this->month = $month;
$this->year = $year;
$dir = $_dir . '/' . $year;
if (!is_dir($dir)) {
$result = dol_mkdir($dir);
if ($result < 0) {
$this->error = $langs->transnoentities("ErrorCanNotCreateDir", $dir);
return -1;
}
}
$month = sprintf("%02d", $month);
$year = sprintf("%04d", $year);
$file = $dir . "/payments-" . $year . "-" . $month . ".pdf";
$pdf = pdf_getInstance($this->format);
$default_font_size = pdf_getPDFFontSize($outputlangs);
// Must be after pdf_getInstance
if (class_exists('TCPDF')) {
$pdf->setPrintHeader(false);
$pdf->setPrintFooter(false);
}
$pdf->SetFont(pdf_getPDFFont($outputlangs));
$num = 0;
$lines = array();
$sql = "SELECT p.datep as dp, f.facnumber";
//$sql .= ", c.libelle as paiement_type, p.num_paiement";
$sql .= ", c.code as paiement_code, p.num_paiement";
$sql .= ", p.amount as paiement_amount, f.total_ttc as facture_amount ";
$sql .= ", pf.amount as pf_amount ";
$sql .= ", p.rowid as prowid";
$sql .= " FROM " . MAIN_DB_PREFIX . "paiement as p, " . MAIN_DB_PREFIX . "facture as f,";
$sql .= " " . MAIN_DB_PREFIX . "c_paiement as c, " . MAIN_DB_PREFIX . "paiement_facture as pf,";
$sql .= " " . MAIN_DB_PREFIX . "societe as s";
if (!$user->rights->societe->client->voir && !$socid) {
$sql .= ", " . MAIN_DB_PREFIX . "societe_commerciaux as sc";
}
$sql .= " WHERE f.fk_soc = s.rowid AND pf.fk_facture = f.rowid AND pf.fk_paiement = p.rowid";
$sql .= " AND f.entity = " . $conf->entity;
$sql .= " AND p.fk_paiement = c.id ";
$sql .= " AND p.datep BETWEEN '" . $this->db->idate(dol_get_first_day($year, $month)) . "' AND '" . $this->db->idate(dol_get_last_day($year, $month)) . "'";
if (!$user->rights->societe->client->voir && !$socid) {
$sql .= " AND s.rowid = sc.fk_soc AND sc.fk_user = "******" AND s.rowid = " . $socid;
}
$sql .= " ORDER BY p.datep ASC, pf.fk_paiement ASC";
dol_syslog(get_class($this) . "::write_file sql=" . $sql);
$result = $this->db->query($sql);
if ($result) {
$num = $this->db->num_rows($result);
$i = 0;
$var = True;
while ($i < $num) {
$objp = $this->db->fetch_object($result);
$var = !$var;
$lines[$i][0] = $objp->facnumber;
$lines[$i][1] = dol_print_date($this->db->jdate($objp->dp), "day", false, $outputlangs, true);
$lines[$i][2] = $langs->transnoentities("PaymentTypeShort" . $objp->paiement_code);
$lines[$i][3] = $objp->num_paiement;
$lines[$i][4] = price($objp->paiement_amount);
$lines[$i][5] = price($objp->facture_amount);
$lines[$i][6] = price($objp->pf_amount);
$lines[$i][7] = $objp->prowid;
$i++;
}
} else {
dol_print_error($this->db);
}
$pages = intval($num / $this->line_per_page);
if ($lines % $this->line_per_page > 0) {
$pages++;
}
if ($pages == 0) {
// force to build at least one page if report has no line
$pages = 1;
}
$pdf->Open();
$pagenb = 0;
$pdf->SetDrawColor(128, 128, 128);
$pdf->SetTitle($outputlangs->transnoentities("Payments"));
$pdf->SetSubject($outputlangs->transnoentities("Payments"));
$pdf->SetCreator("Dolibarr " . DOL_VERSION);
$pdf->SetAuthor($outputlangs->convToOutputCharset($user->getFullName($outputlangs)));
//$pdf->SetKeyWords();
if (!empty($conf->global->MAIN_DISABLE_PDF_COMPRESSION)) {
$pdf->SetCompression(false);
}
$pdf->SetMargins($this->marge_gauche, $this->marge_haute, $this->marge_droite);
// Left, Top, Right
$pdf->SetAutoPageBreak(1, 0);
// New page
$pdf->AddPage();
$pagenb++;
$this->_pagehead($pdf, $pages, 1, $outputlangs);
$pdf->SetFont('', '', 9);
$pdf->MultiCell(0, 3, '');
// Set interline to 3
$pdf->SetTextColor(0, 0, 0);
$this->Body($pdf, 1, $lines, $outputlangs);
if (method_exists($pdf, 'AliasNbPages')) {
$pdf->AliasNbPages();
}
$pdf->Close();
$pdf->Output($file, 'F');
if (!empty($conf->global->MAIN_UMASK)) {
@chmod($file, octdec($conf->global->MAIN_UMASK));
}
return 1;
}
print '<br>';
/* Affichage de la liste des projets du mois */
print '<table class="noborder" width="100%">';
print '<tr class="liste_titre">';
print '<td>' . $langs->trans("ActivityOnProjectThisMonth") . ': ' . dol_print_date($now, "%B %Y") . '</td>';
print '<td align="right">' . $langs->trans("Time") . '</td>';
print "</tr>\n";
$sql = "SELECT p.rowid, p.ref, p.title, p.public, SUM(tt.task_duration) as nb";
$sql .= " FROM " . MAIN_DB_PREFIX . "projet as p";
$sql .= ", " . MAIN_DB_PREFIX . "projet_task as t";
$sql .= ", " . MAIN_DB_PREFIX . "projet_task_time as tt";
$sql .= " WHERE t.fk_projet = p.rowid";
$sql .= " AND p.entity = " . $conf->entity;
$sql .= " AND tt.fk_task = t.rowid";
$sql .= " AND tt.fk_user = "******" AND task_date BETWEEN '" . $db->idate(dol_get_first_day($year, $month)) . "' AND '" . $db->idate(dol_get_last_day($year, $month)) . "'";
$sql .= " AND p.rowid in (" . $projectsListId . ")";
$sql .= " GROUP BY p.rowid, p.ref, p.title, p.public";
$resql = $db->query($sql);
if ($resql) {
$total = 0;
$var = false;
while ($row = $db->fetch_object($resql)) {
print "<tr " . $bc[$var] . ">";
print '<td>';
$projectstatic->id = $row->rowid;
$projectstatic->ref = $row->ref;
$projectstatic->title = $row->title;
$projectstatic->public = $row->public;
print $projectstatic->getNomUrl(1, '', 1);
print '</td>';
/**
* Export events from database into a cal file.
* @param format 'vcal', 'ical/ics', 'rss'
* @param type 'event' or 'journal'
* @param cachedelay Do not rebuild file if date older than cachedelay seconds
* @param filename Force filename
* @param filters Array of filters
* @return int <0 if error, nb of events in new file if ok
*/
function build_exportfile($format, $type, $cachedelay, $filename, $filters)
{
global $conf, $langs, $dolibarr_main_url_root, $mysoc;
require_once DOL_DOCUMENT_ROOT . "/lib/xcal.lib.php";
require_once DOL_DOCUMENT_ROOT . "/lib/date.lib.php";
dol_syslog("ActionComm::build_exportfile Build export file format=" . $format . ", type=" . $type . ", cachedelay=" . $cachedelay . ", filename=" . $filename . ", filters size=" . sizeof($filters), LOG_DEBUG);
// Check parameters
if (empty($format)) {
return -1;
}
// Clean parameters
if (!$filename) {
$extension = 'vcs';
if ($format == 'ical') {
$extension = 'ics';
}
$filename = $format . '.' . $extension;
}
// Create dir and define output file (definitive and temporary)
$result = create_exdir($conf->agenda->dir_temp);
$outputfile = $conf->agenda->dir_temp . '/' . $filename;
$result = 0;
$buildfile = true;
$login = '';
$logina = '';
$logind = '';
$logint = '';
$now = dol_now();
if ($cachedelay) {
$nowgmt = dol_now();
include_once DOL_DOCUMENT_ROOT . '/lib/files.lib.php';
if (dol_filemtime($outputfile) > $nowgmt - $cachedelay) {
dol_syslog("ActionComm::build_exportfile file " . $outputfile . " is not older than now - cachedelay (" . $nowgmt . " - " . $cachedelay . "). Build is canceled");
$buildfile = false;
}
}
if ($buildfile) {
// Build event array
$eventarray = array();
$sql = "SELECT a.id,";
$sql .= " a.datep,";
// Start
$sql .= " a.datep2,";
// End
$sql .= " a.durationp,";
$sql .= " a.datec, a.tms as datem,";
$sql .= " a.note, a.label, a.fk_action as type_id,";
$sql .= " a.fk_soc,";
$sql .= " a.fk_user_author, a.fk_user_mod,";
$sql .= " a.fk_user_action, a.fk_user_done,";
$sql .= " a.fk_contact, a.percent as percentage,";
$sql .= " a.fk_element, a.elementtype,";
$sql .= " a.priority, a.fulldayevent, a.location,";
$sql .= " u.firstname, u.name,";
$sql .= " s.nom as socname,";
$sql .= " c.id as type_id, c.code as type_code, c.libelle";
$sql .= " FROM (" . MAIN_DB_PREFIX . "c_actioncomm as c, " . MAIN_DB_PREFIX . "actioncomm as a)";
$sql .= " LEFT JOIN " . MAIN_DB_PREFIX . "user as u on u.rowid = a.fk_user_author";
$sql .= " LEFT JOIN " . MAIN_DB_PREFIX . "societe as s on s.rowid = a.fk_soc AND s.entity IN (0, " . $conf->entity . ")";
$sql .= " WHERE a.fk_action=c.id";
$sql .= " AND a.entity = " . $conf->entity;
foreach ($filters as $key => $value) {
if ($key == 'notolderthan') {
$sql .= " AND a.datep >= '" . $this->db->idate($now - $value * 24 * 60 * 60) . "'";
}
if ($key == 'year') {
$sql .= " AND a.datep BETWEEN '" . $this->db->idate(dol_get_first_day($value, 1)) . "' AND '" . $this->db->idate(dol_get_last_day($value, 12)) . "'";
}
if ($key == 'id') {
$sql .= " AND a.id=" . (is_numeric($value) ? $value : 0);
}
if ($key == 'idfrom') {
$sql .= " AND a.id >= " . (is_numeric($value) ? $value : 0);
}
if ($key == 'idto') {
$sql .= " AND a.id <= " . (is_numeric($value) ? $value : 0);
}
if ($key == 'login') {
$login = $value;
$userforfilter = new User($this->db);
$result = $userforfilter->fetch('', $value);
$sql .= " AND (";
$sql .= " a.fk_user_author = " . $userforfilter->id;
$sql .= " OR a.fk_user_action = " . $userforfilter->id;
$sql .= " OR a.fk_user_done = " . $userforfilter->id;
$sql .= ")";
}
if ($key == 'logina') {
$logina = $value;
$userforfilter = new User($this->db);
$result = $userforfilter->fetch('', $value);
$sql .= " AND a.fk_user_author = " . $userforfilter->id;
}
if ($key == 'logint') {
$logint = $value;
$userforfilter = new User($this->db);
$result = $userforfilter->fetch('', $value);
$sql .= " AND a.fk_user_action = " . $userforfilter->id;
}
if ($key == 'logind') {
$logind = $value;
$userforfilter = new User($this->db);
$result = $userforfilter->fetch('', $value);
$sql .= " AND a.fk_user_done = " . $userforfilter->id;
}
}
$sql .= " AND a.datep IS NOT NULL";
// To exclude corrupted events and avoid errors in lightning/sunbird import
$sql .= " ORDER by datep";
//print $sql;exit;
dol_syslog("ActionComm::build_exportfile select events sql=" . $sql);
$resql = $this->db->query($sql);
if ($resql) {
// Note: Output of sql request is encoded in $conf->file->character_set_client
while ($obj = $this->db->fetch_object($resql)) {
$qualified = true;
// 'eid','startdate','duration','enddate','title','summary','category','email','url','desc','author'
$event = array();
$event['uid'] = 'dolibarragenda-' . $this->db->database_name . '-' . $obj->id . "@" . $_SERVER["SERVER_NAME"];
$event['type'] = $type;
//$datestart=$obj->datea?$obj->datea:$obj->datep;
//$dateend=$obj->datea2?$obj->datea2:$obj->datep2;
//$duration=$obj->durationa?$obj->durationa:$obj->durationp;
$datestart = $this->db->jdate($obj->datep);
//print $datestart.'x'; exit;
$dateend = $this->db->jdate($obj->datep2);
$duration = $obj->durationp;
$event['summary'] = $langs->convToOutputCharset($obj->label . ($obj->socname ? " (" . $obj->socname . ")" : ""));
$event['desc'] = $langs->convToOutputCharset($obj->note);
$event['startdate'] = $datestart;
$event['duration'] = $duration;
// Not required with type 'journal'
$event['enddate'] = $dateend;
// Not required with type 'journal'
$event['author'] = $obj->firstname . ($obj->name ? " " . $obj->name : "");
$event['priority'] = $obj->priority;
$event['fulldayevent'] = $obj->fulldayevent;
$event['location'] = $langs->convToOutputCharset($obj->location);
$event['transparency'] = 'TRANSPARENT';
// OPAQUE (busy) or TRANSPARENT (not busy)
$event['category'] = $langs->convToOutputCharset($obj->libelle);
// libelle type action
$urlwithouturlroot = preg_replace('/' . preg_quote(DOL_URL_ROOT, '/') . '$/i', '', $dolibarr_main_url_root);
$url = $urlwithouturlroot . DOL_URL_ROOT . '/comm/action/fiche.php?id=' . $obj->id;
$event['url'] = $url;
$event['created'] = $this->db->jdate($obj->datec);
$event['modified'] = $this->db->jdate($obj->datem);
if ($qualified && $datestart) {
$eventarray[$datestart] = $event;
}
}
} else {
$this->error = $this->db->lasterror();
dol_syslog("ActionComm::build_exportfile " . $this->db->lasterror(), LOG_ERR);
return -1;
}
$langs->load("agenda");
// Define title and desc
$more = '';
if ($login) {
$more = $langs->transnoentities("User") . ' ' . $langs->convToOutputCharset($login);
}
if ($logina) {
$more = $langs->transnoentities("ActionsAskedBy") . ' ' . $langs->convToOutputCharset($logina);
}
if ($logint) {
$more = $langs->transnoentities("ActionsToDoBy") . ' ' . $langs->convToOutputCharset($logint);
}
if ($logind) {
$more = $langs->transnoentities("ActionsDoneBy") . ' ' . $langs->convToOutputCharset($logind);
}
if ($more) {
$title = $langs->convToOutputCharset('Dolibarr actions ' . $mysoc->name) . ' - ' . $more;
$desc = $more;
$desc .= $langs->convToOutputCharset(' (' . $mysoc->name . ' - built by Dolibarr)');
} else {
$title = $langs->convToOutputCharset('Dolibarr actions ' . $mysoc->name);
$desc = $langs->transnoentities('ListOfActions');
$desc .= $langs->convToOutputCharset(' (' . $mysoc->name . ' - built by Dolibarr)');
}
// Create temp file
$outputfiletmp = tempnam($conf->agenda->dir_temp, 'tmp');
// Temporary file (allow call of function by different threads
@chmod($outputfiletmp, octdec($conf->global->MAIN_UMASK));
// Write file
if ($format == 'vcal') {
$result = build_calfile($format, $title, $desc, $eventarray, $outputfiletmp);
}
if ($format == 'ical') {
$result = build_calfile($format, $title, $desc, $eventarray, $outputfiletmp);
}
if ($format == 'rss') {
$result = build_rssfile($format, $title, $desc, $eventarray, $outputfiletmp);
}
if ($result >= 0) {
if (rename($outputfiletmp, $outputfile)) {
$result = 1;
} else {
dol_syslog("ActionComm::build_exportfile failed to rename " . $outputfiletmp . " to " . $outputfile, LOG_ERR);
dol_delete_file($outputfiletmp, 0, 1);
$result = -1;
}
} else {
dol_syslog("ActionComm::build_exportfile build_xxxfile function fails to for format=" . $format . " outputfiletmp=" . $outputfile, LOG_ERR);
dol_delete_file($outputfiletmp, 0, 1);
$langs->load("errors");
$this->error = $langs->trans("ErrorFailToCreateFile", $outputfile);
}
}
return $result;
}
/**
* Return HTML table with list of projects and number of opened tasks
*
* @param DoliDB $db Database handler
* @param Form $form Object form
* @param int $socid Id thirdparty
* @param int $projectsListId Id of project I have permission on
* @param int $mytasks Limited to task I am contact to
* @param int $statut -1=No filter on statut, 0 or 1 = Filter on status
* @param array $listofoppstatus List of opportunity status
* @param array $hiddenfields List of info to not show ('projectlabel', 'declaredprogress', '...', )
* @return void
*/
function print_projecttasks_array($db, $form, $socid, $projectsListId, $mytasks = 0, $statut = -1, $listofoppstatus = array(), $hiddenfields = array())
{
global $langs, $conf, $user, $bc;
require_once DOL_DOCUMENT_ROOT . '/projet/class/project.class.php';
$projectstatic = new Project($db);
$thirdpartystatic = new Societe($db);
$sortfield = '';
$sortorder = '';
$project_year_filter = 0;
$title = $langs->trans("Projects");
if (strcmp($statut, '') && $statut >= 0) {
$title = $langs->trans("Projects") . ' ' . $langs->trans($projectstatic->statuts_long[$statut]);
}
$arrayidtypeofcontact = array();
print '<table class="noborder" width="100%">';
$sql .= " FROM " . MAIN_DB_PREFIX . "projet as p";
if ($mytasks) {
$sql .= ", " . MAIN_DB_PREFIX . "projet_task as t";
$sql .= ", " . MAIN_DB_PREFIX . "element_contact as ec";
$sql .= ", " . MAIN_DB_PREFIX . "c_type_contact as ctc";
} else {
$sql .= " LEFT JOIN " . MAIN_DB_PREFIX . "projet_task as t ON p.rowid = t.fk_projet";
}
$sql .= " WHERE p.entity = " . $conf->entity;
$sql .= " AND p.rowid IN (" . $projectsListId . ")";
if ($socid) {
$sql .= " AND (p.fk_soc IS NULL OR p.fk_soc = 0 OR p.fk_soc = " . $socid . ")";
}
if ($mytasks) {
$sql .= " AND p.rowid = t.fk_projet";
$sql .= " AND ec.element_id = t.rowid";
$sql .= " AND ec.fk_socpeople = " . $user->id;
$sql .= " AND ec.fk_c_type_contact = ctc.rowid";
// Replace the 2 lines with ec.fk_c_type_contact in $arrayidtypeofcontact
$sql .= " AND ctc.element = 'project_task'";
}
if ($statut >= 0) {
$sql .= " AND p.fk_statut = " . $statut;
}
if (!empty($conf->global->PROJECT_LIMIT_YEAR_RANGE)) {
$project_year_filter = GETPOST("project_year_filter");
//Check if empty or invalid year. Wildcard ignores the sql check
if ($project_year_filter != "*") {
if (empty($project_year_filter) || !ctype_digit($project_year_filter)) {
$project_year_filter = date("Y");
}
$sql .= " AND (p.dateo IS NULL OR p.dateo <= " . $db->idate(dol_get_last_day($project_year_filter, 12, false)) . ")";
$sql .= " AND (p.datee IS NULL OR p.datee >= " . $db->idate(dol_get_first_day($project_year_filter, 1, false)) . ")";
}
}
// Get id of project we must show tasks
$arrayidofprojects = array();
$sql1 = "SELECT p.rowid as projectid";
$sql1 .= $sql;
$resql = $db->query($sql1);
if ($resql) {
$i = 0;
$num = $db->num_rows($resql);
while ($i < $num) {
$objp = $db->fetch_object($resql);
$arrayidofprojects[$objp->projectid] = $objp->projectid;
$i++;
}
} else {
dol_print_error($db);
}
if (empty($arrayidofprojects)) {
$arrayidofprojects[0] = -1;
}
// Get list of project with calculation on tasks
$sql2 = "SELECT p.rowid as projectid, p.ref, p.title, p.fk_soc, s.nom as socname, p.fk_user_creat, p.public, p.fk_statut as status, p.fk_opp_status as opp_status, p.opp_amount,";
$sql2 .= " COUNT(t.rowid) as nb, SUM(t.planned_workload) as planned_workload, SUM(t.planned_workload * t.progress / 100) as declared_progess_workload";
$sql2 .= " FROM " . MAIN_DB_PREFIX . "projet as p";
$sql2 .= " LEFT JOIN " . MAIN_DB_PREFIX . "societe as s ON s.rowid = p.fk_soc";
$sql2 .= " LEFT JOIN " . MAIN_DB_PREFIX . "projet_task as t ON p.rowid = t.fk_projet";
$sql2 .= " WHERE p.rowid IN (" . join(',', $arrayidofprojects) . ")";
$sql2 .= " GROUP BY p.rowid, p.ref, p.title, p.fk_soc, p.fk_user_creat, p.public, p.fk_statut, p.fk_opp_status, p.opp_amount";
$sql2 .= " ORDER BY p.title, p.ref";
$var = true;
$resql = $db->query($sql2);
if ($resql) {
$total_task = 0;
$total_opp_amount = 0;
$ponderated_opp_amount = 0;
$num = $db->num_rows($resql);
$i = 0;
print '<tr class="liste_titre">';
print_liste_field_titre($title . ' <span class="badge">' . $num . '</span>', $_SERVER["PHP_SELF"], "", "", "", "", $sortfield, $sortorder);
print_liste_field_titre($langs->trans("ThirdParty"), $_SERVER["PHP_SELF"], "", "", "", "", $sortfield, $sortorder);
if (!empty($conf->global->PROJECT_USE_OPPORTUNITIES)) {
print_liste_field_titre($langs->trans("OpportunityAmount"), "", "", "", "", 'align="right"', $sortfield, $sortorder);
print_liste_field_titre($langs->trans("OpportunityStatus"), "", "", "", "", 'align="right"', $sortfield, $sortorder);
}
if (empty($conf->global->PROJECT_HIDE_TASKS)) {
print_liste_field_titre($langs->trans("Tasks"), "", "", "", "", 'align="right"', $sortfield, $sortorder);
if (!in_array('plannedworkload', $hiddenfields)) {
print_liste_field_titre($langs->trans("PlannedWorkload"), "", "", "", "", 'align="right"', $sortfield, $sortorder);
}
if (!in_array('declaredprogress', $hiddenfields)) {
print_liste_field_titre($langs->trans("ProgressDeclared"), "", "", "", "", 'align="right"', $sortfield, $sortorder);
}
}
print_liste_field_titre($langs->trans("Status"), "", "", "", "", 'align="right"', $sortfield, $sortorder);
print "</tr>\n";
while ($i < $num) {
$objp = $db->fetch_object($resql);
$projectstatic->id = $objp->projectid;
$projectstatic->user_author_id = $objp->fk_user_creat;
$projectstatic->public = $objp->public;
// Check is user has read permission on project
$userAccess = $projectstatic->restrictedProjectArea($user);
if ($userAccess >= 0) {
$var = !$var;
print "<tr " . $bc[$var] . ">";
print '<td>';
$projectstatic->ref = $objp->ref;
print $projectstatic->getNomUrl(1);
if (!in_array('projectlabel', $hiddenfields)) {
print ' - ' . dol_trunc($objp->title, 24);
}
print '</td>';
print '<td>';
if ($objp->fk_soc > 0) {
$thirdpartystatic->id = $objp->fk_soc;
$thirdpartystatic->ref = $objp->socname;
$thirdpartystatic->name = $objp->socname;
print $thirdpartystatic->getNomUrl(1);
}
print '</td>';
if (!empty($conf->global->PROJECT_USE_OPPORTUNITIES)) {
print '<td align="right">';
if ($objp->opp_amount) {
print price($objp->opp_amount, 0, '', 1, -1, -1, $conf->currency);
}
print '</td>';
print '<td align="right">';
$code = dol_getIdFromCode($db, $objp->opp_status, 'c_lead_status', 'rowid', 'code');
if ($code) {
print $langs->trans("OppStatus" . $code);
}
print '</td>';
}
$projectstatic->statut = $objp->status;
if (empty($conf->global->PROJECT_HIDE_TASKS)) {
print '<td align="right">' . $objp->nb . '</td>';
$plannedworkload = $objp->planned_workload;
$total_plannedworkload += $plannedworkload;
if (!in_array('plannedworkload', $hiddenfields)) {
print '<td align="right">' . ($plannedworkload ? convertSecondToTime($plannedworkload) : '') . '</td>';
}
if (!in_array('declaredprogress', $hiddenfields)) {
$declaredprogressworkload = $objp->declared_progess_workload;
$total_declaredprogressworkload += $declaredprogressworkload;
print '<td align="right">';
//print $objp->planned_workload.'-'.$objp->declared_progess_workload."<br>";
print $plannedworkload ? round(100 * $declaredprogressworkload / $plannedworkload, 0) . '%' : '';
print '</td>';
}
}
print '<td align="right">' . $projectstatic->getLibStatut(3) . '</td>';
print "</tr>\n";
$total_task = $total_task + $objp->nb;
$total_opp_amount = $total_opp_amount + $objp->opp_amount;
$ponderated_opp_amount = $ponderated_opp_amount + price2num($listofoppstatus[$objp->opp_status] * $objp->opp_amount / 100);
}
$i++;
}
print '<tr class="liste_total">';
print '<td colspan="2">' . $langs->trans("Total") . "</td>";
if (!empty($conf->global->PROJECT_USE_OPPORTUNITIES)) {
print '<td class="liste_total" align="right">' . price($total_opp_amount, 0, '', 1, -1, -1, $conf->currency) . '</td>';
print '<td class="liste_total" align="right">' . $form->textwithpicto(price($ponderated_opp_amount, 0, '', 1, -1, -1, $conf->currency), $langs->trans("OpportunityPonderatedAmountDesc"), 1) . '</td>';
}
if (empty($conf->global->PROJECT_HIDE_TASKS)) {
print '<td class="liste_total" align="right">' . $total_task . '</td>';
if (!in_array('plannedworkload', $hiddenfields)) {
print '<td class="liste_total" align="right">' . ($total_plannedworkload ? convertSecondToTime($total_plannedworkload) : '') . '</td>';
}
if (!in_array('declaredprogress', $hiddenfields)) {
print '<td class="liste_total" align="right">' . ($total_plannedworkload ? round(100 * $total_declaredprogressworkload / $total_plannedworkload, 0) . '%' : '') . '</td>';
}
}
print '<td class="liste_total"></td>';
print '</tr>';
$db->free($resql);
} else {
dol_print_error($db);
}
print "</table>";
if (!empty($conf->global->PROJECT_LIMIT_YEAR_RANGE)) {
//Add the year filter input
print '<form method="get" action="' . $_SERVER["PHP_SELF"] . '">';
print '<table width="100%">';
print '<tr>';
print '<td>' . $langs->trans("Year") . '</td>';
print '<td style="text-align:right"><input type="text" size="4" class="flat" name="project_year_filter" value="' . $project_year_filter . '"/>';
print "</tr>\n";
print '</table></form>';
}
}
if (! GETPOST("year") && $month_start > $month_current)
{
$year_start--;
$year_end--;
}
$month_end=$month_start-1;
if ($month_end < 1) $month_end=12;
else $year_end++;
}
else $month_end=$month_start;
$date_start=dol_get_first_day($year_start,$month_start,false); $date_end=dol_get_last_day($year_end,$month_end,false);
}
if ($q==1) { $date_start=dol_get_first_day($year_start,1,false); $date_end=dol_get_last_day($year_start,3,false); }
if ($q==2) { $date_start=dol_get_first_day($year_start,4,false); $date_end=dol_get_last_day($year_start,6,false); }
if ($q==3) { $date_start=dol_get_first_day($year_start,7,false); $date_end=dol_get_last_day($year_start,9,false); }
if ($q==4) { $date_start=dol_get_first_day($year_start,10,false); $date_end=dol_get_last_day($year_start,12,false); }
}
else
{
// TODO We define q
}
// Define modecompta ('CREANCES-DETTES' or 'RECETTES-DEPENSES')
$modecompta = $conf->compta->mode;
if ($_GET["modecompta"]) $modecompta=$_GET["modecompta"];
/*
* View
$subtotalpaye = 0;
$subtotal = 0;
}
}
print '<tr class="liste_total"><td align="right" colspan="3">' . $langs->trans("TotalToPay") . ':</td><td class="nowrap" align="right">' . price($total) . '</td>';
print "<td> </td>\n";
print '</tr>';
/*}
else
{
print '<tr><td colspan="5">'.$langs->trans("FeatureNotYetAvailable").'</td></tr>';
print '<tr><td colspan="5">'.$langs->trans("FeatureIsSupportedInInOutModeOnly").'</td></tr>';
}*/
print '</table>';
print '</td>';
print '<td class="notopnoleftnoright" valign="top" width="50%">';
/*
* Payed
*/
$sql = "SELECT SUM(amount) as mm, date_format(f.datev,'%Y-%m') as dm";
$sql .= " FROM " . MAIN_DB_PREFIX . "tva as f";
$sql .= " WHERE f.entity = " . $conf->entity;
$sql .= " AND f.datev >= '" . $db->idate(dol_get_first_day($y, 1, false)) . "'";
$sql .= " AND f.datev <= '" . $db->idate(dol_get_last_day($y, 12, false)) . "'";
$sql .= " GROUP BY dm ORDER BY dm ASC";
pt($db, $sql, $langs->trans("Year") . " {$y}");
print "</td></tr></table>";
print '</td></tr>';
print '</table>';
$db->close();
llxFooter();
if (!$user->rights->holiday->read_all) {
accessforbidden();
}
$year = GETPOST('year');
if (empty($year)) {
$tmpdate = dol_getdate(dol_now());
$year = $tmpdate['year'];
}
$langs->load('users');
/*
* View
*/
$cp = new Holiday($db);
llxHeader(array(), $langs->trans('CPTitreMenu') . ' (' . $langs->trans("Year") . ' ' . $year . ')');
// Recent changes are more important than old changes
$log_holiday = $cp->fetchLog('ORDER BY cpl.rowid DESC', " AND date_action BETWEEN '" . $db->idate(dol_get_first_day($year, 1, 1)) . "' AND '" . $db->idate(dol_get_last_day($year, 12, 1)) . "'");
// Load $cp->logs
print load_fiche_titre($langs->trans('LogCP'), '<div class="pagination"><ul><li class="pagination"><a href="' . $_SERVER["PHP_SELF"] . '?year=' . ($year - 1) . '"><</a><li class="pagination"><a href="">' . $langs->trans("Year") . ' ' . $year . '</a></li><li class="pagination"><a href="' . $_SERVER["PHP_SELF"] . '?year=' . ($year + 1) . '">></a></li></lu></div>', 'title_hrm.png');
print '<div class="info">' . $langs->trans('LastUpdateCP') . ': ' . "\n";
if ($cp->getConfCP('lastUpdate')) {
print '<strong>' . dol_print_date($db->jdate($cp->getConfCP('lastUpdate')), 'dayhour', 'tzuser') . '</strong>';
} else {
print $langs->trans('None');
}
print "</div><br>\n";
print '<table class="noborder" width="100%">';
print '<tbody>';
print '<tr class="liste_titre">';
print '<td class="liste_titre">' . $langs->trans('ID') . '</td>';
print '<td class="liste_titre" align="center">' . $langs->trans('Date') . '</td>';
print '<td class="liste_titre">' . $langs->trans('ActionByCP') . '</td>';
$datesrfc = GETPOST('datesrfc');
$dateerfc = GETPOST('dateerfc');
$dates = dol_mktime(0, 0, 0, GETPOST('datesmonth'), GETPOST('datesday'), GETPOST('datesyear'));
$datee = dol_mktime(23, 59, 59, GETPOST('dateemonth'), GETPOST('dateeday'), GETPOST('dateeyear'));
if (empty($dates) && !empty($datesrfc)) {
$dates = dol_stringtotime($datesrfc);
}
if (empty($datee) && !empty($dateerfc)) {
$datee = dol_stringtotime($dateerfc);
}
if (!isset($_POST['datesrfc']) && !isset($_POST['datesday']) && !empty($conf->global->PROJECT_LINKED_ELEMENT_DEFAULT_FILTER_YEAR)) {
$new = dol_now();
$tmp = dol_getdate($new);
//$datee=$now
//$dates=dol_time_plus_duree($datee, -1, 'y');
$dates = dol_get_first_day($tmp['year'], 1);
}
if ($projectid == '' && $ref == '') {
dol_print_error('', 'Bad parameter');
exit;
}
$mine = $_REQUEST['mode'] == 'mine' ? 1 : 0;
//if (! $user->rights->projet->all->lire) $mine=1; // Special for projects
$project = new Project($db);
if ($id > 0 || !empty($ref)) {
$project->fetch($id, $ref);
$project->fetch_thirdparty();
$projectid = $project->id;
} else {
$project->fetch($projectid);
$project->fetch_thirdparty();
/**
* \brief Return average amount
* \param year Year to scan
* \return array Array of values
*/
function getAverageByMonth($year)
{
$sql = "SELECT date_format(datef,'%m') as dm, AVG(".$this->field.")";
$sql.= " FROM ".$this->from;
$sql.= " WHERE datef BETWEEN '".$this->db->idate(dol_get_first_day($year))."' AND '".$this->db->idate(dol_get_last_day($year))."'";
$sql.= " AND ".$this->where;
$sql.= " GROUP BY dm";
$sql.= $this->db->order('dm','DESC');
return $this->_getAverageByMonth($year, $sql);
}
} else {
$filter .= " AND (cp.date_fin BETWEEN '" . $db->idate(dol_get_first_day($year_end, 1, 1)) . "' AND '" . $db->idate(dol_get_last_day($year_end, 12, 1)) . "')";
//$filter.= " AND date_format(cp.date_fin, '%Y') = '$year_end'";
}
} else {
if ($month_end > 0) {
$filter .= " AND date_format(cp.date_fin, '%m') = '{$month_end}'";
}
}
// DATE CREATE
if ($year_create > 0) {
if ($month_create > 0) {
$filter .= " AND (cp.date_create BETWEEN '" . $db->idate(dol_get_first_day($year_create, $month_create, 1)) . "' AND '" . $db->idate(dol_get_last_day($year_create, $month_create, 1)) . "')";
//$filter.= " AND date_format(cp.date_create, '%Y-%m') = '$year_create-$month_create'";
} else {
$filter .= " AND (cp.date_create BETWEEN '" . $db->idate(dol_get_first_day($year_create, 1, 1)) . "' AND '" . $db->idate(dol_get_last_day($year_create, 12, 1)) . "')";
//$filter.= " AND date_format(cp.date_create, '%Y') = '$year_create'";
}
} else {
if ($month_create > 0) {
$filter .= " AND date_format(cp.date_create, '%m') = '{$month_create}'";
}
}
// EMPLOYE
if (!empty($search_employe) && $search_employe != -1) {
$filter .= " AND cp.fk_user = '******'\n";
}
// VALIDEUR
if (!empty($search_valideur) && $search_valideur != -1) {
$filter .= " AND cp.fk_validator = '" . $db->escape($search_valideur) . "'\n";
}
}
}
// Date Start
if ($month_end > 0) {
if ($year_end > 0 && empty($day)) {
$sql .= " AND d.date_fin BETWEEN '" . $db->idate(dol_get_first_day($year_end, $month_end, false)) . "' AND '" . $db->idate(dol_get_last_day($year_end, $month_end, false)) . "'";
} else {
if ($year_end > 0 && !empty($day)) {
$sql .= " AND d.date_fin BETWEEN '" . $db->idate(dol_mktime(0, 0, 0, $month_end, $day, $year_end)) . "' AND '" . $db->idate(dol_mktime(23, 59, 59, $month_end, $day, $year_end)) . "'";
} else {
$sql .= " AND date_format(d.date_fin, '%m') = '" . $month_end . "'";
}
}
} else {
if ($year_end > 0) {
$sql .= " AND d.date_fin BETWEEN '" . $db->idate(dol_get_first_day($year_end, 1, false)) . "' AND '" . $db->idate(dol_get_last_day($year_end, 12, false)) . "'";
}
}
// Amount
if ($search_amount_ht != '') {
$sql .= natural_search('d.total_ht', $search_amount_ht, 1);
}
if ($search_amount_ttc != '') {
$sql .= natural_search('d.total_ttc', $search_amount_ttc, 1);
}
// User
if ($search_user != '' && $search_user >= 0) {
$sql .= " AND u.rowid = '" . $db->escape($search_user) . "'";
}
// Status
if ($search_status != '' && $search_status >= 0) {
* Put here all code to build page
****************************************************/
$helpurl = 'EN:Module_DoliPos|FR:Module_DoliPos_FR|ES:Módulo_DoliPos';
llxHeader('', '', $helpurl);
$html = new Form($db);
$year_current = strftime("%Y", dol_now());
$pastmonth = strftime("%m", dol_now()) - 1;
$pastmonthyear = $year_current;
if ($pastmonth == 0) {
$pastmonth = 12;
$pastmonthyear--;
}
$date_start = dol_mktime(0, 0, 0, GETPOST("date_startmonth"), GETPOST("date_startday"), GETPOST("date_startyear"));
$date_end = dol_mktime(23, 59, 59, GETPOST("date_endmonth"), GETPOST("date_endday"), GETPOST("date_endyear"));
if (empty($date_start) || empty($date_end)) {
$date_start = dol_get_first_day($pastmonthyear, $pastmonth, false);
$date_end = dol_get_last_day($pastmonthyear, $pastmonth, false);
}
$nom = $langs->trans("TicketSellsJournal");
//$nomlink=;
$builddate = time();
$description = $langs->trans("TicketDescSellsJournal");
$period = $html->select_date($date_start, 'date_start', 0, 0, 0, '', 1, 0, 1) . ' - ' . $html->select_date($date_end, 'date_end', 0, 0, 0, '', 1, 0, 1);
report_header($nom, $nomlink, $period, $periodlink, $description, $builddate, $exportlink);
$p = explode(":", $conf->global->MAIN_INFO_SOCIETE_PAYS);
$idpays = $p[0];
$sql = "SELECT f.rowid, f.ticketnumber, f.type, f.date_closed, fd.product_type, fd.total_ht, fd.total_tva, fd.tva_tx, fd.total_ttc,";
$sql .= " fd.total_localtax1, fd.total_localtax2, p.accountancy_code_sell, s.code_compta,";
$sql .= " s.rowid as socid, s.nom as name, s.code_compta, s.client,";
if (version_compare(DOL_VERSION, 3.3) >= 0) {
$sql .= " ct.accountancy_code_sell";
/**
*
* Return Facture history
*
* @param string $ticketnumber ticket number for filter
* @param int $stat status of ticket
* @param int $mode 0, count rows; 1, get rows
* @param string $terminal terminal for filter
* @param string $seller seller user for filter
* @param string $client client for filter
* @param float $amount amount for filter
* @param int $month month for filter
* @param int $year year for filter
*/
public static function getHistoricFac($ticketnumber = '', $stat, $terminal = '', $seller = '', $client = '', $amount = '', $months = 0, $years = 0)
{
global $db, $conf, $user;
$ret = -1;
$function = "GetHistoric";
$sql = ' SELECT ';
$sql .= ' f.rowid as ticketid, f.facnumber, f.total_ttc,';
$sql .= ' f.fk_user_valid, f.datec as datec,';
$sql .= ' f.fk_statut, pf.fk_place, ';
$sql .= ' s.nom, s.rowid as socid,';
$sql .= ' u.firstname, u.lastname,';
$sql .= ' t.name, pf.fk_cash, f.type';
$sql .= ' FROM ' . MAIN_DB_PREFIX . 'societe as s';
if (!$user->rights->societe->client->voir && !$user->societe_id) {
$sql .= " RIGHT JOIN " . MAIN_DB_PREFIX . "societe_commerciaux as sc ON s.rowid = sc.fk_soc AND sc.fk_user = "******" AND f.entity = " . $conf->entity;
$sql .= " AND pf.fk_cash = t.rowid";
$sql .= " AND pf.fk_facture = f.rowid";
$sql .= " AND u.rowid = f.fk_user_valid";
if ($conf->global->POS_USER_TERMINAL) {
$sql .= " AND pf.fk_cash IN (";
$sql .= "SELECT pu.fk_terminal FROM " . MAIN_DB_PREFIX . "pos_users as pu WHERE pu.fk_object = " . $_SESSION["uid"] . " AND pu.objtype = 'user'";
$sql .= " UNION SELECT pu.fk_terminal FROM " . MAIN_DB_PREFIX . "pos_users as pu LEFT JOIN " . MAIN_DB_PREFIX . "usergroup_user as ug ON pu.fk_object = ug.fk_usergroup";
$sql .= " WHERE ug.fk_user = "******"uid"] . " AND pu.objtype = 'group')";
}
if ($stat >= 0 && $stat != 4 && $stat <= 99) {
$sql .= " AND f.fk_statut = " . $stat;
$sql .= " AND f.type = 0";
}
if ($stat == 4) {
$sql .= " AND f.type = 2";
}
if ($socid) {
$sql .= ' AND s.rowid = ' . $socid;
}
if ($ticketnumber) {
$sql .= ' AND f.facnumber LIKE \'%' . $db->escape(trim($ticketnumber)) . '%\'';
}
if ($months > 0) {
if ($years > 0) {
$sql .= " AND f.datec BETWEEN '" . $db->idate(dol_get_first_day($years, $months, false)) . "' AND '" . $db->idate(dol_get_last_day($years, $months, false)) . "'";
} else {
$sql .= " AND date_format(f.datec, '%m') = '" . $months . "'";
}
} else {
if ($years > 0) {
$sql .= " AND f.datec BETWEEN '" . $db->idate(dol_get_first_day($years, 1, false)) . "' AND '" . $db->idate(dol_get_last_day($years, 12, false)) . "'";
}
}
$now = dol_now();
$time = dol_getdate($now);
$day = $time['mday'];
$month = $time['mon'];
$year = $time['year'];
if ($stat == 100) {
//Today
$ini = sprintf("%04d%02d%02d%02d%02d%02d", $year, $month, $day, 0, 0, 0);
$fin = sprintf("%04d%02d%02d%02d%02d%02d", $year, $month, $day, 23, 59, 59);
$sql .= " AND f.datec BETWEEN '" . $ini . "' AND '" . $fin . "'";
}
if ($stat == 101) {
//Yesterday
$time = dol_get_prev_day($day, $month, $year);
$ini = sprintf("%04d%02d%02d%02d%02d%02d", $time['year'], $time['month'], $time['day'], 0, 0, 0);
$fin = sprintf("%04d%02d%02d%02d%02d%02d", $time['year'], $time['month'], $time['day'], 23, 59, 59);
$sql .= " AND f.datec BETWEEN '" . $ini . "' AND '" . $fin . "'";
}
if ($stat == 102) {
//This week
$time = dol_get_first_day_week($day, $month, $year);
$ini = sprintf("%04d%02d%02d%02d%02d%02d", $time['year'], $time['month'], $time['first_day'], 0, 0, 0);
$fin = sprintf("%04d%02d%02d%02d%02d%02d", $year, $month, $day, 23, 59, 59);
$sql .= " AND f.datec BETWEEN '" . $ini . "' AND '" . $fin . "'";
}
if ($stat == 103) {
//Last week
$time = dol_get_first_day_week($day, $month, $year);
$ini = sprintf("%04d%02d%02d%02d%02d%02d", $time['prev_year'], $time['prev_month'], $time['prev_day'], 0, 0, 0);
$fin = sprintf("%04d%02d%02d%02d%02d%02d", $year, $time['first_day'] - 1 == 0 ? $time['prev_month'] : $month, $time['first_day'] - 1 == 0 ? $time['prev_day'] + 6 : $time['first_day'] - 1, 23, 59, 59);
$sql .= " AND f.datec BETWEEN '" . $ini . "' AND '" . $fin . "'";
}
if ($stat == 104) {
//Two weeks ago
$time = dol_get_prev_week($day, '', $month, $year);
$time2 = dol_get_prev_week($time['day'], '', $time['month'], $time['year']);
$ini = sprintf("%04d%02d%02d%02d%02d%02d", $time2['year'], $time2['month'], $time2['day'], 0, 0, 0);
$fin = sprintf("%04d%02d%02d%02d%02d%02d", $time['year'], $time['day'] - 1 == 0 ? $time2['month'] : $time['month'], $time['day'] - 1 == 0 ? $time2['day'] + 6 : $time['day'] - 1, 23, 59, 59);
$sql .= " AND f.datec BETWEEN '" . $ini . "' AND '" . $fin . "'";
}
if ($stat == 105) {
//Three weeks ago
$time = dol_get_prev_week($day, '', $month, $year);
$time = dol_get_prev_week($time['day'], '', $time['month'], $time['year']);
$time2 = dol_get_prev_week($time['day'], '', $time['month'], $time['year']);
$ini = sprintf("%04d%02d%02d%02d%02d%02d", $time2['year'], $time2['month'], $time2['day'], 0, 0, 0);
$fin = sprintf("%04d%02d%02d%02d%02d%02d", $time['year'], $time['day'] - 1 == 0 ? $time2['month'] : $time['month'], $time['day'] - 1 == 0 ? $time2['day'] + 6 : $time['day'] - 1, 23, 59, 59);
$sql .= " AND f.datec BETWEEN '" . $ini . "' AND '" . $fin . "'";
}
if ($stat == 106) {
//This month
$ini = sprintf("%04d%02d%02d%02d%02d%02d", $year, $month, 01, 0, 0, 0);
$fin = sprintf("%04d%02d%02d%02d%02d%02d", $year, $month, $day, 23, 59, 59);
$sql .= " AND f.datec BETWEEN '" . $ini . "' AND '" . $fin . "'";
}
if ($stat == 107) {
//One month ago
$time = dol_get_prev_month($month, $year);
$ini = sprintf("%04d%02d%02d%02d%02d%02d", $time['year'], $time['month'], $day, 0, 0, 0);
$fin = sprintf("%04d%02d%02d%02d%02d%02d", $year, $month, $day, 23, 59, 59);
$sql .= " AND f.datec BETWEEN '" . $ini . "' AND '" . $fin . "'";
}
if ($stat == 108) {
//Last month
$time = dol_get_prev_month($month, $year);
$ini = sprintf("%04d%02d%02d%02d%02d%02d", $time['year'], $time['month'], 01, 0, 0, 0);
$fin = sprintf("%04d%02d%02d%02d%02d%02d", $time['year'], $time['month'], 31, 0, 0, 0);
$sql .= " AND f.datec BETWEEN '" . $ini . "' AND '" . $fin . "'";
}
if ($terminal) {
$sql .= ' AND t.name LIKE \'%' . $db->escape(trim($terminal)) . '%\'';
}
if ($seller) {
$sql .= ' AND (u.firstname LIKE \'%' . $db->escape(trim($seller)) . '%\'';
$sql .= ' OR u.lastname LIKE \'%' . $db->escape(trim($seller)) . '%\')';
}
if ($client) {
$prefix = empty($conf->global->COMPANY_DONOTSEARCH_ANYWHERE) ? '%' : '';
// Can use index if COMPANY_DONOTSEARCH_ANYWHERE is on
$sql .= ' AND s.nom LIKE \'' . $prefix . $db->escape(trim($client)) . '%\'';
}
if ($amount) {
$sql .= ' AND f.total_ttc = \'' . $db->escape(trim($amount)) . '\'';
}
$sql .= ' GROUP BY f.rowid';
$sql .= ' UNION SELECT ';
$sql .= ' p.rowid as ticketid, p.ticketnumber, p.total_ttc,';
$sql .= ' p.fk_user_close, p.date_creation as datec,';
$sql .= ' p.fk_statut, p.fk_place, ';
$sql .= ' s.nom, s.rowid as socid,';
$sql .= ' u.firstname, u.lastname,';
$sql .= ' t.name, p.fk_cash, p.type';
$sql .= ' FROM ' . MAIN_DB_PREFIX . 'societe as s';
if (!$user->rights->societe->client->voir && !$user->societe_id) {
$sql .= " RIGHT JOIN " . MAIN_DB_PREFIX . "societe_commerciaux as sc ON s.rowid = sc.fk_soc AND sc.fk_user = "******" AND p.entity = " . $conf->entity;
$sql .= " AND p.fk_cash = t.rowid";
$sql .= " AND p.fk_statut = 0";
$sql .= " AND u.rowid = p.fk_user_author";
if ($conf->global->POS_USER_TERMINAL) {
$sql .= " AND p.fk_cash IN (";
$sql .= "SELECT pu.fk_terminal FROM " . MAIN_DB_PREFIX . "pos_users as pu WHERE pu.fk_object = " . $_SESSION["uid"] . " AND pu.objtype = 'user'";
$sql .= " UNION SELECT pu.fk_terminal FROM " . MAIN_DB_PREFIX . "pos_users as pu LEFT JOIN " . MAIN_DB_PREFIX . "usergroup_user as ug ON pu.fk_object = ug.fk_usergroup";
$sql .= " WHERE ug.fk_user = "******"uid"] . " AND pu.objtype = 'group')";
}
if ($stat >= 0 && $stat != 4 && $stat <= 99) {
$sql .= " AND p.fk_statut = " . $stat;
}
if ($stat == 4) {
$sql .= " AND p.type = 1";
}
if ($socid) {
$sql .= ' AND s.rowid = ' . $socid;
}
if ($ticketnumber) {
$sql .= ' AND p.ticketnumber LIKE \'%' . $db->escape(trim($ticketnumber)) . '%\'';
}
if ($months > 0) {
if ($years > 0) {
$sql .= " AND p.date_ticket BETWEEN '" . $db->idate(dol_get_first_day($years, $months, false)) . "' AND '" . $db->idate(dol_get_last_day($years, $months, false)) . "'";
} else {
$sql .= " AND date_format(p.date_ticket, '%m') = '" . $months . "'";
}
} else {
if ($years > 0) {
$sql .= " AND p.date_ticket BETWEEN '" . $db->idate(dol_get_first_day($years, 1, false)) . "' AND '" . $db->idate(dol_get_last_day($years, 12, false)) . "'";
}
}
$now = dol_now();
$time = dol_getdate($now);
$day = $time['mday'];
$month = $time['mon'];
$year = $time['year'];
if ($stat == 100) {
//Today
$ini = sprintf("%04d%02d%02d%02d%02d%02d", $year, $month, $day, 0, 0, 0);
$fin = sprintf("%04d%02d%02d%02d%02d%02d", $year, $month, $day, 23, 59, 59);
$sql .= " AND p.date_ticket BETWEEN '" . $ini . "' AND '" . $fin . "'";
}
if ($stat == 101) {
//Yesterday
$time = dol_get_prev_day($day, $month, $year);
$ini = sprintf("%04d%02d%02d%02d%02d%02d", $time['year'], $time['month'], $time['day'], 0, 0, 0);
$fin = sprintf("%04d%02d%02d%02d%02d%02d", $time['year'], $time['month'], $time['day'], 23, 59, 59);
$sql .= " AND p.date_ticket BETWEEN '" . $ini . "' AND '" . $fin . "'";
}
if ($stat == 102) {
//This week
$time = dol_get_first_day_week($day, $month, $year);
$ini = sprintf("%04d%02d%02d%02d%02d%02d", $time['year'], $time['month'], $time['first_day'], 0, 0, 0);
$fin = sprintf("%04d%02d%02d%02d%02d%02d", $year, $month, $day, 23, 59, 59);
$sql .= " AND p.date_ticket BETWEEN '" . $ini . "' AND '" . $fin . "'";
}
if ($stat == 103) {
//Last week
$time = dol_get_first_day_week($day, $month, $year);
$ini = sprintf("%04d%02d%02d%02d%02d%02d", $time['prev_year'], $time['prev_month'], $time['prev_day'], 0, 0, 0);
$fin = sprintf("%04d%02d%02d%02d%02d%02d", $year, $time['first_day'] - 1 == 0 ? $time['prev_month'] : $month, $time['first_day'] - 1 == 0 ? $time['prev_day'] + 6 : $time['first_day'] - 1, 23, 59, 59);
$sql .= " AND p.date_ticket BETWEEN '" . $ini . "' AND '" . $fin . "'";
}
if ($stat == 104) {
//Two weeks ago
$time = dol_get_prev_week($day, '', $month, $year);
$time2 = dol_get_prev_week($time['day'], '', $time['month'], $time['year']);
$ini = sprintf("%04d%02d%02d%02d%02d%02d", $time2['year'], $time2['month'], $time2['day'], 0, 0, 0);
$fin = sprintf("%04d%02d%02d%02d%02d%02d", $time['year'], $time['day'] - 1 == 0 ? $time2['month'] : $time['month'], $time['day'] - 1 == 0 ? $time2['day'] + 6 : $time['day'] - 1, 23, 59, 59);
$sql .= " AND p.date_ticket BETWEEN '" . $ini . "' AND '" . $fin . "'";
}
if ($stat == 105) {
//Three weeks ago
$time = dol_get_prev_week($day, '', $month, $year);
$time = dol_get_prev_week($time['day'], '', $time['month'], $time['year']);
$time2 = dol_get_prev_week($time['day'], '', $time['month'], $time['year']);
$ini = sprintf("%04d%02d%02d%02d%02d%02d", $time2['year'], $time2['month'], $time2['day'], 0, 0, 0);
$fin = sprintf("%04d%02d%02d%02d%02d%02d", $time['year'], $time['day'] - 1 == 0 ? $time2['month'] : $time['month'], $time['day'] - 1 == 0 ? $time2['day'] + 6 : $time['day'] - 1, 23, 59, 59);
$sql .= " AND p.date_ticket BETWEEN '" . $ini . "' AND '" . $fin . "'";
}
if ($stat == 106) {
//This month
$ini = sprintf("%04d%02d%02d%02d%02d%02d", $year, $month, 01, 0, 0, 0);
$fin = sprintf("%04d%02d%02d%02d%02d%02d", $year, $month, $day, 23, 59, 59);
$sql .= " AND p.date_ticket BETWEEN '" . $ini . "' AND '" . $fin . "'";
}
if ($stat == 107) {
//One month ago
$time = dol_get_prev_month($month, $year);
$ini = sprintf("%04d%02d%02d%02d%02d%02d", $time['year'], $time['month'], $day, 0, 0, 0);
$fin = sprintf("%04d%02d%02d%02d%02d%02d", $year, $month, $day, 23, 59, 59);
$sql .= " AND p.date_ticket BETWEEN '" . $ini . "' AND '" . $fin . "'";
}
if ($stat == 108) {
//Last month
$time = dol_get_prev_month($month, $year);
$ini = sprintf("%04d%02d%02d%02d%02d%02d", $time['year'], $time['month'], 01, 0, 0, 0);
$fin = sprintf("%04d%02d%02d%02d%02d%02d", $time['year'], $time['month'], 31, 0, 0, 0);
$sql .= " AND p.date_ticket BETWEEN '" . $ini . "' AND '" . $fin . "'";
}
if ($terminal) {
$sql .= ' AND t.name LIKE \'%' . $db->escape(trim($terminal)) . '%\'';
}
if ($seller) {
$sql .= ' AND (u.firstname LIKE \'%' . $db->escape(trim($seller)) . '%\'';
$sql .= ' OR u.lastname LIKE \'%' . $db->escape(trim($seller)) . '%\')';
}
if ($client) {
$prefix = empty($conf->global->COMPANY_DONOTSEARCH_ANYWHERE) ? '%' : '';
// Can use index if COMPANY_DONOTSEARCH_ANYWHERE is on
$sql .= ' AND s.nom LIKE \'' . $prefix . $db->escape(trim($client)) . '%\'';
}
if ($amount) {
$sql .= ' AND p.total_ttc = \'' . $db->escape(trim($amount)) . '\'';
}
$sql .= ' GROUP BY p.rowid';
$sql .= ' ORDER BY ';
$sql .= ' datec DESC ';
$sql .= 'LIMIT 0,50';
$res = $db->query($sql);
if ($res) {
$num = $db->num_rows($resql);
$i = 0;
$ticketstatic = new Ticket($db);
while ($i < $num) {
$obj = $db->fetch_object($res);
$tickets[$i]["id"] = $obj->ticketid;
$tickets[$i]["type"] = $obj->type == 2 ? 1 : $obj->type;
$tickets[$i]["ticketnumber"] = $obj->facnumber;
$tickets[$i]["date_creation"] = dol_print_date($db->jdate($obj->datec), 'dayhour');
$tickets[$i]["date_close"] = dol_print_date($db->jdate($obj->date_closed), 'dayhour');
$tickets[$i]["fk_place"] = $obj->fk_place;
$cash = new Cash($db);
$cash->fetch($obj->fk_cash);
$tickets[$i]["terminal"] = $cash->name;
$userstatic = new User($db);
$userstatic->fetch($obj->fk_user_valid);
$tickets[$i]["seller"] = $userstatic->getFullName($langs);
$tickets[$i]["client"] = $obj->nom;
$tickets[$i]["amount"] = $obj->total_ttc;
$tickets[$i]["customer_pay"] = $obj->customer_pay;
$tickets[$i]["statut"] = $obj->fk_statut;
$tickets[$i]["statutlabel"] = $ticketstatic->LibStatut($obj->fk_statut, 0);
$i++;
}
return ErrorControl($tickets, $function);
} else {
return ErrorControl($ret, $function);
}
}
/**
* Gets VAT to collect for the given year (and given quarter or month)
* The function gets the VAT in split results, as the VAT declaration asks
* to report the amounts for different VAT rates as different lines.
* This function also accounts recurrent invoices.
*
* @param DoliDB $db Database handler object
* @param int $y Year
* @param int $q Quarter
* @param string $date_start Start date
* @param string $date_end End date
* @param int $modetax 0 or 1 (option vat on debit)
* @param int $direction 'sell' (customer invoice) or 'buy' (supplier invoices)
* @param int $m Month
* @return array List of quarters with vat
*/
function vat_by_date($db, $y, $q, $date_start, $date_end, $modetax, $direction, $m = 0)
{
global $conf;
$list = array();
if ($direction == 'sell') {
$invoicetable = 'facture';
$invoicedettable = 'facturedet';
$fk_facture = 'fk_facture';
$fk_facture2 = 'fk_facture';
$fk_payment = 'fk_paiement';
$total_tva = 'total_tva';
$total_localtax1 = 'total_localtax1';
$total_localtax2 = 'total_localtax2';
$paymenttable = 'paiement';
$paymentfacturetable = 'paiement_facture';
$invoicefieldref = 'facnumber';
}
if ($direction == 'buy') {
$invoicetable = 'facture_fourn';
$invoicedettable = 'facture_fourn_det';
$fk_facture = 'fk_facture_fourn';
$fk_facture2 = 'fk_facturefourn';
$fk_payment = 'fk_paiementfourn';
$total_tva = 'tva';
$total_localtax1 = 'total_localtax1';
$total_localtax2 = 'total_localtax2';
$paymenttable = 'paiementfourn';
$paymentfacturetable = 'paiementfourn_facturefourn';
$invoicefieldref = 'ref';
}
// CAS DES BIENS
// Define sql request
$sql = '';
if ($modetax == 1) {
// Count on delivery date (use invoice date as delivery is unknown)
$sql = "SELECT d.rowid, d.product_type as dtype, d." . $fk_facture . " as facid, d.tva_tx as rate, d.total_ht as total_ht, d.total_ttc as total_ttc, d." . $total_tva . " as total_vat, d.description as descr,";
$sql .= " d." . $total_localtax1 . " as total_localtax1, d." . $total_localtax2 . " as total_localtax2, ";
$sql .= " d.date_start as date_start, d.date_end as date_end,";
$sql .= " f." . $invoicefieldref . " as facnum, f.type, f.total_ttc as ftotal_ttc, f.datef, s.nom as company_name, s.rowid as company_id,";
$sql .= " p.rowid as pid, p.ref as pref, p.fk_product_type as ptype,";
$sql .= " 0 as payment_id, 0 as payment_amount";
$sql .= " FROM " . MAIN_DB_PREFIX . $invoicetable . " as f,";
$sql .= " " . MAIN_DB_PREFIX . "societe as s,";
$sql .= " " . MAIN_DB_PREFIX . $invoicedettable . " as d";
$sql .= " LEFT JOIN " . MAIN_DB_PREFIX . "product as p on d.fk_product = p.rowid";
$sql .= " WHERE f.entity = " . $conf->entity;
$sql .= " AND f.fk_statut in (1,2)";
// Validated or paid (partially or completely)
if (!empty($conf->global->FACTURE_DEPOSITS_ARE_JUST_PAYMENTS)) {
$sql .= " AND f.type IN (0,1,2,5)";
} else {
$sql .= " AND f.type IN (0,1,2,3,5)";
}
$sql .= " AND f.rowid = d." . $fk_facture;
$sql .= " AND s.rowid = f.fk_soc";
if ($y && $m) {
$sql .= " AND f.datef >= '" . $db->idate(dol_get_first_day($y, $m, false)) . "'";
$sql .= " AND f.datef <= '" . $db->idate(dol_get_last_day($y, $m, false)) . "'";
} else {
if ($y) {
$sql .= " AND f.datef >= '" . $db->idate(dol_get_first_day($y, 1, false)) . "'";
$sql .= " AND f.datef <= '" . $db->idate(dol_get_last_day($y, 12, false)) . "'";
}
}
if ($q) {
$sql .= " AND (date_format(f.datef,'%m') > " . ($q - 1) * 3 . " AND date_format(f.datef,'%m') <= " . $q * 3 . ")";
}
if ($date_start && $date_end) {
$sql .= " AND f.datef >= '" . $db->idate($date_start) . "' AND f.datef <= '" . $db->idate($date_end) . "'";
}
$sql .= " AND (d.product_type = 0";
// Limit to products
$sql .= " AND d.date_start is null AND d.date_end IS NULL)";
// enhance detection of service
$sql .= " ORDER BY d.rowid, d." . $fk_facture;
} else {
// Count on delivery date (use invoice date as delivery is unknown)
$sql = "SELECT d.rowid, d.product_type as dtype, d." . $fk_facture . " as facid, d.tva_tx as rate, d.total_ht as total_ht, d.total_ttc as total_ttc, d." . $total_tva . " as total_vat, d.description as descr,";
$sql .= " d." . $total_localtax1 . " as total_localtax1, d." . $total_localtax2 . " as total_localtax2, ";
$sql .= " d.date_start as date_start, d.date_end as date_end,";
$sql .= " f." . $invoicefieldref . " as facnum, f.type, f.total_ttc as ftotal_ttc, f.datef as date_f, s.nom as company_name, s.rowid as company_id,";
$sql .= " p.rowid as pid, p.ref as pref, p.fk_product_type as ptype,";
$sql .= " 0 as payment_id, 0 as payment_amount";
$sql .= " FROM " . MAIN_DB_PREFIX . $invoicetable . " as f,";
$sql .= " " . MAIN_DB_PREFIX . "societe as s,";
$sql .= " " . MAIN_DB_PREFIX . $invoicedettable . " as d";
$sql .= " LEFT JOIN " . MAIN_DB_PREFIX . "product as p on d.fk_product = p.rowid";
$sql .= " WHERE f.entity = " . $conf->entity;
$sql .= " AND f.fk_statut in (1,2)";
// Validated or paid (partially or completely)
if (!empty($conf->global->FACTURE_DEPOSITS_ARE_JUST_PAYMENTS)) {
$sql .= " AND f.type IN (0,1,2,5)";
} else {
$sql .= " AND f.type IN (0,1,2,3,5)";
}
$sql .= " AND f.rowid = d." . $fk_facture;
$sql .= " AND s.rowid = f.fk_soc";
if ($y && $m) {
$sql .= " AND f.datef >= '" . $db->idate(dol_get_first_day($y, $m, false)) . "'";
$sql .= " AND f.datef <= '" . $db->idate(dol_get_last_day($y, $m, false)) . "'";
} else {
if ($y) {
$sql .= " AND f.datef >= '" . $db->idate(dol_get_first_day($y, 1, false)) . "'";
$sql .= " AND f.datef <= '" . $db->idate(dol_get_last_day($y, 12, false)) . "'";
}
}
if ($q) {
$sql .= " AND (date_format(f.datef,'%m') > " . ($q - 1) * 3 . " AND date_format(f.datef,'%m') <= " . $q * 3 . ")";
}
if ($date_start && $date_end) {
$sql .= " AND f.datef >= '" . $db->idate($date_start) . "' AND f.datef <= '" . $db->idate($date_end) . "'";
}
$sql .= " AND (d.product_type = 0";
// Limit to products
$sql .= " AND d.date_start is null AND d.date_end IS NULL)";
// enhance detection of service
$sql .= " ORDER BY d.rowid, d." . $fk_facture;
//print $sql;
}
//print $sql.'<br>';
if (!$sql) {
return -1;
}
if ($sql == 'TODO') {
return -2;
}
if ($sql != 'TODO') {
dol_syslog("Tax.lib.php::vat_by_date", LOG_DEBUG);
$resql = $db->query($sql);
if ($resql) {
$rate = -1;
$oldrowid = '';
while ($assoc = $db->fetch_array($resql)) {
if (!isset($list[$assoc['rate']]['totalht'])) {
$list[$assoc['rate']]['totalht'] = 0;
}
if (!isset($list[$assoc['rate']]['vat'])) {
$list[$assoc['rate']]['vat'] = 0;
}
if (!isset($list[$assoc['rate']]['localtax1'])) {
$list[$assoc['rate']]['localtax1'] = 0;
}
if (!isset($list[$assoc['rate']]['localtax2'])) {
$list[$assoc['rate']]['localtax2'] = 0;
}
if ($assoc['rowid'] != $oldrowid) {
$oldrowid = $assoc['rowid'];
$list[$assoc['rate']]['totalht'] += $assoc['total_ht'];
$list[$assoc['rate']]['vat'] += $assoc['total_vat'];
$list[$assoc['rate']]['localtax1'] += $assoc['total_localtax1'];
$list[$assoc['rate']]['localtax2'] += $assoc['total_localtax2'];
}
$list[$assoc['rate']]['dtotal_ttc'][] = $assoc['total_ttc'];
$list[$assoc['rate']]['dtype'][] = $assoc['dtype'];
$list[$assoc['rate']]['datef'][] = $assoc['datef'];
$list[$assoc['rate']]['company_name'][] = $assoc['company_name'];
$list[$assoc['rate']]['company_id'][] = $assoc['company_id'];
$list[$assoc['rate']]['ddate_start'][] = $db->jdate($assoc['date_start']);
$list[$assoc['rate']]['ddate_end'][] = $db->jdate($assoc['date_end']);
$list[$assoc['rate']]['facid'][] = $assoc['facid'];
$list[$assoc['rate']]['facnum'][] = $assoc['facnum'];
$list[$assoc['rate']]['type'][] = $assoc['type'];
$list[$assoc['rate']]['ftotal_ttc'][] = $assoc['ftotal_ttc'];
$list[$assoc['rate']]['descr'][] = $assoc['descr'];
$list[$assoc['rate']]['totalht_list'][] = $assoc['total_ht'];
$list[$assoc['rate']]['vat_list'][] = $assoc['total_vat'];
$list[$assoc['rate']]['localtax1_list'][] = $assoc['total_localtax1'];
$list[$assoc['rate']]['localtax2_list'][] = $assoc['total_localtax2'];
$list[$assoc['rate']]['pid'][] = $assoc['pid'];
$list[$assoc['rate']]['pref'][] = $assoc['pref'];
$list[$assoc['rate']]['ptype'][] = $assoc['ptype'];
$list[$assoc['rate']]['payment_id'][] = $assoc['payment_id'];
$list[$assoc['rate']]['payment_amount'][] = $assoc['payment_amount'];
$rate = $assoc['rate'];
}
} else {
dol_print_error($db);
return -3;
}
}
// CAS DES SERVICES
// Define sql request
$sql = '';
if ($modetax == 1) {
// Count on invoice date
$sql = "SELECT d.rowid, d.product_type as dtype, d." . $fk_facture . " as facid, d.tva_tx as rate, d.total_ht as total_ht, d.total_ttc as total_ttc, d." . $total_tva . " as total_vat, d.description as descr,";
$sql .= " d." . $total_localtax1 . " as total_localtax1, d." . $total_localtax2 . " as total_localtax2, ";
$sql .= " d.date_start as date_start, d.date_end as date_end,";
$sql .= " f." . $invoicefieldref . " as facnum, f.type, f.total_ttc as ftotal_ttc, f.datef, s.nom as company_name, s.rowid as company_id,";
$sql .= " p.rowid as pid, p.ref as pref, p.fk_product_type as ptype,";
$sql .= " 0 as payment_id, 0 as payment_amount";
$sql .= " FROM " . MAIN_DB_PREFIX . $invoicetable . " as f,";
$sql .= " " . MAIN_DB_PREFIX . "societe as s,";
$sql .= " " . MAIN_DB_PREFIX . $invoicedettable . " as d";
$sql .= " LEFT JOIN " . MAIN_DB_PREFIX . "product as p on d.fk_product = p.rowid";
$sql .= " WHERE f.entity = " . $conf->entity;
$sql .= " AND f.fk_statut in (1,2)";
// Validated or paid (partially or completely)
if (!empty($conf->global->FACTURE_DEPOSITS_ARE_JUST_PAYMENTS)) {
$sql .= " AND f.type IN (0,1,2,5)";
} else {
$sql .= " AND f.type IN (0,1,2,3,5)";
}
$sql .= " AND f.rowid = d." . $fk_facture;
$sql .= " AND s.rowid = f.fk_soc";
if ($y && $m) {
$sql .= " AND f.datef >= '" . $db->idate(dol_get_first_day($y, $m, false)) . "'";
$sql .= " AND f.datef <= '" . $db->idate(dol_get_last_day($y, $m, false)) . "'";
} else {
if ($y) {
$sql .= " AND f.datef >= '" . $db->idate(dol_get_first_day($y, 1, false)) . "'";
$sql .= " AND f.datef <= '" . $db->idate(dol_get_last_day($y, 12, false)) . "'";
}
}
if ($q) {
$sql .= " AND (date_format(f.datef,'%m') > " . ($q - 1) * 3 . " AND date_format(f.datef,'%m') <= " . $q * 3 . ")";
}
if ($date_start && $date_end) {
$sql .= " AND f.datef >= '" . $db->idate($date_start) . "' AND f.datef <= '" . $db->idate($date_end) . "'";
}
$sql .= " AND (d.product_type = 1";
// Limit to services
$sql .= " OR d.date_start is NOT null OR d.date_end IS NOT NULL)";
// enhance detection of service
$sql .= " ORDER BY d.rowid, d." . $fk_facture;
} else {
// Count on payments date
$sql = "SELECT d.rowid, d.product_type as dtype, d." . $fk_facture . " as facid, d.tva_tx as rate, d.total_ht as total_ht, d.total_ttc as total_ttc, d." . $total_tva . " as total_vat, d.description as descr,";
$sql .= " d." . $total_localtax1 . " as total_localtax1, d." . $total_localtax2 . " as total_localtax2, ";
$sql .= " d.date_start as date_start, d.date_end as date_end,";
$sql .= " f." . $invoicefieldref . " as facnum, f.type, f.total_ttc as ftotal_ttc, f.datef, s.nom as company_name, s.rowid as company_id,";
$sql .= " p.rowid as pid, p.ref as pref, p.fk_product_type as ptype,";
$sql .= " pf." . $fk_payment . " as payment_id, pf.amount as payment_amount";
$sql .= " FROM " . MAIN_DB_PREFIX . $invoicetable . " as f,";
$sql .= " " . MAIN_DB_PREFIX . $paymentfacturetable . " as pf,";
$sql .= " " . MAIN_DB_PREFIX . $paymenttable . " as pa,";
$sql .= " " . MAIN_DB_PREFIX . "societe as s,";
$sql .= " " . MAIN_DB_PREFIX . $invoicedettable . " as d";
$sql .= " LEFT JOIN " . MAIN_DB_PREFIX . "product as p on d.fk_product = p.rowid";
$sql .= " WHERE f.entity = " . $conf->entity;
$sql .= " AND f.fk_statut in (1,2)";
// Paid (partially or completely)
if (!empty($conf->global->FACTURE_DEPOSITS_ARE_JUST_PAYMENTS)) {
$sql .= " AND f.type IN (0,1,2,5)";
} else {
$sql .= " AND f.type IN (0,1,2,3,5)";
}
$sql .= " AND f.rowid = d." . $fk_facture;
$sql .= " AND s.rowid = f.fk_soc";
$sql .= " AND pf." . $fk_facture2 . " = f.rowid";
$sql .= " AND pa.rowid = pf." . $fk_payment;
if ($y && $m) {
$sql .= " AND pa.datep >= '" . $db->idate(dol_get_first_day($y, $m, false)) . "'";
$sql .= " AND pa.datep <= '" . $db->idate(dol_get_last_day($y, $m, false)) . "'";
} else {
if ($y) {
$sql .= " AND pa.datep >= '" . $db->idate(dol_get_first_day($y, 1, false)) . "'";
$sql .= " AND pa.datep <= '" . $db->idate(dol_get_last_day($y, 12, false)) . "'";
}
}
if ($q) {
$sql .= " AND (date_format(pa.datep,'%m') > " . ($q - 1) * 3 . " AND date_format(pa.datep,'%m') <= " . $q * 3 . ")";
}
if ($date_start && $date_end) {
$sql .= " AND pa.datep >= '" . $db->idate($date_start) . "' AND pa.datep <= '" . $db->idate($date_end) . "'";
}
$sql .= " AND (d.product_type = 1";
// Limit to services
$sql .= " OR d.date_start is NOT null OR d.date_end IS NOT NULL)";
// enhance detection of service
$sql .= " ORDER BY d.rowid, d." . $fk_facture . ", pf.rowid";
}
if (!$sql) {
dol_syslog("Tax.lib.php::vat_by_date no accountancy module enabled" . $sql, LOG_ERR);
return -1;
// -1 = Not accountancy module enabled
}
if ($sql == 'TODO') {
return -2;
}
// -2 = Feature not yet available
if ($sql != 'TODO') {
dol_syslog("Tax.lib.php::vat_by_date", LOG_DEBUG);
$resql = $db->query($sql);
if ($resql) {
$rate = -1;
$oldrowid = '';
while ($assoc = $db->fetch_array($resql)) {
if (!isset($list[$assoc['rate']]['totalht'])) {
$list[$assoc['rate']]['totalht'] = 0;
}
if (!isset($list[$assoc['rate']]['vat'])) {
$list[$assoc['rate']]['vat'] = 0;
}
if (!isset($list[$assoc['rate']]['localtax1'])) {
$list[$assoc['rate']]['localtax1'] = 0;
}
if (!isset($list[$assoc['rate']]['localtax2'])) {
$list[$assoc['rate']]['localtax2'] = 0;
}
if ($assoc['rowid'] != $oldrowid) {
$oldrowid = $assoc['rowid'];
$list[$assoc['rate']]['totalht'] += $assoc['total_ht'];
$list[$assoc['rate']]['vat'] += $assoc['total_vat'];
$list[$assoc['rate']]['localtax1'] += $assoc['total_localtax1'];
$list[$assoc['rate']]['localtax2'] += $assoc['total_localtax2'];
}
$list[$assoc['rate']]['dtotal_ttc'][] = $assoc['total_ttc'];
$list[$assoc['rate']]['dtype'][] = $assoc['dtype'];
$list[$assoc['rate']]['datef'][] = $assoc['datef'];
$list[$assoc['rate']]['company_name'][] = $assoc['company_name'];
$list[$assoc['rate']]['company_id'][] = $assoc['company_id'];
$list[$assoc['rate']]['ddate_start'][] = $db->jdate($assoc['date_start']);
$list[$assoc['rate']]['ddate_end'][] = $db->jdate($assoc['date_end']);
$list[$assoc['rate']]['facid'][] = $assoc['facid'];
$list[$assoc['rate']]['facnum'][] = $assoc['facnum'];
$list[$assoc['rate']]['type'][] = $assoc['type'];
$list[$assoc['rate']]['ftotal_ttc'][] = $assoc['ftotal_ttc'];
$list[$assoc['rate']]['descr'][] = $assoc['descr'];
$list[$assoc['rate']]['totalht_list'][] = $assoc['total_ht'];
$list[$assoc['rate']]['vat_list'][] = $assoc['total_vat'];
$list[$assoc['rate']]['localtax1_list'][] = $assoc['total_localtax1'];
$list[$assoc['rate']]['localtax2_list'][] = $assoc['total_localtax2'];
$list[$assoc['rate']]['pid'][] = $assoc['pid'];
$list[$assoc['rate']]['pref'][] = $assoc['pref'];
$list[$assoc['rate']]['ptype'][] = $assoc['ptype'];
$list[$assoc['rate']]['payment_id'][] = $assoc['payment_id'];
$list[$assoc['rate']]['payment_amount'][] = $assoc['payment_amount'];
$rate = $assoc['rate'];
}
} else {
dol_print_error($db);
return -3;
}
}
return $list;
}
$sql .= " AND c.date_commande BETWEEN '" . $db->idate(dol_get_first_day($orderyear, 1, false)) . "' AND '" . $db->idate(dol_get_last_day($orderyear, 12, false)) . "'";
}
}
if ($deliverymonth > 0) {
if ($deliveryyear > 0 && empty($day)) {
$sql .= " AND c.date_livraison BETWEEN '" . $db->idate(dol_get_first_day($deliveryyear, $deliverymonth, false)) . "' AND '" . $db->idate(dol_get_last_day($deliveryyear, $deliverymonth, false)) . "'";
} else {
if ($deliveryyear > 0 && !empty($day)) {
$sql .= " AND c.date_livraison BETWEEN '" . $db->idate(dol_mktime(0, 0, 0, $deliverymonth, $day, $deliveryyear)) . "' AND '" . $db->idate(dol_mktime(23, 59, 59, $deliverymonth, $day, $deliveryyear)) . "'";
} else {
$sql .= " AND date_format(c.date_livraison, '%m') = '" . $deliverymonth . "'";
}
}
} else {
if ($deliveryyear > 0) {
$sql .= " AND c.date_livraison BETWEEN '" . $db->idate(dol_get_first_day($deliveryyear, 1, false)) . "' AND '" . $db->idate(dol_get_last_day($deliveryyear, 12, false)) . "'";
}
}
if (!empty($search_company)) {
$sql .= natural_search('s.nom', $search_company);
}
if (!empty($search_ref_customer)) {
$sql .= natural_search('c.ref_client', $search_ref_customer);
}
if ($search_sale > 0) {
$sql .= " AND s.rowid = sc.fk_soc AND sc.fk_user = "******" AND ec.fk_c_type_contact = tc.rowid AND tc.element='commande' AND tc.source='internal' AND ec.element_id = c.rowid AND ec.fk_socpeople = " . $search_user;
}
if ($search_total_ht != '') {
if ($viewstatut != '') {
$sql .= ' AND p.fk_statut IN (' . $viewstatut . ')';
}
if ($month > 0) {
if ($year > 0 && empty($day)) {
$sql .= " AND p.datep BETWEEN '" . $db->idate(dol_get_first_day($year, $month, false)) . "' AND '" . $db->idate(dol_get_last_day($year, $month, false)) . "'";
} else {
if ($year > 0 && !empty($day)) {
$sql .= " AND p.datep BETWEEN '" . $db->idate(dol_mktime(0, 0, 0, $month, $day, $year)) . "' AND '" . $db->idate(dol_mktime(23, 59, 59, $month, $day, $year)) . "'";
} else {
$sql .= " AND date_format(p.datep, '%m') = '" . $month . "'";
}
}
} else {
if ($year > 0) {
$sql .= " AND p.datep BETWEEN '" . $db->idate(dol_get_first_day($year, 1, false)) . "' AND '" . $db->idate(dol_get_last_day($year, 12, false)) . "'";
}
}
if ($search_sale > 0) {
$sql .= " AND s.rowid = sc.fk_soc AND sc.fk_user = "******" AND c.fk_c_type_contact = tc.rowid AND tc.element='propal' AND tc.source='internal' AND c.element_id = p.rowid AND c.fk_socpeople = " . $search_user;
}
$sql .= ' ORDER BY ' . $sortfield . ' ' . $sortorder . ', p.ref DESC';
$nbtotalofrecords = 0;
if (empty($conf->global->MAIN_DISABLE_FULL_SCANLIST)) {
$result = $db->query($sql);
$nbtotalofrecords = $db->num_rows($result);
}
//print $sql;
$sql .= " ROUND(SUM(IF(MONTH(ic.date_acq)=1,ic.montant_ttc,0)),2) AS 'Janvier',";
$sql .= " ROUND(SUM(IF(MONTH(ic.date_acq)=2,ic.montant_ttc,0)),2) AS 'Fevrier',";
$sql .= " ROUND(SUM(IF(MONTH(ic.date_acq)=3,ic.montant_ttc,0)),2) AS 'Mars',";
$sql .= " ROUND(SUM(IF(MONTH(ic.date_acq)=4,ic.montant_ttc,0)),2) AS 'Avril',";
$sql .= " ROUND(SUM(IF(MONTH(ic.date_acq)=5,ic.montant_ttc,0)),2) AS 'Mai',";
$sql .= " ROUND(SUM(IF(MONTH(ic.date_acq)=6,ic.montant_ttc,0)),2) AS 'Juin',";
$sql .= " ROUND(SUM(IF(MONTH(ic.date_acq)=7,ic.montant_ttc,0)),2) AS 'Juillet',";
$sql .= " ROUND(SUM(IF(MONTH(ic.date_acq)=8,ic.montant_ttc,0)),2) AS 'Aout',";
$sql .= " ROUND(SUM(IF(MONTH(ic.date_acq)=9,ic.montant_ttc,0)),2) AS 'Septembre',";
$sql .= " ROUND(SUM(IF(MONTH(ic.date_acq)=10,ic.montant_ttc,0)),2) AS 'Octobre',";
$sql .= " ROUND(SUM(IF(MONTH(ic.date_acq)=11,ic.montant_ttc,0)),2) AS 'Novembre',";
$sql .= " ROUND(SUM(IF(MONTH(ic.date_acq)=12,ic.montant_ttc,0)),2) AS 'Decembre',";
$sql .= " ROUND(SUM(ic.montant_ttc),2) as 'Total'";
$sql .= " FROM " . MAIN_DB_PREFIX . "immo_charge as ic";
$sql .= " , " . MAIN_DB_PREFIX . "immo_typologie as it";
$sql .= " WHERE ic.date_acq >= '" . $db->idate(dol_get_first_day($y, 1, false)) . "'";
$sql .= " AND ic.date_acq <= '" . $db->idate(dol_get_last_day($y, 12, false)) . "'";
$sql .= " AND ic.type = it.rowid ";
if ($user->id != 1) {
$sql .= " AND ic.proprietaire_id=" . $user->id;
}
$resql = $db->query($sql);
if ($resql) {
$i = 0;
$num = $db->num_rows($resql);
while ($i < $num) {
$row = $db->fetch_row($resql);
print '<tr><td>' . $row[0] . '</td>';
print '<td align="right">' . $row[1] . '</td>';
print '<td align="right">' . $row[2] . '</td>';
print '<td align="right">' . $row[3] . '</td>';
}
}
if ($q == 1) {
$date_start = dol_get_first_day($year_start, 1, false);
$date_end = dol_get_last_day($year_start, 3, false);
}
if ($q == 2) {
$date_start = dol_get_first_day($year_start, 4, false);
$date_end = dol_get_last_day($year_start, 6, false);
}
if ($q == 3) {
$date_start = dol_get_first_day($year_start, 7, false);
$date_end = dol_get_last_day($year_start, 9, false);
}
if ($q == 4) {
$date_start = dol_get_first_day($year_start, 10, false);
$date_end = dol_get_last_day($year_start, 12, false);
}
}
$min = GETPOST("min");
if (empty($min)) {
$min = 0;
}
// Define modetax (0 or 1)
// 0=normal, 1=option vat for services is on debit
$modetax = $conf->global->TAX_MODE;
if (isset($_REQUEST["modetax"])) {
$modetax = $_REQUEST["modetax"];
}
if (empty($modetax)) {
$modetax = 0;
/**
* Return nb, amount of predefined product for year
*
* @param int $year Year to scan
* @return array Array of values
*/
function getAllByProduct($year)
{
global $user;
$sql = "SELECT product.ref, COUNT(product.ref) as nb, SUM(tl." . $this->field_line . ") as total, AVG(tl." . $this->field_line . ") as avg";
$sql .= " FROM " . $this->from . ", " . $this->from_line . ", " . MAIN_DB_PREFIX . "product as product";
if (!$user->rights->societe->client->voir && !$user->socid) {
$sql .= ", " . MAIN_DB_PREFIX . "societe_commerciaux as sc";
}
$sql .= " WHERE " . $this->where;
$sql .= " AND p.rowid = tl.fk_propal AND tl.fk_product = product.rowid";
$sql .= " AND " . $this->field_date . " BETWEEN '" . $this->db->idate(dol_get_first_day($year, 1, false)) . "' AND '" . $this->db->idate(dol_get_last_day($year, 12, false)) . "'";
$sql .= " GROUP BY product.ref";
$sql .= $this->db->order('nb', 'DESC');
//$sql.= $this->db->plimit(20);
return $this->_getAllByProduct($sql);
}