$sql .= " LEFT JOIN " . MAIN_DB_PREFIX . "product_stock as ps ON e.rowid = ps.fk_entrepot";
$sql .= " LEFT JOIN " . MAIN_DB_PREFIX . "product as p ON ps.fk_product = p.rowid";
$sql .= " WHERE e.entity IN (" . getEntity('stock', 1) . ")";
if ($search_ref) {
$sql .= natural_search("e.label", $search_ref);
}
// ref
if ($search_label) {
$sql .= natural_search("e.lieu", $search_label);
}
// label
if ($search_status != '' && $search_status >= 0) {
$sql .= " AND e.statut = " . $search_status;
}
if ($sall) {
$sql .= natural_search(array('e.label', 'e.description', 'e.lieu', 'e.address', 'e.town'), $sall);
}
$sql .= " GROUP BY e.rowid, e.label, e.statut, e.lieu, e.address, e.zip, e.town, e.fk_pays";
$sql .= $db->order($sortfield, $sortorder);
$sql .= $db->plimit($limit + 1, $offset);
$result = $db->query($sql);
if ($result) {
$num = $db->num_rows($result);
$i = 0;
$help_url = 'EN:Module_Stocks_En|FR:Module_Stock|ES:Módulo_Stocks';
llxHeader("", $langs->trans("ListOfWarehouses"), $help_url);
print_barre_liste($langs->trans("ListOfWarehouses"), $page, $_SERVER["PHP_SELF"], "", $sortfield, $sortorder, '', $num);
print '<form action="' . $_SERVER["PHP_SELF"] . '" method="post" name="formulaire">';
print '<input type="hidden" name="token" value="' . $_SESSION['newtoken'] . '">';
print '<input type="hidden" name="action" value="list">';
print '<input type="hidden" name="sortfield" value="' . $sortfield . '">';
$sql = "SELECT d.rowid, d.datedon, d.firstname, d.lastname, d.societe,";
$sql .= " d.amount, d.fk_statut as statut, ";
$sql .= " p.rowid as pid, p.ref, p.title, p.public";
$sql .= " FROM " . MAIN_DB_PREFIX . "don as d LEFT JOIN " . MAIN_DB_PREFIX . "projet AS p";
$sql .= " ON p.rowid = d.fk_projet WHERE 1 = 1";
if ($statut >= 0) {
$sql .= " AND d.fk_statut = " . $statut;
}
if (trim($search_ref) != '') {
$sql .= ' AND d.rowid LIKE \'%' . $db->escape(trim($search_ref)) . '%\'';
}
if (trim($search_company) != '') {
$sql .= natural_search('d.societe', $search_company);
}
if (trim($search_name) != '') {
$sql .= natural_search(array('d.lastname', 'd.firstname'), $search_name);
}
if ($search_amount) {
$sql .= " AND d.amount='" . $db->escape(price2num(trim($search_amount))) . "'";
}
$sql .= $db->order($sortfield, $sortorder);
$sql .= $db->plimit($limit + 1, $offset);
$resql = $db->query($sql);
if ($resql) {
$num = $db->num_rows($resql);
$i = 0;
$param = "&statut={$statut}&sortorder={$sortorder}&sortfield={$sortfield}";
if ($statut >= 0) {
$donationstatic->statut = $statut;
$label = $donationstatic->getLibStatut(0);
print_barre_liste($label, $page, $_SERVER["PHP_SELF"], $param, '', '', '', $num);
$sql .= natural_search('p.ref', $search_ref);
}
if ($search_refcustomer) {
$sql .= natural_search('p.ref_client', $search_refcustomer);
}
if ($search_societe) {
$sql .= natural_search('s.nom', $search_societe);
}
if ($search_author) {
$sql .= " AND u.login LIKE '%" . $db->escape(trim($search_author)) . "%'";
}
if ($search_montant_ht != '') {
$sql .= natural_search("p.total_ht", $search_montant_ht, 1);
}
if ($sall) {
$sql .= natural_search(array('s.nom', 'p.note_private', 'p.note_public', 'pd.description'), $sall);
}
if ($search_product_category > 0) {
$sql .= " AND cp.fk_categorie = " . $search_product_category;
}
if ($socid > 0) {
$sql .= ' AND s.rowid = ' . $socid;
}
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)) {
// No need to check company, as filtering of projects must be done by getProjectsAuthorizedForUser
//if ($socid || ! $user->rights->societe->client->voir) $sql.= " AND (p.fk_soc IS NULL OR p.fk_soc = 0 OR p.fk_soc = ".$socid.")";
if ($socid) {
$sql .= " AND (p.fk_soc IS NULL OR p.fk_soc = 0 OR p.fk_soc = " . $socid . ")";
}
if ($search_ref) {
$sql .= natural_search('p.ref', $search_ref);
}
if ($search_label) {
$sql .= natural_search('p.title', $search_label);
}
if ($search_societe) {
$sql .= natural_search('s.nom', $search_societe);
}
if ($search_all) {
$sql .= natural_search(array('p.ref', 'p.title', 's.nom'), $search_all);
}
$sql .= $db->order($sortfield, $sortorder);
$sql .= $db->plimit($conf->liste_limit + 1, $offset);
dol_syslog("list allowed project", LOG_DEBUG);
$resql = $db->query($sql);
if ($resql) {
$var = true;
$num = $db->num_rows($resql);
$i = 0;
$text = $langs->trans("Projects");
if ($mine) {
$text = $langs->trans('MyProjects');
}
print_barre_liste($text, $page, $_SERVER["PHP_SELF"], "", $sortfield, $sortorder, "", $num);
// Show description of content
$sql .= " LEFT JOIN " . MAIN_DB_PREFIX . "product_lang as pl ON pl.fk_product = p.rowid AND lang='" . $current_lang . "'";
}
$sql .= ' WHERE p.entity IN (' . getEntity('product', 1) . ')';
if ($key != "") {
// For natural search
$params = array('p.ref', 'p.label', 'p.description', 'p.note');
// multilang
if (!empty($conf->global->MAIN_MULTILANGS)) {
$params[] = 'pl.label';
$params[] = 'pl.description';
$params[] = 'pl.note';
}
if (!empty($conf->barcode->enabled)) {
$params[] = 'p.barcode';
}
$sql .= natural_search($params, $key);
}
if (!empty($conf->categorie->enabled) && !empty($parent) && $parent != -1) {
$sql .= " AND cp.fk_categorie ='" . $db->escape($parent) . "'";
}
$sql .= " ORDER BY p.ref ASC";
$resql = $db->query($sql);
}
//print $sql;
llxHeader("", "", $langs->trans("CardProduct" . $object->type));
$head = product_prepare_head($object);
$titre = $langs->trans("CardProduct" . $object->type);
$picto = $object->type == Product::TYPE_SERVICE ? 'service' : 'product';
dol_fiche_head($head, 'subproduct', $titre, 0, $picto);
if ($id > 0 || !empty($ref)) {
/*
if ($search_ref) {
if (is_numeric($search_ref)) {
$sql .= " AND (d.rowid = " . $db->escape($search_ref) . ")";
} else {
$sql .= " AND 1 = 2";
}
// Always wrong
}
if ($search_lastname) {
$sql .= natural_search(array("d.firstname", "d.lastname", "d.societe"), $search_lastname);
}
if ($search_login) {
$sql .= natural_search("d.login", $search_login);
}
if ($search_email) {
$sql .= natural_search("d.email", $search_email);
}
if ($filter == 'uptodate') {
$sql .= " AND datefin >= '" . $db->idate($now) . "'";
}
if ($filter == 'outofdate') {
$sql .= " AND (datefin IS NULL OR datefin < '" . $db->idate($now) . "')";
}
// Add where from hooks
$parameters = array();
$reshook = $hookmanager->executeHooks('printFieldListWhere', $parameters);
// Note that $action and $object may have been modified by hook
$sql .= $hookmanager->resPrint;
// Count total nb of records with no order and no limits
$nbtotalofrecords = 0;
if (empty($conf->global->MAIN_DISABLE_FULL_SCANLIST)) {
$sql .= ' AND d.fk_user IN (' . join(',', $childids) . ')';
}
if (!$user->rights->societe->client->voir && !$socid) {
$sql .= " AND sc.fk_user = "******" AND s.rowid = " . $socid;
}
if ($search_ref) {
$sql .= " AND d.rowid=" . $search_ref;
}
if ($search_name) {
$sql .= natural_search('u.lastname', $search_name);
}
if ($search_company) {
$sql .= natural_search('s.nom', $search_company);
}
// if ($search_amount) $sql.=" AND d.km='".$db->escape(price2num(trim($search_amount)))."'";
if ($month > 0) {
if ($year > 0 && empty($day)) {
$sql .= " AND d.dated 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 d.dated 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(d.dated, '%m') = '" . $month . "'";
}
}
} else {
if ($year > 0) {
$sql .= " AND d.dated BETWEEN '" . $db->idate(dol_get_first_day($year, 1, false)) . "' AND '" . $db->idate(dol_get_last_day($year, 12, false)) . "'";
// Add fields for extrafields
foreach ($extrafields->attribute_list as $key => $val) {
$sql .= ",ef." . $key . ' as options_' . $key;
}
// Add fields from hooks
$parameters = array();
$reshook = $hookmanager->executeHooks('printFieldListSelect', $parameters);
// Note that $action and $object may have been modified by hook
$sql .= $hookmanager->resPrint;
$sql .= " FROM " . MAIN_DB_PREFIX . "mytable as t";
$sql .= " WHERE 1 = 1";
if ($search_field1) {
$sql .= natural_search("field1", $search_field1);
}
if ($search_field2) {
$sql .= natural_search("field2", $search_field2);
}
// Add where from hooks
$parameters = array();
$reshook = $hookmanager->executeHooks('printFieldListWhere', $parameters);
// Note that $action and $object may have been modified by hook
$sql .= $hookmanager->resPrint;
// Count total nb of records
$nbtotalofrecords = 0;
if (empty($conf->global->MAIN_DISABLE_FULL_SCANLIST)) {
$result = $db->query($sql);
$nbtotalofrecords = $db->num_rows($result);
}
$sql .= $db->order($sortfield, $sortorder);
$sql .= $db->plimit($conf->liste_limit + 1, $offset);
dol_syslog($script_file, LOG_DEBUG);
}
// client=0, fournisseur=0 must be visible
if ($search_sale) {
$sql .= " AND sc.fk_user = "******" AND cs.fk_categorie = " . $db->escape($search_categ);
}
if ($search_nom_only) {
$sql .= natural_search("s.nom", $search_nom_only);
}
if ($search_all) {
$sql .= natural_search(array("s.nom", "s.name_alias", "s.code_client", "s.code_fournisseur", "s.email", "s.url", "s.siren", "s.siret", "s.ape", "s.idprof4", "s.idprof5", "s.idprof6"), $search_all);
}
if ($search_nom) {
$sql .= natural_search(array("s.nom", "s.name_alias", "s.code_client", "s.code_fournisseur", "s.email", "s.url", "s.siren", "s.siret", "s.ape", "s.idprof4", "s.idprof5", "s.idprof6"), $search_nom);
}
if ($search_town) {
$sql .= " AND s.town LIKE '%" . $db->escape($search_town) . "%'";
}
if ($search_idprof1) {
$sql .= " AND s.siren LIKE '%" . $db->escape($search_idprof1) . "%'";
}
if ($search_idprof2) {
$sql .= " AND s.siret LIKE '%" . $db->escape($search_idprof2) . "%'";
}
if ($search_idprof3) {
$sql .= " AND s.ape LIKE '%" . $db->escape($search_idprof3) . "%'";
}
if ($search_idprof4) {
$sql .= " AND s.idprof4 LIKE '%" . $db->escape($search_idprof4) . "%'";
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($snom)) {
$sql .= natural_search('s.nom', $snom);
}
if (!empty($sref_client)) {
$sql .= ' AND c.ref_client LIKE \'%' . $db->escape($sref_client) . '%\'';
}
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;
}
$sql .= ' ORDER BY ' . $sortfield . ' ' . $sortorder;
$nbtotalofrecords = 0;
if (empty($conf->global->MAIN_DISABLE_FULL_SCANLIST)) {
$result = $db->query($sql);
$nbtotalofrecords = $db->num_rows($result);
$sql .= natural_search(array('s.nom'), $search_thirdparty);
}
if ($search_login != '') {
$sql .= natural_search("u.login", $search_login);
}
if ($search_lastname != '') {
$sql .= natural_search("u.lastname", $search_lastname);
}
if ($search_firstname != '') {
$sql .= natural_search("u.firstname", $search_firstname);
}
if ($search_statut != '' && $search_statut >= 0) {
$sql .= " AND (u.statut=" . $search_statut . ")";
}
if ($sall) {
$sql .= natural_search(array('u.login', 'u.lastname', 'u.firstname', 'u.email', 'u.note'), $sall);
}
$sql .= $db->order($sortfield, $sortorder);
$result = $db->query($sql);
if ($result) {
$num = $db->num_rows($result);
$i = 0;
print '<form method="POST" id="searchFormList" action="' . $_SERVER["PHP_SELF"] . '">' . "\n";
$param = "search_user="******"&sall=" . $sall;
$param .= "&search_statut=" . $search_statut;
print '<table class="noborder" width="100%">';
print '<tr class="liste_titre">';
print_liste_field_titre($langs->trans("Login"), $_SERVER['PHP_SELF'], "u.login", $param, "", "", $sortfield, $sortorder);
print_liste_field_titre($langs->trans("LastName"), $_SERVER['PHP_SELF'], "u.lastname", $param, "", "", $sortfield, $sortorder);
print_liste_field_titre($langs->trans("FirstName"), $_SERVER['PHP_SELF'], "u.firstname", $param, "", "", $sortfield, $sortorder);
print_liste_field_titre($langs->trans("Company"), $_SERVER['PHP_SELF'], "u.fk_soc", $param, "", "", $sortfield, $sortorder);
$sql .= " AND d.fk_statut = " . $statut;
}
if (trim($search_ref) != '') {
$sql .= ' AND d.rowid LIKE \'%' . $db->escape(trim($search_ref)) . '%\'';
}
if (trim($search_all) != '') {
$sql .= natural_search(array_keys($fieldstosearchall), $search_all);
}
if (trim($search_company) != '') {
$sql .= natural_search('d.societe', $search_company);
}
if (trim($search_name) != '') {
$sql .= natural_search(array('d.lastname', 'd.firstname'), $search_name);
}
if ($search_amount) {
$sql .= natural_search(array('d.amount'), price2num(trim($search_amount)), 1);
}
$sql .= $db->order($sortfield, $sortorder);
$sql .= $db->plimit($limit + 1, $offset);
$resql = $db->query($sql);
if ($resql) {
$num = $db->num_rows($resql);
$i = 0;
$param = '&statut=' . $statut;
if ($page > 0) {
$param .= '&page=' . $page;
}
if ($optioncss != '') {
$param .= '&optioncss=' . $optioncss;
}
if ($statut >= 0) {
} else {
if ($year_lim > 0) {
$sql .= " AND fac.datef BETWEEN '" . $db->idate(dol_get_first_day($year_lim, 1, false)) . "' AND '" . $db->idate(dol_get_last_day($year_lim, 12, false)) . "'";
}
}
if ($search_label) {
$sql .= natural_search('fac.libelle', $search_label);
}
if ($search_company) {
$sql .= natural_search('s.nom', $search_company);
}
if ($search_amount_no_tax != '') {
$sql .= natural_search('fac.total_ht', $search_amount_no_tax, 1);
}
if ($search_amount_all_tax != '') {
$sql .= natural_search('fac.total_ttc', $search_amount_all_tax, 1);
}
if ($search_status != '') {
$sql .= " AND fac.fk_statut = " . $search_status;
}
$nbtotalofrecords = 0;
if (empty($conf->global->MAIN_DISABLE_FULL_SCANLIST)) {
$result = $db->query($sql);
$nbtotalofrecords = $db->num_rows($result);
}
$sql .= $db->order($sortfield, $sortorder);
$sql .= $db->plimit($limit + 1, $offset);
$resql = $db->query($sql);
if ($resql) {
$num = $db->num_rows($resql);
$i = 0;
}
// Search period criteria
if (dol_strlen($search_dt_start) > 0) {
$sql .= " AND b.dateo >= '" . $db->idate($search_dt_start) . "'";
}
if (dol_strlen($search_dt_end) > 0) {
$sql .= " AND b.dateo <= '" . $db->idate($search_dt_end) . "'";
}
// Search criteria amount
$debit = price2num(str_replace('-', '', $debit));
$credit = price2num(str_replace('-', '', $credit));
if ($debit) {
$sql .= natural_search('- b.amount', $debit, 1);
}
if ($credit) {
$sql .= natural_search('b.amount', $credit, 1);
}
$sql .= $db->order($sortfield, $sortorder);
$nbtotalofrecords = 0;
if (empty($conf->global->MAIN_DISABLE_FULL_SCANLIST)) {
$result = $db->query($sql);
$nbtotalofrecords = $db->num_rows($result);
}
$sql .= $db->plimit($limit + 1, $offset);
//print $sql;
dol_syslog('compta/bank/search.php::', LOG_DEBUG);
$resql = $db->query($sql);
if ($resql) {
$var = True;
$num = $db->num_rows($resql);
$i = 0;
$sql .= " AND cs.fk_categorie IS NULL";
}
if ($search_company) {
$sql .= natural_search(array('s.nom', 's.name_alias'), $search_company);
}
if ($search_zipcode) {
$sql .= natural_search("s.zip", $search_zipcode);
}
if ($search_town) {
$sql .= natural_search('s.town', $search_town);
}
if ($search_code) {
$sql .= natural_search("s.code_client", $search_code);
}
if ($search_compta) {
$sql .= natural_search("s.code_compta", $search_compta);
}
if ($search_status != '') {
$sql .= " AND s.status = " . $db->escape($search_status);
}
if ($search_sale > 0) {
$sql .= " AND sc.fk_user = " . $search_sale;
}
// Add where from hooks
$parameters = array();
$reshook = $hookmanager->executeHooks('printFieldListWhere', $parameters);
// Note that $action and $object may have been modified by hook
$sql .= $hookmanager->resPrint;
// Count total nb of records
$nbtotalofrecords = 0;
if (empty($conf->global->MAIN_DISABLE_FULL_SCANLIST)) {
if ($socid) {
$sql .= " AND s.rowid = " . $socid;
}
if ($socname) {
$sql .= natural_search('s.nom', $socname);
$sortfield = "s.nom";
$sortorder = "ASC";
}
if ($search_name) {
$sql .= natural_search('s.nom', $search_name);
}
if ($search_zipcode) {
$sql .= " AND s.zip LIKE '" . $db->escape($search_zipcode) . "%'";
}
if ($search_town) {
$sql .= natural_search('s.town', $search_town);
}
if ($search_supplier_code) {
$sql .= " AND s.code_fournisseur LIKE '%" . $db->escape($search_supplier_code) . "%'";
}
if ($search_supplier_accounting) {
$sql .= " AND s.code_compta_fournisseur LIKE '%" . $db->escape($search_supplier_accounting) . "%'";
}
if ($search_datec) {
$sql .= " AND s.datec LIKE '%" . $db->escape($search_datec) . "%'";
}
if ($catid > 0) {
$sql .= " AND cf.fk_categorie = " . $catid;
}
if ($catid == -2) {
$sql .= " AND cf.fk_categorie IS NULL";
}
}
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 != '') {
$sql .= natural_search('c.total_ht', $search_total_ht, 1);
}
$sql .= $db->order($sortfield, $sortorder);
$nbtotalofrecords = 0;
if (empty($conf->global->MAIN_DISABLE_FULL_SCANLIST)) {
$result = $db->query($sql);
$nbtotalofrecords = $db->num_rows($result);
}
$sql .= $db->plimit($limit + 1, $offset);
//print $sql;
$resql = $db->query($sql);
if ($resql) {
if ($socid) {
$soc = new Societe($db);
$soc->fetch($socid);
$title = $langs->trans('ListOfOrders') . ' - ' . $soc->name;
$sql .= " AND p.fk_soc IS NULL";
} else {
if ($type == "f") {
$sql .= " AND s.fournisseur = 1";
} else {
if ($type == "c") {
$sql .= " AND s.client IN (1, 3)";
} else {
if ($type == "p") {
$sql .= " AND s.client IN (2, 3)";
}
}
}
}
if ($sall) {
$sql .= natural_search(array('p.lastname', 'p.firstname', 'p.email', 's.nom'), $sall);
}
if (!empty($socid)) {
$sql .= " AND s.rowid = " . $socid;
}
// Count total nb of records
$nbtotalofrecords = 0;
if (empty($conf->global->MAIN_DISABLE_FULL_SCANLIST)) {
$result = $db->query($sql);
$nbtotalofrecords = $db->num_rows($result);
}
// Add order and limit
if ($view == "recent") {
$sql .= " ORDER BY p.datec DESC ";
$sql .= " " . $db->plimit($conf->liste_limit + 1, $offset);
} else {
$sql .= ", " . $db->decrypt('name') . " as name";
$sql .= ", " . $db->decrypt('value') . " as value";
$sql .= ", type";
$sql .= ", note";
$sql .= ", entity";
$sql .= " FROM " . MAIN_DB_PREFIX . "const";
$sql .= " WHERE entity IN (" . $user->entity . "," . $conf->entity . ")";
if ((empty($user->entity) || $user->admin) && $debug) {
} else {
if (!GETPOST('visible') || GETPOST('visible') != 'all') {
$sql .= " AND visible = 1";
}
}
// We must always have this. Otherwise, array is too large and submitting data fails due to apache POST or GET limits
if (GETPOST('name')) {
$sql .= natural_search("name", GETPOST('name'));
}
$sql .= " ORDER BY entity, name ASC";
dol_syslog("Const::listConstant", LOG_DEBUG);
$result = $db->query($sql);
if ($result) {
$num = $db->num_rows($result);
$i = 0;
$var = false;
while ($i < $num) {
$obj = $db->fetch_object($result);
$var = !$var;
print "\n";
print '<tr ' . $bc[$var] . '><td>' . $obj->name . '</td>' . "\n";
// Value
print '<td>';
$sql .= " AND s.rowid = sc.fk_soc AND sc.fk_user = "******" AND u.login LIKE '%" . $db->escape($search_user) . "%'";
}
if ($search_ttc) {
$sql .= " AND cf.total_ttc = '" . $db->escape(price2num($search_ttc)) . "'";
}
if ($sall) {
$sql .= natural_search(array('cf.ref', 'cf.note_public', 'cf.note_private'), $sall);
}
if ($socid) {
$sql .= " AND s.rowid = " . $socid;
}
//Required triple check because statut=0 means draft filter
if (GETPOST('statut', 'int') !== '') {
$sql .= " AND cf.fk_statut IN (" . GETPOST('statut') . ")";
}
if ($search_refsupp) {
$sql .= " AND (cf.ref_supplier LIKE '%" . $db->escape($search_refsupp) . "%')";
}
if ($search_status >= 0) {
if ($search_status == 6 || $search_status == 7) {
$sql .= " AND cf.fk_statut IN (6,7)";
} else {
}
}
// Get list of project id allowed to user (in a string list separated by coma)
$projectsListId = $projectstatic->getProjectsAuthorizedForUser($user, $mine, 1, $socid);
//var_dump($projectsListId);
// Get list of tasks in tasksarray and taskarrayfiltered
// We need all tasks (even not limited to a user because a task assigned to a user can have a parent that is not assigned to him and we need such parents).
$morewherefilter = '';
if ($search_all) {
$morewherefilter .= natural_search(array_keys($fieldstosearchall), $search_all);
}
if ($search_task_ref) {
$morewherefilter .= natural_search('t.ref', $search_task_ref);
}
if ($search_task_label) {
$morewherefilter .= natural_search('t.label', $search_task_label);
}
$tasksarray = $taskstatic->getTasksArray(0, 0, $projectstatic->id, $socid, 0, $search_project, $search_status, $morewherefilter, $search_project_user, 0);
// We don't use filter on task user. Because sometimes a task is assigned but not the parent one and we want to show also parent, so filtering is done during output
$tasksrole = $taskstatic->getUserRolesForProjectsOrTasks(0, $tuser->id ? $tuser : null, $projectstatic->id, 0, $search_status);
// We load also tasks limited to a particular user
print '<form method="POST" action="' . $_SERVER["PHP_SELF"] . '">';
if ($optioncss != '') {
print '<input type="hidden" name="optioncss" value="' . $optioncss . '">';
}
print '<input type="hidden" name="token" value="' . $_SESSION['newtoken'] . '">';
print '<input type="hidden" name="action" value="list">';
print '<input type="hidden" name="sortfield" value="' . $sortfield . '">';
print '<input type="hidden" name="sortorder" value="' . $sortorder . '">';
print '<input type="hidden" name="type" value="' . $type . '">';
if ($search_all) {
$sql .= " AND s.rowid = sc.fk_soc AND sc.fk_user = "******" AND s.rowid = sc.fk_soc AND sc.fk_user = "******" GROUP BY c.rowid, c.ref, c.datec, c.date_contrat, c.statut, c.ref_supplier, s.nom, s.rowid";
$sql .= $db->order($sortfield, $sortorder);
$sql .= $db->plimit($conf->liste_limit + 1, $offset);
$resql = $db->query($sql);
if ($resql) {
$num = $db->num_rows($resql);
$i = 0;
print_barre_liste($langs->trans("ListOfContracts"), $page, $_SERVER["PHP_SELF"], '&search_contract=' . $search_contract . '&search_name=' . $search_name, $sortfield, $sortorder, '', $num, $totalnboflines, 'title_commercial.png');
print '<form method="POST" action="' . $_SERVER['PHP_SELF'] . '">';
print '<table class="liste" width="100%">';
// If the user can view prospects other than his'
$moreforfilter = '';
if ($user->rights->societe->client->voir || $socid) {
$langs->load("commercial");
$sql .= " AND p.fk_product_type <> '1'";
}
}
if ($sref) {
$sql .= natural_search('p.ref', $sref);
}
if ($sbarcode) {
$sql .= natural_search('p.barcode', $sbarcode);
}
if ($snom) {
$params = array('p.label');
// multilang
if ($conf->global->MAIN_MULTILANGS) {
$params[] = 'pl.label';
}
$sql .= natural_search($params, $snom);
}
if (isset($tosell) && dol_strlen($tosell) > 0 && $tosell != -1) {
$sql .= " AND p.tosell = " . $db->escape($tosell);
}
if (isset($tobuy) && dol_strlen($tobuy) > 0 && $tobuy != -1) {
$sql .= " AND p.tobuy = " . $db->escape($tobuy);
}
if (dol_strlen($canvas) > 0) {
$sql .= " AND p.canvas = '" . $db->escape($canvas) . "'";
}
if ($catid > 0) {
$sql .= " AND cp.fk_categorie = " . $catid;
}
if ($catid == -2) {
$sql .= " AND cp.fk_categorie IS NULL";
$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 ($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) {
$sql .= ' GROUP BY f.rowid, f.facnumber, ref_client, f.type, f.note_private, f.increment, f.total, f.tva, f.total_ttc,';
$sql .= ' f.datef, f.date_lim_reglement,';
$sql .= ' f.paye, f.fk_statut,';
$sql .= ' s.nom, s.rowid, s.code_client, s.client';
} else {
$sql .= natural_search(array('s.nom', 'f.facnumber', 'f.note_public', 'fd.description'), $sall);
}
$sql .= ' ORDER BY ';
$listfield = explode(',', $sortfield);
foreach ($listfield as $key => $value) {
$sql .= $listfield[$key] . ' ' . $sortorder . ',';
}
$sql .= ' f.rowid DESC ';
$nbtotalofrecords = 0;
if (empty($conf->global->MAIN_DISABLE_FULL_SCANLIST)) {
$result = $db->query($sql);
$nbtotalofrecords = $db->num_rows($result);
}
$sql .= $db->plimit($limit + 1, $offset);
//print $sql;
$resql = $db->query($sql);
}
if ($search_status != '' && $search_status >= 0) {
$sql .= ' AND f.fk_statut = ' . $search_status;
}
if (!$user->rights->societe->client->voir && empty($socid)) {
$sql .= " AND s.rowid = sc.fk_soc AND sc.fk_user = "******" AND s.rowid = " . $socid;
}
if ($sall) {
$arraytosearch = array('f.ref', 'f.description', 's.nom');
if (empty($conf->global->FICHINTER_DISABLE_DETAILS)) {
$arraytosearch = array('f.ref', 'f.description', 's.nom', 'fd.description');
}
$sql .= natural_search($arraytosearch, $sall);
}
$sql .= $db->order($sortfield, $sortorder);
$sql .= $db->plimit($limit + 1, $offset);
//print $sql;
$result = $db->query($sql);
if ($result) {
$num = $db->num_rows($result);
$urlparam = '';
if ($socid) {
$urlparam .= "&socid=" . $socid;
}
if ($search_ref) {
$urlparam .= "&search_ref=" . urlencode($search_ref);
}
if ($search_company) {
$sql .= " LEFT JOIN " . MAIN_DB_PREFIX . "c_paiement as pst ON s.fk_typepayment = pst.id,";
$sql .= " " . MAIN_DB_PREFIX . "user as u";
$sql .= " WHERE u.rowid = s.fk_user";
$sql .= " AND s.entity = " . $conf->entity;
// Search criteria
if ($search_ref) {
$sql .= " AND s.rowid=" . $search_ref;
}
if ($search_user) {
$sql .= natural_search(array('u.login', 'u.lastname', 'u.firstname', 'u.email', 'u.note'), $search_user);
}
if ($search_label) {
$sql .= natural_search(array('s.label'), $search_label);
}
if ($search_amount) {
$sql .= natural_search("s.amount", $search_amount, 1);
}
if ($filtre) {
$filtre = str_replace(":", "=", $filtre);
$sql .= " AND " . $filtre;
}
if ($typeid) {
$sql .= " AND s.fk_typepayment=" . $typeid;
}
//$sql.= " GROUP BY u.rowid, u.lastname, u.firstname, s.rowid, s.fk_user, s.amount, s.label, s.datev, s.fk_typepayment, s.num_payment, pst.code";
$sql .= $db->order($sortfield, $sortorder);
$sql .= $db->plimit($limit + 1, $offset);
$result = $db->query($sql);
if ($result) {
$num = $db->num_rows($result);
$i = 0;
$sql .= natural_search('cf.ref', $search_ref);
}
if ($search_company) {
$sql .= natural_search('s.nom', $search_company);
}
if ($search_user) {
$sql .= " AND u.login LIKE '%" . $db->escape($search_user) . "%'";
}
if ($search_ht != '') {
$sql .= natural_search("cf.total_ht", $search_ht, 1);
}
if ($search_ttc != '') {
$sql .= natural_search("cf.total_ttc", $search_ttc, 1);
}
if ($sall) {
$sql .= natural_search(array_keys($fieldstosearchall), $sall);
}
if ($socid) {
$sql .= " AND s.rowid = " . $socid;
}
//Required triple check because statut=0 means draft filter
if (GETPOST('statut', 'alpha') !== '') {
$sql .= " AND cf.fk_statut IN (" . GETPOST('statut', 'alpha') . ")";
}
if ($billed !== '') {
$sql .= " AND cf.billed = " . $billed;
}
if ($search_refsupp) {
$sql .= " AND (cf.ref_supplier LIKE '%" . $db->escape($search_refsupp) . "%')";
}
if ($search_status != '' && $search_status >= 0) {
$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) {
if (strstr($search_status, ',')) {
$sql .= " AND d.fk_statut IN (" . $db->escape($search_status) . ")";
} else {
$sql .= " AND d.fk_statut = " . $search_status;
}
}
// RESTRICT RIGHTS
if (empty($user->rights->expensereport->readall) && empty($user->rights->expensereport->lire_tous)) {
}
if ($search_accountancy_code_sell) {
$sql .= natural_search('p.accountancy_code_buy', $search_accountancy_code_buy);
}
// Add where from extra fields
foreach ($search_array_options as $key => $val) {
$crit = $val;
$tmpkey = preg_replace('/search_options_/', '', $key);
$typ = $extrafields->attribute_type[$tmpkey];
$mode = 0;
if (in_array($typ, array('int'))) {
$mode = 1;
}
// Search on a numeric
if ($val && ($crit != '' && !in_array($typ, array('select')) || !empty($crit))) {
$sql .= natural_search('ef.' . $tmpkey, $crit, $mode);
}
}
// Add where from hooks
$parameters = array();
$reshook = $hookmanager->executeHooks('printFieldListWhere', $parameters);
// Note that $action and $object may have been modified by hook
$sql .= $hookmanager->resPrint;
$sql .= " GROUP BY p.rowid, p.ref, p.label, p.barcode, p.price, p.price_ttc, p.price_base_type,";
$sql .= " p.fk_product_type, p.duration, p.tosell, p.tobuy, p.seuil_stock_alerte, p.desiredstock,";
$sql .= ' p.datec, p.tms';
// Add fields from extrafields
foreach ($extrafields->attribute_label as $key => $val) {
$sql .= ",ef." . $key;
}
// Add fields from hooks
$sql .= " LEFT JOIN " . MAIN_DB_PREFIX . "categorie_product as cp ON cp.fk_product = p.rowid";
}
$sql .= " LEFT JOIN " . MAIN_DB_PREFIX . "product_fournisseur_price as ppf ON p.rowid = ppf.fk_product";
$sql .= " LEFT JOIN " . MAIN_DB_PREFIX . "societe as s ON ppf.fk_soc = s.rowid";
$sql .= " WHERE p.entity IN (" . getEntity('product', 1) . ")";
if ($sRefSupplier) {
$sql .= natural_search('ppf.ref_fourn', $sRefSupplier);
}
if (GETPOST('type')) {
$sql .= " AND p.fk_product_type = " . GETPOST('type', 'int');
}
if ($sref) {
$sql .= natural_search('p.ref', $sref);
}
if ($snom) {
$sql .= natural_search('p.label', $snom);
}
if ($catid) {
$sql .= " AND cp.fk_categorie = " . $catid;
}
if ($fourn_id > 0) {
$sql .= " AND ppf.fk_soc = " . $fourn_id;
}
$sql .= " ORDER BY " . $sortfield . " " . $sortorder;
$sql .= $db->plimit($limit + 1, $offset);
dol_syslog("fourn/product/liste.php: sql=" . $sql);
$resql = $db->query($sql);
if ($resql) {
$num = $db->num_rows($resql);
$i = 0;
if ($num == 1 && GETPOST("mode") == 'search') {
