'; echo dossier::hidden(); $hid = new IHidden(); $hid->name = "ac"; $hid->value = $_REQUEST['ac']; echo $hid->input(); $hid->name = "see"; $hid->value = ""; echo $hid->input(); $w = new ISelect(); $w->name = "p_periode"; // filter on the current year $filter_year = " where p_exercice='" . $g_user->get_exercice() . "'"; $periode_start = $cn->make_array("select p_id,to_char(p_start,'DD-MM-YYYY') from parm_periode {$filter_year} order by p_start,p_end", 1); $g_user = new User($cn); $current = isset($_GET['p_periode']) ? $_GET['p_periode'] : $g_user->get_periode(); $w->value = $periode_start; $w->selected = $current; echo _('Filtrer par période') . ":" . $w->input() . HtmlInput::submit('gl_submit', 'Valider') . '</form>'; echo '<hr>'; echo '<div class="content" >'; echo $a->html_table($current); echo '</div>'; return; } if (isset($_POST['save'])) { // record the operation and exit // and exit //----------------------------- echo '<div class="redcontent" >' . _('Opération sauvée'); $a = new Anc_Group_Operation($cn);
/** * @brief this function will create a sql stmt to use to create the list for * the ledger, * @param$p_array is usually the $_GET, * @param$p_order the order of the row * @param$p_where is the sql condition if not null then the $p_array will not be used * \note the p_action will be used to filter the ledger but gl means ALL * struct array $p_array \verbatim ( [gDossier] => 13 [p_jrn] => -1 [date_start] => [date_end] => [amount_min] => 0 [amount_max] => 0 [desc] => [search] => Rechercher [p_action] => ven [sa] => l ) \endverbatim * \return an array with a valid sql statement, an the where clause => array[sql] array[where] * \see list_operation * \see display_search_form * \see search_form */ public function build_search_sql($p_array, $p_order = "", $p_where = "") { $sql = "select jr_id\t,\n jr_montant,\n substr(jr_comment,1,60) as jr_comment,\n to_char(jr_ech,'DD.MM.YY') as str_jr_ech,\n to_char(jr_date,'DD.MM.YY') as str_jr_date,\n jr_date as jr_date_order,\n jr_grpt_id,\n jr_rapt,\n jr_internal,\n jrn_def_id,\n jrn_def_name,\n jrn_def_ech,\n jrn_def_type,\n jr_valid,\n jr_tech_per,\n jr_pj_name,\n p_closed,\n jr_pj_number,\n n_text,\n\t case\n\t when jrn_def_type='VEN' then\n\t\t (select ad_value from fiche_detail where ad_id=1\n\t\t and f_id=(select max(qs_client) from quant_sold join jrnx using (j_id) join jrn as e on (e.jr_grpt_id=j_grpt) where e.jr_id=x.jr_id))\n\t when jrn_def_type = 'ACH' then\n\t\t(select ad_value from fiche_detail where ad_id=1\n\t\tand f_id=(select max(qp_supplier) from quant_purchase join jrnx using (j_id) join jrn as e on (e.jr_grpt_id=j_grpt) where e.jr_id=x.jr_id))\n\t when jrn_def_type = 'FIN' then\n\t\t(select ad_value from fiche_detail where ad_id=1\n\t\tand f_id=(select qf_other from quant_fin where quant_fin.jr_id=x.jr_id))\n\t end as name,\n\t case\n\t when jrn_def_type='VEN' then (select ad_value from fiche_detail where ad_id=32 and f_id=(select max(qs_client) from quant_sold join jrnx using (j_id) join jrn as e on (e.jr_grpt_id=j_grpt) where e.jr_id=x.jr_id))\n\t when jrn_def_type = 'ACH' then (select ad_value from fiche_detail where ad_id=32 and f_id=(select max(qp_supplier) from quant_purchase join jrnx using (j_id) join jrn as e on (e.jr_grpt_id=j_grpt) where e.jr_id=x.jr_id))\n\t when jrn_def_type = 'FIN' then (select ad_value from fiche_detail where ad_id=32 and f_id=(select qf_other from quant_fin where quant_fin.jr_id=x.jr_id))\n\t end as first_name,\n\t case\n\t when jrn_def_type='VEN' then (select ad_value from fiche_detail where ad_id=23 and f_id=(select max(qs_client) from quant_sold join jrnx using (j_id) join jrn as e on (e.jr_grpt_id=j_grpt) where e.jr_id=x.jr_id))\n\t when jrn_def_type = 'ACH' then (select ad_value from fiche_detail where ad_id=23 and f_id=(select max(qp_supplier) from quant_purchase join jrnx using (j_id) join jrn as e on (e.jr_grpt_id=j_grpt) where e.jr_id=x.jr_id))\n\t when jrn_def_type = 'FIN' then (select ad_value from fiche_detail where ad_id=23 and f_id=(select qf_other from quant_fin where quant_fin.jr_id=x.jr_id))\n\t end as quick_code,\n\t case\n\t when jrn_def_type='VEN' then\n\t\t (select sum(qs_price)+sum(vat) from\n\t\t\t\t(select qs_internal,qs_price,case when qs_vat_sided<>0 then 0 else qs_vat end as vat from quant_sold where qs_internal=X.jr_internal) as ven_invoice\n\t\t\t )\n\t when jrn_def_type = 'ACH' then\n\t\t\t(\n\t\t\t\tselect sum(qp_price)+sum(vat)+sum(qp_nd_tva)+sum(qp_nd_tva_recup)\n\t\t\t\tfrom\n\t\t\t\t (select qp_internal,qp_price,qp_nd_tva,qp_nd_tva_recup,qp_vat-qp_vat_sided as vat from quant_purchase where qp_internal=X.jr_internal) as invoice_purchase\n\t\t\t)\n\t\telse null\n\t\tend as total_invoice,\n jr_date_paid,\n to_char(jr_date_paid,'DD.MM.YY') as str_jr_date_paid\n from\n jrn as X left join jrn_note using(jr_id)\n join jrn_def on jrn_def_id=jr_def_id\n join parm_periode on p_id=jr_tech_per"; if (!empty($p_array)) { extract($p_array); } if (isset($op)) { $r_jrn = isset(${$op . "r_jrn"}) ? ${$op . "r_jrn"} : -1; } else { $r_jrn = isset($r_jrn) ? $r_jrn : -1; } /* if no variable are set then give them a default * value */ if ($p_array == null || empty($p_array) || !isset($amount_min)) { $amount_min = 0; $amount_max = 0; $desc = ''; $qcode = isset($qcode) ? $qcode : ""; if (isset($qcodesearch_op)) { $qcode = $qcodesearch_op; } $accounting = isset($accounting) ? $accounting : ""; $periode = new Periode($this->db); $g_user = new User($this->db); $p_id = $g_user->get_periode(); if ($p_id != null) { list($date_start, $date_end) = $periode->get_date_limit($p_id); } } /* if p_jrn : 0 if means all ledgers, if -1 means all ledger of this * type otherwise only one ledger */ $fil_ledger = ''; $fil_amount = ''; $fil_date = ''; $fil_desc = ''; $fil_sec = ''; $fil_qcode = ''; $fil_account = ''; $fil_paid = ''; $fil_date_paid = ''; $and = ''; $g_user = new User($this->db); $p_action = $ledger_type; if ($p_action == '') { $p_action = 'ALL'; } if ($r_jrn == -1) { /* from compta.php the p_action is quick_writing instead of ODS */ if ($p_action == 'quick_writing') { $p_action = 'ODS'; } $fil_ledger = $g_user->get_ledger_sql($p_action, 3); $and = ' and '; } else { if ($p_action == 'quick_writing') { $p_action = 'ODS'; } $aLedger = $g_user->get_ledger($p_action, 3); $fil_ledger = ''; $sp = ''; for ($i = 0; $i < count($r_jrn); $i++) { if (isset($r_jrn[$i])) { $a = $r_jrn[$i]; $fil_ledger .= $sp . $a; $sp = ','; } } $fil_ledger = ' jrn_def_id in (' . $fil_ledger . ')'; $and = ' and '; /* no ledger selected */ if ($sp == '') { $fil_ledger = ''; $and = ''; } } /* format the number */ $amount_min = abs(toNumber($amount_min)); $amount_max = abs(toNumber($amount_max)); if ($amount_min > 0 && isNumber($amount_min)) { $fil_amount = $and . ' jr_montant >=' . $amount_min; $and = ' and '; } if ($amount_max > 0 && isNumber($amount_max)) { $fil_amount .= $and . ' jr_montant <=' . $amount_max; $and = ' and '; } /* -------------------------------------------------------------------------- * * if both amount are the same then we need to search into the detail * and we reset the fil_amount * -------------------------------------------------------------------------- */ if (isNumber($amount_min) && isNumber($amount_max) && $amount_min > 0 && bccomp($amount_min, $amount_max, 2) == 0) { $fil_amount = $and . ' ( '; // Look in detail $fil_amount .= 'jr_grpt_id in ( select distinct j_grpt from jrnx where j_montant = ' . $amount_min . ') '; //and the total operation $fil_amount .= ' or '; $fil_amount .= ' jr_montant = ' . $amount_min; $fil_amount .= ')'; $and = " and "; } // date if (isset($date_start) && isDate($date_start) != null) { $fil_date = $and . " jr_date >= to_date('" . $date_start . "','DD.MM.YYYY')"; $and = " and "; } if (isset($date_end) && isDate($date_end) != null) { $fil_date .= $and . " jr_date <= to_date('" . $date_end . "','DD.MM.YYYY')"; $and = " and "; } // date paiement if (isset($date_paid_start) && isDate($date_paid_start) != null) { $fil_date_paid = $and . " jr_date_paid >= to_date('" . $date_paid_start . "','DD.MM.YYYY')"; $and = " and "; } if (isset($date_paid_end) && isDate($date_paid_end) != null) { $fil_date_paid .= $and . " jr_date_paid <= to_date('" . $date_paid_end . "','DD.MM.YYYY')"; $and = " and "; } // comment if (isset($desc) && $desc != null) { $desc = sql_string($desc); $fil_desc = $and . " ( upper(jr_comment) like upper('%" . $desc . "%') or upper(jr_pj_number) like upper('%" . $desc . "%') " . " or upper(jr_internal) like upper('%" . $desc . "%')\n or jr_grpt_id in (select j_grpt from jrnx where j_text ~* '" . $desc . "')\n or jr_id in (select jr_id from jrn_info where ji_value is not null and ji_value ~* '{$desc}')\n )"; $and = " and "; } // Poste if (isset($accounting) && $accounting != null) { $fil_account = $and . " jr_grpt_id in (select j_grpt\n from jrnx where j_poste::text like '" . sql_string($accounting) . "%' ) "; $and = " and "; } // Quick Code if (isset($qcodesearch_op)) { $qcode = $qcodesearch_op; } if (isset($qcode) && $qcode != null) { $fil_qcode = $and . " jr_grpt_id in ( select j_grpt from\n jrnx where trim(j_qcode) = upper(trim('" . sql_string($qcode) . "')))"; $and = " and "; } // Only the unpaid if (isset($unpaid)) { $fil_paid = $and . SQL_LIST_UNPAID_INVOICE; $and = " and "; } $g_user = new User(new Database()); $g_user->Check(); $g_user->check_dossier(dossier::id()); if ($g_user->admin == 0 && $g_user->is_local_admin() == 0) { $fil_sec = $and . " jr_def_id in ( select uj_jrn_id " . " from user_sec_jrn where " . " uj_login='******'g_user'] . "'" . " and uj_priv in ('R','W'))"; } $where = $fil_ledger . $fil_amount . $fil_date . $fil_desc . $fil_sec . $fil_amount . $fil_qcode . $fil_paid . $fil_account . $fil_date_paid; $sql .= " where " . $where; return array($sql, $where); }
<tr> <td> <?php echo _('Thème'); ?> </td> <td> <?php echo $style->input(); ?> </td> </tr> <?php if ($inside_dossier) { $l_user_per = $g_user->get_periode(); if ($l_user_per == "") { $l_user_per = $cn->get_value("select min(p_id) from parm_periode where p_closed='f'"); } // if periode is closed then warns the users $period = new Periode($cn, $l_user_per); $period->p_id = $l_user_per; $period->jrn_def_id = 0; if ($period->is_closed($l_user_per) == 1) { $msg = _('Attention cette période est fermée, vous ne pourrez rien modifier dans le module comptable'); $msg = '<h2 class="notice">' . $msg . '</h2>'; } $period = new IPeriod("period"); $period->user = $g_user; $period->cn = $cn; $period->filter_year = false;
echo '<h2 class="error" style="font-size:12px">' . _("Attention: la version de base de donnée est supérieure à la version du programme, vous devriez mettre à jour") . '</h2>'; } if (DBVERSION > dossier::get_version($cn)) { echo '<h2 class="error" style="font-size:12px">' . _("Votre base de données n'est pas à jour") . ' '; $a = _("cliquez ici pour appliquer le patch"); $base = dirname($_SERVER['REQUEST_URI']); if ($base == '/') { $base = ''; } $base .= '/admin/setup.php'; echo '<a hreF="' . $base . '">' . $a . '</a></h2>'; } /* * Set a correct periode for the user */ $periode = $g_user->get_periode(); $oPeriode = new Periode($cn, $periode); if ($oPeriode->load() == -1) { $periode = $cn->get_value('select p_id from parm_periode order by p_start asc limit 1'); $g_user->set_periode($periode); } $module_selected = -1; /* * Set the user preference */ if (isset($_POST['set_preference'])) { //// Save value extract($_POST); if (strlen(trim($pass_1)) != 0 && strlen(trim($pass_2)) != 0) { $g_user->save_password($pass_1, $pass_2); }