* Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
*/
/*! \file
* \brief Welcome page where the folder and module are choosen
*/
// Copyright Author Dany De Bontridder danydb@aevalys.eu
require_once '../include/constant.php';
include_once NOALYSS_INCLUDE . '/ac_common.php';
require_once NOALYSS_INCLUDE . '/class_database.php';
require_once NOALYSS_INCLUDE . '/class_itext.php';
require_once NOALYSS_INCLUDE . '/function_javascript.php';
@html_page_start($_SESSION['g_theme']);
$rep = new Database();
include_once NOALYSS_INCLUDE . '/class_user.php';
$User = new User($rep);
$User->Check();
/* Check Browser version if < IE6 then unsupported */
$browser = $_SERVER['HTTP_USER_AGENT'];
if (strpos($browser, 'MSIE 6') != false || strpos($browser, 'MSIE 5') != false) {
$nav = _('Vous utilisez un navigateur dépassé depuis près de 8 ans!');
$nav2 = _("Pour une meilleure expérience web, prenez le temps de mettre votre navigateur à jour");
echo <<<EOF
<!--[if lt IE 7]>
<div style='border: 1px solid #F7941D; background: #FEEFDA; text-align: center; clear: both; height: 75px; position: relative;'>
<div style='position: absolute; right: 3px; top: 3px; font-family: courier new; font-weight: bold;'><a href='#' onclick='javascript:this.parentNode.parentNode.style.display="none"; return false;'><img src='http://www.ie6nomore.com/files/theme/ie6nomore-cornerx.jpg' style='border: none;' alt='Close this notice'/></a></div>
<div style='width: 640px; margin: 0 auto; text-align: left; padding: 0; overflow: hidden; color: black;'>
<div style='width: 75px; float: left;'><img src='http://www.ie6nomore.com/files/theme/ie6nomore-warning.jpg' alt='Warning!'/></div>
<div style='width: 275px; float: left; font-family: Arial, sans-serif;'>
<div style='font-size: 14px; font-weight: bold; margin-top: 12px;'>{$nav}</div>
<div style='font-size: 12px; margin-top: 6px; line-height: 12px;'>{$nav2}.</div>
</div>
* \brief Search module
*/
require_once '../include/constant.php';
require_once NOALYSS_INCLUDE . '/class_dossier.php';
include_once NOALYSS_INCLUDE . '/ac_common.php';
require_once NOALYSS_INCLUDE . '/class_acc_ledger.php';
html_page_start($_SESSION['g_theme']);
load_all_script();
$gDossier = dossier::id();
require_once NOALYSS_INCLUDE . '/class_database.php';
/* Admin. Dossier */
$cn = new Database($gDossier);
include_once NOALYSS_INCLUDE . '/class_user.php';
global $g_user;
$g_user = new User($cn);
$g_user->Check();
$act = $g_user->check_dossier($gDossier);
// AC CODE = SEARCH
if ($act == 'P') {
redirect("extension.php?" . dossier::get(), 0);
exit;
}
if ($act == 'X') {
alert(_('Accès interdit'));
exit;
}
// display a search box
$ledger = new Acc_Ledger($cn, 0);
$ledger->type = 'ALL';
$search_box = $ledger->search_form('ALL', 1);
echo '<div class="content">';
/**
* @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);
}
