PHP natural_search函数代码示例

$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 = " . $user->id;
}
if ($socid) {
    $sql .= " 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);

    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 = " . $search_sale;
}
if ($search_user > 0) {
    $sql .= " 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=" . $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;

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 = " . $search_sale;
}
if ($search_user > 0) {
    $sql .= " 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;