X-Git-Url: https://delta.frontaccounting.com/gitweb/?a=blobdiff_plain;f=includes%2Freferences.inc;h=fc343e65bec7c661e057536a6d3ef80e4c6f7b89;hb=00a3e9d49999a521ea3bc23cc32d2f94974fcf41;hp=38bd84cdceb541470d38674b2b4f1eb638d5c187;hpb=13367be8aaf5175295f7aab7160370458dad1824;p=fa-stable.git diff --git a/includes/references.inc b/includes/references.inc index 38bd84cd..fc343e65 100644 --- a/includes/references.inc +++ b/includes/references.inc @@ -9,77 +9,405 @@ MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the License here . ***********************************************************************/ -include_once($path_to_root . "/includes/db/references_db.inc"); +include_once($path_to_root . "/includes/db/class.reflines_db.inc"); +include_once($path_to_root . "/admin/db/fiscalyears_db.inc"); +include_once($path_to_root . "/includes/types.inc"); //--------------------------------------------------------------------------------------------- +// 2.4 - further changes toward removing refs table introduced: +// . all transactions now have references stored in trans table. +// . all reference related moved to class (is_new_reference yet preserved) +// . template based reflines implemented // -// For now (2.3) the references system has somewhat inconsistent design scheme. -// Most transactions store references in respective table, but this is not the case -// for journal entries. All references regardless of type are stored also in refs table. -// Reference uniquness now can be checked with is_new_reference() for all transactions. -// In near future this should be fixed either with removing reference fields -// in transaction tables, or adding ref in bank transaction/journal and removing refs table. -// +// FIXME: +// - implement refline field in all transaction tables (obsoletes not always accurate find_refline_id) +// - remove save() and restore_last() - for now preserved for reflines without placeholder +// - see fixmes below +// - remove refs table and create view instead (need e.g. CREATE VIEW support in db_import/db_export) + +$refline_options = array( + ST_JOURNAL => array('date', 'user'), + ST_COSTUPDATE => array('date', 'user'), + + ST_BANKPAYMENT => array('date', 'user'), + ST_BANKDEPOSIT => array('date', 'user'), + ST_BANKTRANSFER => array('date', 'user'), + ST_SUPPAYMENT => array('date', 'user'), + ST_CUSTPAYMENT => array('date', 'user'), + + ST_SALESORDER => array('date', 'customer', 'branch', 'user', 'pos'), + ST_SALESQUOTE => array('date', 'customer', 'branch', 'user', 'pos'), + ST_SALESINVOICE => array('date', 'customer', 'branch', 'user', 'pos'), + ST_CUSTCREDIT => array('date', 'customer', 'branch', 'user', 'pos'), + ST_CUSTDELIVERY => array('date', 'customer', 'branch', 'user', 'pos'), + + ST_LOCTRANSFER => array('date', 'location', 'user'), + ST_INVADJUST => array('date', 'location', 'user'), + + ST_PURCHORDER => array('date', 'location', 'supplier', 'user'), + ST_SUPPINVOICE => array('date', 'location', 'supplier', 'user'), + ST_SUPPCREDIT => array('date', 'location', 'supplier', 'user'), + ST_SUPPRECEIVE => array('date', 'location', 'supplier', 'user'), + + ST_WORKORDER => array('date', 'location', 'user'), + ST_MANUISSUE => array('date', 'location', 'user'), + ST_MANURECEIVE => array('date', 'user'), + ST_DIMENSION => array('date','user'), +); + +$refline_placeholders = array( + 'MM' => 'date', + 'YY' => 'date', + 'YYYY' => 'date', + 'FF' => 'date', // fiscal year + 'FFFF' => 'date', + 'UU' => 'user', + 'P' => 'pos', +// FIXME: for placeholders below all the code should work, but as the ref length is variable, +// length specification in placeholder format should be implemented. +// 'C' => 'customer', +// 'B' => 'branch', +// 'S' => 'supplier', +// 'L' => 'location' +); class references { - // - // Get reference from refs table for given transaction. - // Used for transactions which do not hold references (journal and bank). - // - function get($type, $id) + var $reflines; + + function __construct() { - return get_reference($type, $id); + $this->reflines = new reflines_db(); } + + function _legacy_line($refline) + { + return strpbrk($refline['pattern'], '{}') == false; + } + + function _parse_next($type, $template, $context=null) + { + global $refline_options, $refline_placeholders; + + // date based placeholders are always allowed, so default for non-array context is date + if (!isset($context)) + $context = new_doc_date(); + + if (is_string($context)) + $context = array('date' => $context); + + $context['user'] = $_SESSION['wa_current_user']->user; + $context['pos'] = $_SESSION['wa_current_user']->pos; + $out = ''; + + while(($start = strpos($template, '{')) !==false) { + + $out .= substr($template, 0, $start); + $stop = strpos($template, '}'); + if ($stop === false) { + display_warning(_("Invalid refline template.")); + $out .= $template; // debug + break; + } + $ph = substr($template, $start+1, $stop-$start-1); + $template = substr($template, $stop+1); + + if (isset($refline_placeholders[$ph])) { + if (!isset($context[$refline_placeholders[$ph]])) + { + display_warning(sprintf(_("Missing refline context data: '%s'"), $refline_placeholders[$ph])); + $out .= $ph; // debug + } else { + switch ($ph) + { + case 'MM': + case 'YY': + case 'YYYY': + list($day, $month, $year) = explode_date_to_dmy($context['date']); + $out .= $ph == 'MM' ? sprintf('%02d', $month) : ($ph == 'YY' ? sprintf('%02d', $year%100): sprintf('%04d', $year)); + break; + case 'FF': + case 'FFFF': + list($day, $month, $year) = explode_date_to_dmy(get_fiscalyear_begin_for_date($context['date'])); + $out .= $ph == 'FF' ? sprintf('%02d', $year%100): sprintf('%04d', $year); + break; + case 'C': + $out .= sprintf('%d', $context['customer']); + break; + + case 'B': + $out .= sprintf('%d', $context['branch']); + break; + + case 'S': + $out .= sprintf('%d', $context['supplier']); + break; + + case 'L': + $out .= sprintf('%s', $context['location']); + break; + + case 'P': + $out .= sprintf('%s', $context['pos']); + break; + + case 'UU': + $out .= sprintf('%02d', $context['user']); + break; + } + } + } elseif (is_numeric($ph)) { + $out .= '{'.$ph.'}'; // index placeholder + } + } + + $out .= $template; // add postfix + if (!preg_match('/^([^\{]*)?\{([^\}]*)\}(.*)/', $out, $match)) { // parse index + display_error(_("Missing numeric placeholder in refline definition.")); + return $out; + } + + $prefix = $match[1]; + $postfix = $match[3]; + + $db_info = get_systype_db_info($type); + $trans_table = $db_info[0]; + $type_fld = $db_info[1]; + $tno_fld = $db_info[2]; + $ref_fld = $db_info[3]; + $type = db_escape($type); + + // retrieve last ref number in the refline from original transaction table + $sql = "SELECT MAX(CAST(SUBSTR($ref_fld, ".(strlen($prefix)+1).",LENGTH($ref_fld)-".(strlen($postfix)+strlen($prefix)).") AS UNSIGNED))" + ." FROM `$trans_table` tbl + LEFT JOIN ".TB_PREF."voided v ON tbl.`$tno_fld`=v.id AND v.type=$type" + ." WHERE ISNULL(v.id)" + .($type_fld ? " AND tbl.`$type_fld`=$type" : '') + ." AND `$ref_fld` REGEXP ".db_escape('^'.preg_quote($prefix).'[0-9]*'.preg_quote($postfix).'$'); + $result = db_query($sql, 'cannot retrieve last reference'); + $result = db_fetch_row($result); + + // fill with zeros to the length of original index placeholder + return $prefix.sprintf('%0'.strlen($match[2]).'d', $result[0]+1).$postfix; + } + // - // Check if reference is used for any non voided transaction (used for ST_JOURNALENTRY type) + // Get/check transaction reference. + // $ref!=null => check reference is not used (or unique for $trans_no!=0) + // $trans!=0 $ref=null => retrieve reference for the $type/$trans_no (if any) // - function exists($type, $reference) + function _get($type, $trans_no=0, $ref=null) + { + $db_info = get_systype_db_info($type); + $trans_table = $db_info[0]; + $type_fld = $db_info[1]; + $tno_fld = $db_info[2]; + $ref_fld = $db_info[3]; + + $type = db_escape($type); + + $sql = "SELECT `$ref_fld` + FROM `$trans_table` tbl + LEFT JOIN ".TB_PREF."voided v ON + tbl.`$tno_fld`=v.id AND v.type=$type" + ." WHERE ISNULL(v.id)" + .($type_fld ? " AND tbl.`$type_fld`=$type" : ''); + if ($ref) + { + $sql .= " AND tbl.`$ref_fld`=".db_escape(trim($ref)); + if ($trans_no) + $sql .= " AND tbl.`$tno_fld` != ".db_escape($trans_no); + } else { + $sql .= " AND tbl.`$tno_fld`=".db_escape($trans_no); + } + + $result = db_query($sql, "could not test for unique reference"); + if (!$result) + return false; + + $result = db_fetch_row($result); + return $result[0]; + } + + function is_new_reference($ref, $type, $trans_no=0) { - return (find_reference($type, $reference) != null); + return !$this->_get($type, $trans_no, $ref); } + // - // Get default reference on new transaction creation. + // Get default reference for new transaction. // - function get_next($type) + function get_next($type, $line=null, $context=null) { - return get_next_reference($type); + + if (isset($line)) + $refline = $this->reflines->get($line); + else { + $refs = $this->reflines->get_all("trans_type=".db_escape($type)." AND `default`"); + $refline = db_fetch($refs); + } + + if ($this->_legacy_line($refline)) + return $refline['pattern']; + + return $this->_parse_next($type, $refline['prefix'].$refline['pattern'], $context); } + + /** + * Normalize reference to format allowed by refline (optionally selected by prefix). + * FIXME: currently this is fake function which return either input reference or + * next reference when no line has been recognized. + **/ + function normalize($ref, $type, $context, $line=null) + { + if (!isset($type)) // inquiries + return $ref; + + if (!$line) + $line = $this->reflines->find_refline_id($ref, $type); + + return $this->is_valid($ref, $type, $context, $line) ? $ref : $this->get_next($type, $line, $context); + } + // // Check reference is valid before add/update transaction. + // FIXME: does not check leading zeros in number // - function is_valid($reference) + function is_valid($reference, $type, $context=null, $line=null) { - return strlen(trim($reference)) > 0; + if (!isset($line)) + $line = $this->reflines->find_refline_id($reference, $type, true); + + if (!isset($line)) + return false; + + $refline = $this->reflines->get($line); + + if ($this->_legacy_line($refline)) //legacy non-templated line + return strlen(trim($reference)) > 0; + + $regex = preg_quote($refline['prefix'].$refline['pattern']); + if (!is_array($context)) + $context = array('date'=>$context); + + $context['pos'] = $_SESSION["wa_current_user"]->pos; + + if (is_date(@$context['date'])) + { + list($year4, $month, $day) = explode("-", date2sql($context['date'])); + $year2 = substr($year4, 2); + $f_year = explode("-", date2sql(get_fiscalyear_begin_for_date($context['date']))); + $f_year2 = substr($f_year[0], 2); + $f_year4 = $f_year[0]; + } else + { + $month = '\d{2,}'; + $year2 = '\d{2,}'; + $year4 = '\d{4,}'; + $f_year2 = '\d{2,}'; + $f_year4 = '\d{4,}'; + } + $cust = @$context['customer'] ? $context['customer'] : '\d+'; + $supp = @$context['supplier'] ? $context['supplier'] : '\d+'; + $branch = @$context['branch'] ? $context['branch'] : '\d+'; + $location = @$context['location'] ? $context['location'] : '[a-z0-9]+'; + $pos = @$context['pos'] ? $context['pos'] : '\d+'; + $user = sprintf("%02d", $_SESSION['wa_current_user']->user); + + $regex = preg_replace( + array( + '/\\\{/', // unquote placeholders + '/\\\}/', + '/\{MM\}/', + '/\{YY\}/', + '/\{YYYY\}/', + '/\{FF\}/', + '/\{FFFF\}/', + '/\{C\}/', + '/\{B\}/', + '/\{S\}/', + '/\{L\}/', + '/\{UU\}/', + '/\{P\}/', + '/\{\d+}/', + ), + array( + '{', + '}', + $month, + $year2, + $year4, + $f_year2, + $f_year4, + $cust, + $branch, + $supp, + $location, + $user, + $pos, + '\d+', + ), $regex); + + $regex = '"^'.$regex.'"i'; + + return preg_match($regex, $reference, $match) ? 1 : 0; } + // // Save reference (and prepare next) on write transaction. // - function save($type, $id, $reference) + function save($type, $id, $reference, $line = null) { - update_reference($type, $id, $reference); // store in refs table - if ($reference == $this->get_next($type)) { // if reference was bigger or not changed from default + if ($reference == 'auto') + return; + + $sql = "REPLACE ".TB_PREF."refs SET reference=".db_escape($reference) + .", type=".db_escape($type).", id=".db_escape($id); + db_query($sql, "could not update reference entry"); + + if (!isset($line)) + { + $line = $this->reflines->find_refline_id($reference, $type); + } + + $refline = $this->reflines->get($line); + // legacy code used with simple templates + if ($this->_legacy_line($refline) && ($reference == $this->get_next($type, $line))) { // if reference was not changed from default $next = $this->_increment($reference); // increment default - save_next_reference($type, $next); + $this->reflines->save_next($type, $next, $line); } } // // Restore previous reference (if possible) after voiding transaction. // - function restore_last($type, $id) + function restore_last($type, $id) { + // get refline for removed document reference $reference = get_reference($type, $id); - $prev = $this->_increment($this->get_next($type), true); //decrement - if ($reference==$prev) { - save_next_reference($type, $prev); + $line = $this->reflines->find_refline_id($reference, $type); + $refline = $this->reflines->get($line); + + if ($this->_legacy_line($refline)) // legacy code used with simple templates + { + $last = $this->_increment($this->get_next($type, $line), true); // find last reference used in this line + if ($reference == $last) + { + // save last reference as next + $sql = "UPDATE ".TB_PREF."reflines SET pattern=SUBSTRING(" . db_escape(trim($last)) .", LENGTH(`prefix`)+1)" + . " WHERE trans_type = ".db_escape($type) . " AND `id`=".db_escape($line); + + db_query($sql, "The next transaction ref for $type could not be updated"); + } } } + //----------------------------------------------------------------------- // // Increments (or decrements if $back==true) reference template // function _increment($reference, $back=false) { - // New method done by Pete. So f.i. WA036 will increment to WA037 and so on. + // Legacy code used when no palceholder is in use: + // WA036 will increment to WA037 and so on. // If $reference contains at least one group of digits, // extract first didgits group and add 1, then put all together. // NB. preg_match returns 1 if the regex matches completely @@ -98,6 +426,23 @@ class references else return $reference; } + +} + +//---------------------------------------------------------------------------- +// +// Check if reference was not used so far (for other transaction than $trans_no) +// +function is_new_reference($ref, $type, $trans_no=0) +{ + global $Refs; + + return $Refs->is_new_reference($ref, $type, $trans_no); } -?> \ No newline at end of file +function get_reference($type, $trans_no) +{ + global $Refs; + + return $Refs->_get($type, $trans_no); +}