Ejemplo n.º 1
0
 /**
  * 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;
 }
Ejemplo n.º 2
0
 /**
  *  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 . "&nbsp;" . $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") . "&nbsp;" . $textHead);
     $this->info_box_contents[$i][] = array('td' => 'align="right" ', 'text' => number_format($totalnb, 0, ',', ' ') . "&nbsp;" . $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;
        print "<br>\n";
Ejemplo n.º 4
0
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);
Ejemplo n.º 5
0
    }
}
// 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>';
    // Employee
Ejemplo n.º 6
0
    } 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";
}
Ejemplo n.º 7
0
} 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);
 /**
  *	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);
 }
Ejemplo n.º 9
0
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>';
Ejemplo n.º 10
0
 /**
  *		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;
 }
Ejemplo n.º 11
0
/**
 * 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>';
    }
}
Ejemplo n.º 12
0
    }
    $j++;
}
// Payment Salary
if ($conf->salaries->enabled) {
    if (empty($_GET["mode"]) || $_GET["mode"] != 'sconly') {
        $sal = new PaymentSalary($db);
        print '<br>';
        print load_fiche_titre($langs->trans("SalariesPayments") . ($year ? ' (' . $langs->trans("Year") . ' ' . $year . ')' : ''), '', '');
        $sql = "SELECT s.rowid, s.amount, s.label, s.datep as datep, s.datev as datev, s.datesp, s.dateep, s.salary, u.salary as current_salary";
        $sql .= " FROM " . MAIN_DB_PREFIX . "payment_salary as s, " . MAIN_DB_PREFIX . "user as u";
        $sql .= " WHERE s.entity IN (" . getEntity('user', 1) . ")";
        $sql .= " AND u.rowid = s.fk_user";
        if ($year > 0) {
            $sql .= " AND (s.datesp between '" . $db->idate(dol_get_first_day($year, 1, false)) . "' AND '" . $db->idate(dol_get_last_day($year, 12, false)) . "'";
            $sql .= " OR s.dateep between '" . $db->idate(dol_get_first_day($year, 1, false)) . "' AND '" . $db->idate(dol_get_last_day($year, 12, false)) . "')";
        }
        if (preg_match('/^s\\./', $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"], "s.dateep", "", $param, 'width="140px"', $sortfield, $sortorder);
            print_liste_field_titre($langs->trans("Label"), $_SERVER["PHP_SELF"], "s.label", "", $param, '', $sortfield, $sortorder);
            print_liste_field_titre($langs->trans("ExpectedToPay"), $_SERVER["PHP_SELF"], "s.amount", "", $param, 'align="right"', $sortfield, $sortorder);
            print_liste_field_titre($langs->trans("RefPayment"), $_SERVER["PHP_SELF"], "s.rowid", "", $param, '', $sortfield, $sortorder);
Ejemplo n.º 13
0
        $sql .= " AND f.datef BETWEEN '" . $db->idate(dol_get_first_day($year, 1, false)) . "' AND '" . $db->idate(dol_get_last_day($year, 12, false)) . "'";
    }
}
if ($month_lim > 0) {
    if ($year_lim > 0 && empty($day_lim)) {
        $sql .= " AND f.date_lim_reglement BETWEEN '" . $db->idate(dol_get_first_day($year_lim, $month_lim, false)) . "' AND '" . $db->idate(dol_get_last_day($year_lim, $month_lim, false)) . "'";
    } else {
        if ($year_lim > 0 && !empty($day_lim)) {
            $sql .= " AND f.date_lim_reglement BETWEEN '" . $db->idate(dol_mktime(0, 0, 0, $month_lim, $day_lim, $year_lim)) . "' AND '" . $db->idate(dol_mktime(23, 59, 59, $month_lim, $day_lim, $year_lim)) . "'";
        } else {
            $sql .= " AND date_format(f.date_lim_reglement, '%m') = '" . $month_lim . "'";
        }
    }
} else {
    if ($year_lim > 0) {
        $sql .= " AND f.date_lim_reglement BETWEEN '" . $db->idate(dol_get_first_day($year_lim, 1, false)) . "' AND '" . $db->idate(dol_get_last_day($year_lim, 12, false)) . "'";
    }
}
if ($option == 'late') {
    $sql .= " AND f.date_lim_reglement < '" . $db->idate(dol_now() - $conf->facture->client->warning_delay) . "'";
}
if ($filter == 'paye:0') {
    $sql .= " AND f.fk_statut = 1";
}
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='facture' AND tc.source='internal' AND ec.element_id = f.rowid AND ec.fk_socpeople = " . $search_user;
}
if (!$sall) {
Ejemplo n.º 14
0
$sql .= "  ROUND(SUM(IF(MONTH(bk.doc_date)=1,bk.montant,0)),2) AS 'Janvier',";
$sql .= "  ROUND(SUM(IF(MONTH(bk.doc_date)=2,bk.montant,0)),2) AS 'Fevrier',";
$sql .= "  ROUND(SUM(IF(MONTH(bk.doc_date)=3,bk.montant,0)),2) AS 'Mars',";
$sql .= "  ROUND(SUM(IF(MONTH(bk.doc_date)=4,bk.montant,0)),2) AS 'Avril',";
$sql .= "  ROUND(SUM(IF(MONTH(bk.doc_date)=5,bk.montant,0)),2) AS 'Mai',";
$sql .= "  ROUND(SUM(IF(MONTH(bk.doc_date)=6,bk.montant,0)),2) AS 'Juin',";
$sql .= "  ROUND(SUM(IF(MONTH(bk.doc_date)=7,bk.montant,0)),2) AS 'Juillet',";
$sql .= "  ROUND(SUM(IF(MONTH(bk.doc_date)=8,bk.montant,0)),2) AS 'Aout',";
$sql .= "  ROUND(SUM(IF(MONTH(bk.doc_date)=9,bk.montant,0)),2) AS 'Septembre',";
$sql .= "  ROUND(SUM(IF(MONTH(bk.doc_date)=10,bk.montant,0)),2) AS 'Octobre',";
$sql .= "  ROUND(SUM(IF(MONTH(bk.doc_date)=11,bk.montant,0)),2) AS 'Novembre',";
$sql .= "  ROUND(SUM(IF(MONTH(bk.doc_date)=12,bk.montant,0)),2) AS 'Decembre',";
$sql .= "  ROUND(SUM(bk.montant),2) as 'Total'";
$sql .= " FROM " . MAIN_DB_PREFIX . "accounting_bookkeeping as bk";
$sql .= " WHERE bk.doc_date >= '" . $db->idate(dol_get_first_day($y, 1, false)) . "'";
$sql .= "  AND bk.doc_date <= '" . $db->idate(dol_get_last_day($y, 12, false)) . "'";
$sql .= " GROUP BY bk.numero_compte";
$resql = $db->query($sql);
if ($resql) {
    $i = 0;
    $num = $db->num_rows($resql);
    while ($i < $num) {
        $row = $db->fetch_row($resql);
        print '<tr><td width="14%">' . length_accountg($row[0]) . '</td>';
        print '<td align="right" width="6.5%">' . price($row[1]) . '</td>';
        print '<td align="right" width="6.5%">' . price($row[2]) . '</td>';
        print '<td align="right" width="6.5%">' . price($row[3]) . '</td>';
        print '<td align="right" width="6.5%">' . price($row[4]) . '</td>';
        print '<td align="right" width="6.5%">' . price($row[5]) . '</td>';
        print '<td align="right" width="6.5%">' . price($row[6]) . '</td>';
        print '<td align="right" width="6.5%">' . price($row[7]) . '</td>';
Ejemplo n.º 15
0
$sql .= "  ROUND(SUM(IF(MONTH(lo.echeance)=2,lo.loy,0)),2) AS 'Fevrier',";
$sql .= "  ROUND(SUM(IF(MONTH(lo.echeance)=3,lo.loy,0)),2) AS 'Mars',";
$sql .= "  ROUND(SUM(IF(MONTH(lo.echeance)=4,lo.loy,0)),2) AS 'Avril',";
$sql .= "  ROUND(SUM(IF(MONTH(lo.echeance)=5,lo.loy,0)),2) AS 'Mai',";
$sql .= "  ROUND(SUM(IF(MONTH(lo.echeance)=6,lo.loy,0)),2) AS 'Juin',";
$sql .= "  ROUND(SUM(IF(MONTH(lo.echeance)=7,lo.loy,0)),2) AS 'Juillet',";
$sql .= "  ROUND(SUM(IF(MONTH(lo.echeance)=8,lo.loy,0)),2) AS 'Aout',";
$sql .= "  ROUND(SUM(IF(MONTH(lo.echeance)=9,lo.loy,0)),2) AS 'Septembre',";
$sql .= "  ROUND(SUM(IF(MONTH(lo.echeance)=10,lo.loy,0)),2) AS 'Octobre',";
$sql .= "  ROUND(SUM(IF(MONTH(lo.echeance)=11,lo.loy,0)),2) AS 'Novembre',";
$sql .= "  ROUND(SUM(IF(MONTH(lo.echeance)=12,lo.loy,0)),2) AS 'Decembre',";
$sql .= "  ROUND(SUM(lo.loy),2) as 'Total'";
$sql .= " FROM " . MAIN_DB_PREFIX . "immo_loyer as lo";
$sql .= " , " . MAIN_DB_PREFIX . "immo_local as ll";
$sql .= " WHERE lo.echeance >= '" . $db->idate(dol_get_first_day($y, 1, false)) . "'";
$sql .= "  AND lo.echeance <= '" . $db->idate(dol_get_last_day($y, 12, false)) . "'";
$sql .= "  AND lo.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>';
        print '<td align="right">' . $row[4] . '</td>';
Ejemplo n.º 16
0
        $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 != '') {
Ejemplo n.º 17
0
$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>';
        print '<td align="right">' . $row[4] . '</td>';
 /**
  *	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;
 }
Ejemplo n.º 19
0
            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
Ejemplo n.º 20
0
	/**
	 * 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;
	}
Ejemplo n.º 21
0
$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>';
        print '<td align="right">' . $row[4] . '</td>';
Ejemplo n.º 22
0
/**
 *  \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;
}
Ejemplo n.º 23
0
$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>';
        print '<td align="right">' . price($row[3]) . '</td>';
Ejemplo n.º 24
0
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) . '">&lt;</a><li class="pagination"><a href="">' . $langs->trans("Year") . ' ' . $year . '</a></li><li class="pagination"><a href="' . $_SERVER["PHP_SELF"] . '?year=' . ($year + 1) . '">&gt;</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>';
Ejemplo n.º 25
0
/**
 *  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;
}
Ejemplo n.º 26
0
	/**
	 *	\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);
	}
Ejemplo n.º 27
0
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;
Ejemplo n.º 28
0
    }
}
// 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) {
Ejemplo n.º 29
0
    }
    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 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);
     }
 }