/**
* Get date from $_GET and create the sql stmt for the query
* @note the query is taken in $_REQUEST
* @see Follow_Up::ShowActionList
* @return string SQL condition
*/
static function create_query($cn, $p_array = null)
{
if ($p_array == null) {
$p_array = $_GET;
}
extract($p_array);
$action_query = "";
if (isset($_REQUEST['action_query'])) {
// if a query is request build the sql stmt
$action_query = "and (ag_title ~* '" . sql_string($_REQUEST['action_query']) . "' " . "or ag_ref ='" . trim(sql_string($_REQUEST['action_query'])) . "' or ag_id in (select ag_id from action_gestion_comment where agc_comment ~* '" . trim(sql_string($_REQUEST['action_query'])) . "')" . ")";
}
$str = "";
if (isset($qcode)) {
// verify that qcode is not empty
if (strlen(trim($qcode)) != 0) {
$fiche = new Fiche($cn);
$fiche->get_by_qcode($_REQUEST['qcode']);
// if quick code not found then nothing
if ($fiche->id == 0) {
$str = ' and false ';
} else {
$str = " and (f_id_dest= " . $fiche->id . " or ag_id in (select ag_id from action_person as ap where ap.f_id=" . $fiche->id . ") )";
}
}
}
if (isset($tdoc) && $tdoc != -1) {
$action_query .= ' and dt_id = ' . sql_string($tdoc);
}
if (isset($state) && $state != -1) {
$action_query .= ' and ag_state= ' . sql_string($state);
}
if (isset($hsstate) && $hsstate != -1) {
$action_query .= ' and ag_state <> ' . sql_string($hsstate);
}
if (isset($sag_ref) && trim($sag_ref) != "") {
$query .= ' and ag_ref= \'' . sql_string($sag_ref) . "'";
}
if (isset($_GET['only_internal'])) {
$action_query .= ' and f_id_dest=0 ';
}
if (isset($date_start) && isDate($date_start) != null) {
$action_query .= " and ag_timestamp >= to_date('{$date_start}','DD.MM.YYYY')";
}
if (isset($date_end) && isDate($date_end) != null) {
$action_query .= " and ag_timestamp <= to_date('{$date_end}','DD.MM.YYYY')";
}
if (isset($ag_dest_query) && $ag_dest_query != -2) {
$action_query .= " and ((ag_dest = " . sql_string($ag_dest_query) . " and " . self::sql_security_filter($cn, "R") . ") or " . "(ag_dest = " . sql_string($ag_dest_query) . " and " . self::sql_security_filter($cn, "R") . " and " . " ag_owner='" . $_SESSION['g_user'] . "'))";
} else {
$action_query .= " and (ag_owner='" . $_SESSION['g_user'] . "' or " . self::sql_security_filter($cn, "R") . " or ag_dest=-1 )";
}
if (isNumber($ag_id) == 1 && $ag_id != 0) {
$action_query = " and ag_id= " . sql_string($ag_id);
}
if (isset($remind_date) && $remind_date != "" && isDate($remind_date) == $remind_date) {
$action_query .= " and to_date('" . sql_string($remind_date) . "','DD.MM.YYYY')<= ag_remind_date";
}
if (isset($remind_date_end) && $remind_date_end != "" && isDate($remind_date_end) == $remind_date_end) {
$action_query .= " and to_date('" . sql_string($remind_date_end) . "','DD.MM.YYYY')>= ag_remind_date";
}
if (!isset($closed_action)) {
$action_query .= " and s_status is null ";
}
if (isset($searchtag)) {
$action_query .= Follow_Up::filter_by_tag($cn, $p_array);
}
return $action_query . $str;
}
