/**
* Load object in memory from the database
*
* @param string $sortorder order
* @param string $sortfield field
* @param int $limit page
* @param int $offset Offset results
* @param array $filter output
*
* @return int <0 if KO, >0 if OK
*/
function fetch_all($sortorder, $sortfield, $limit, $offset, $filter = array())
{
global $langs;
$sql = "SELECT";
$sql .= " t.rowid,";
$sql .= " t.ref,";
$sql .= " t.ref_ext,";
$sql .= " t.ref_int,";
$sql .= " t.fk_c_status,";
$sql .= " t.fk_c_type,";
$sql .= " t.fk_soc,";
$sql .= " t.date_closure,";
$sql .= " t.amount_prosp,";
$sql .= " t.fk_user_resp,";
$sql .= " t.description,";
$sql .= " t.note_private,";
$sql .= " t.note_public,";
$sql .= " t.fk_user_author,";
$sql .= " t.datec,";
$sql .= " t.fk_user_mod,";
$sql .= " t.tms";
$sql .= " FROM " . MAIN_DB_PREFIX . "lead as t";
$sql .= " LEFT JOIN " . MAIN_DB_PREFIX . "societe as so ON so.rowid=t.fk_soc";
$sql .= " LEFT JOIN " . MAIN_DB_PREFIX . "user as usr ON usr.rowid=t.fk_user_resp";
$sql .= " LEFT JOIN " . MAIN_DB_PREFIX . "c_lead_status as leadsta ON leadsta.rowid=t.fk_c_status";
$sql .= " LEFT JOIN " . MAIN_DB_PREFIX . "c_lead_type as leadtype ON leadtype.rowid=t.fk_c_type";
$sql .= " WHERE t.entity IN (" . getEntity('lead') . ")";
if (is_array($filter)) {
foreach ($filter as $key => $value) {
if ($key == 't.fk_c_status' || $key == 't.rowid' || $key == 'so.rowid' || $key == 't.fk_c_type' || $key == 't.fk_user_resp') {
$sql .= ' AND ' . $key . ' = ' . $value;
} elseif ($key == 't.date_closure<') {
// To allow $filter['YEAR(s.dated)']=>$year
$sql .= " AND t.date_closure<='" . $this->db->idate($value) . "'";
} elseif (strpos($key, 'date')) {
// To allow $filter['YEAR(s.dated)']=>$year
$sql .= ' AND ' . $key . ' = \'' . $value . '\'';
} elseif ($key == 't.fk_c_status !IN') {
$sql .= ' AND t.fk_c_status NOT IN (' . $value . ')';
} elseif ($key == 't.rowid !IN') {
$sql .= ' AND t.rowid NOT IN (' . $value . ')';
} else {
$sql .= ' AND ' . $key . ' LIKE \'%' . $this->db->escape($value) . '%\'';
}
}
}
if (!empty($sortfield)) {
$sql .= " ORDER BY " . $sortfield . ' ' . $sortorder;
}
if (!empty($limit)) {
$sql .= ' ' . $this->db->plimit($limit + 1, $offset);
}
dol_syslog(get_class($this) . "::fetch_all sql=" . $sql, LOG_DEBUG);
$resql = $this->db->query($sql);
if ($resql) {
$this->lines = array();
$num = $this->db->num_rows($resql);
while ($obj = $this->db->fetch_object($resql)) {
$line = new Lead($this->db, 1);
$line->id = $obj->rowid;
$line->ref = $obj->ref;
$line->ref_ext = $obj->ref_ext;
$line->ref_int = $obj->ref_int;
$line->fk_c_status = $obj->fk_c_status;
$line->fk_c_type = $obj->fk_c_type;
$line->fk_soc = $obj->fk_soc;
$line->date_closure = $this->db->jdate($obj->date_closure);
$line->amount_prosp = $obj->amount_prosp;
$line->fk_user_resp = $obj->fk_user_resp;
$line->description = $obj->description;
$line->note_private = $obj->note_private;
$line->note_public = $obj->note_public;
$line->fk_user_author = $obj->fk_user_author;
$line->datec = $this->db->jdate($obj->datec);
$line->fk_user_mod = $obj->fk_user_mod;
$line->tms = $this->db->jdate($obj->tms);
$line->status_label = $this->status[$line->fk_c_status];
$line->type_label = $this->type[$line->fk_c_type];
$extrafields = new ExtraFields($this->db);
$extralabels = $extrafields->fetch_name_optionals_label($this->table_element, true);
if (count($extralabels) > 0) {
$line->fetch_optionals($line->id, $extralabels);
}
$this->lines[] = $line;
}
$this->db->free($resql);
return $num;
} else {
$this->error = "Error " . $this->db->lasterror();
dol_syslog(get_class($this) . "::fetch_all " . $this->error, LOG_ERR);
return -1;
}
}