2 /**********************************************************************
3 Copyright (C) FrontAccounting, LLC.
4 Released under the terms of the GNU General Public License, GPL,
5 as published by the Free Software Foundation, either version 3
6 of the License, or (at your option) any later version.
7 This program is distributed in the hope that it will be useful,
8 but WITHOUT ANY WARRANTY; without even the implied warranty of
9 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
10 See the License here <http://www.gnu.org/licenses/gpl-3.0.html>.
11 ***********************************************************************/
12 include_once($path_to_root . "/includes/db/class.reflines_db.inc");
13 include_once($path_to_root . "/admin/db/fiscalyears_db.inc");
14 include_once($path_to_root . "/includes/types.inc");
15 //---------------------------------------------------------------------------------------------
16 // 2.4 - further changes toward removing refs table introduced:
17 // . all transactions now have references stored in trans table.
18 // . all reference related moved to class (is_new_reference yet preserved)
19 // . template based reflines implemented
22 // - implement refline field in all transaction tables (obsoletes not always accurate find_refline_id)
23 // - remove save() and restore_last() - for now preserved for reflines without placeholder
25 // - remove refs table and create view instead (need e.g. CREATE VIEW support in db_import/db_export)
27 $refline_options = array(
28 ST_JOURNAL => array('date', 'user'),
29 ST_COSTUPDATE => array('date', 'user'),
31 ST_BANKPAYMENT => array('date', 'user'),
32 ST_BANKDEPOSIT => array('date', 'user'),
33 ST_BANKTRANSFER => array('date', 'user'),
34 ST_SUPPAYMENT => array('date', 'user'),
35 ST_CUSTPAYMENT => array('date', 'user'),
37 ST_SALESORDER => array('date', 'customer', 'branch', 'user', 'pos'),
38 ST_SALESQUOTE => array('date', 'customer', 'branch', 'user', 'pos'),
39 ST_SALESINVOICE => array('date', 'customer', 'branch', 'user', 'pos'),
40 ST_CUSTCREDIT => array('date', 'customer', 'branch', 'user', 'pos'),
41 ST_CUSTDELIVERY => array('date', 'customer', 'branch', 'user', 'pos'),
43 ST_LOCTRANSFER => array('date', 'location', 'user'),
44 ST_INVADJUST => array('date', 'location', 'user'),
46 ST_PURCHORDER => array('date', 'location', 'supplier', 'user'),
47 ST_SUPPINVOICE => array('date', 'location', 'supplier', 'user'),
48 ST_SUPPCREDIT => array('date', 'location', 'supplier', 'user'),
49 ST_SUPPRECEIVE => array('date', 'location', 'supplier', 'user'),
51 ST_WORKORDER => array('date', 'location', 'user'),
52 ST_MANUISSUE => array('date', 'location', 'user'),
53 ST_MANURECEIVE => array('date', 'user'),
54 ST_DIMENSION => array('date','user'),
57 $refline_placeholders = array(
61 'FF' => 'date', // fiscal year
65 // FIXME: for placeholders below all the code should work, but as the ref length is variable,
66 // length specification in placeholder format should be implemented.
77 function __construct()
79 $this->reflines = new reflines_db();
82 function _legacy_line($refline)
84 return strpbrk($refline['pattern'], '{}') == false;
87 function _parse_next($type, $template, $context=null)
89 global $refline_options, $refline_placeholders;
91 // date based placeholders are always allowed, so default for non-array context is date
93 $context = new_doc_date();
95 if (is_string($context))
96 $context = array('date' => $context);
98 $context['user'] = $_SESSION['wa_current_user']->user;
99 $context['pos'] = $_SESSION['wa_current_user']->pos;
102 while(($start = strpos($template, '{')) !==false) {
104 $out .= substr($template, 0, $start);
105 $stop = strpos($template, '}');
106 if ($stop === false) {
107 display_warning(_("Invalid refline template."));
108 $out .= $template; // debug
111 $ph = substr($template, $start+1, $stop-$start-1);
112 $template = substr($template, $stop+1);
114 if (isset($refline_placeholders[$ph])) {
115 if (!isset($context[$refline_placeholders[$ph]]))
117 display_warning(sprintf(_("Missing refline context data: '%s'"), $refline_placeholders[$ph]));
118 $out .= $ph; // debug
125 list($day, $month, $year) = explode_date_to_dmy($context['date']);
126 $out .= $ph == 'MM' ? sprintf('%02d', $month) : ($ph == 'YY' ? sprintf('%02d', $year%100): sprintf('%04d', $year));
130 list($day, $month, $year) = explode_date_to_dmy(get_fiscalyear_begin_for_date($context['date']));
131 $out .= $ph == 'FF' ? sprintf('%02d', $year%100): sprintf('%04d', $year);
134 $out .= sprintf('%d', $context['customer']);
138 $out .= sprintf('%d', $context['branch']);
142 $out .= sprintf('%d', $context['supplier']);
146 $out .= sprintf('%s', $context['location']);
150 $out .= sprintf('%s', $context['pos']);
154 $out .= sprintf('%02d', $context['user']);
158 } elseif (is_numeric($ph)) {
159 $out .= '{'.$ph.'}'; // index placeholder
163 $out .= $template; // add postfix
164 if (!preg_match('/^([^\{]*)?\{([^\}]*)\}(.*)/', $out, $match)) { // parse index
165 display_error(_("Missing numeric placeholder in refline definition."));
170 $postfix = $match[3];
172 $db_info = get_systype_db_info($type);
173 $trans_table = $db_info[0];
174 $type_fld = $db_info[1];
175 $tno_fld = $db_info[2];
176 $ref_fld = $db_info[3];
177 $type = db_escape($type);
179 // retrieve last ref number in the refline from original transaction table
180 $sql = "SELECT MAX(CAST(SUBSTR($ref_fld, ".(strlen($prefix)+1).",LENGTH($ref_fld)-".(strlen($postfix)+strlen($prefix)).") AS UNSIGNED))"
181 ." FROM `$trans_table` tbl
182 LEFT JOIN ".TB_PREF."voided v ON tbl.`$tno_fld`=v.id AND v.type=$type"
183 ." WHERE ISNULL(v.id)"
184 .($type_fld ? " AND tbl.`$type_fld`=$type" : '')
185 ." AND `$ref_fld` REGEXP ".db_escape('^'.preg_quote($prefix).'[0-9]*'.preg_quote($postfix).'$');
186 $result = db_query($sql, 'cannot retrieve last reference');
187 $result = db_fetch_row($result);
189 // fill with zeros to the length of original index placeholder
190 return $prefix.sprintf('%0'.strlen($match[2]).'d', $result[0]+1).$postfix;
194 // Get/check transaction reference.
195 // $ref!=null => check reference is not used (or unique for $trans_no!=0)
196 // $trans!=0 $ref=null => retrieve reference for the $type/$trans_no (if any)
198 function _get($type, $trans_no=0, $ref=null)
200 $db_info = get_systype_db_info($type);
201 $trans_table = $db_info[0];
202 $type_fld = $db_info[1];
203 $tno_fld = $db_info[2];
204 $ref_fld = $db_info[3];
206 $type = db_escape($type);
208 $sql = "SELECT `$ref_fld`
209 FROM `$trans_table` tbl
210 LEFT JOIN ".TB_PREF."voided v ON
211 tbl.`$tno_fld`=v.id AND v.type=$type"
212 ." WHERE ISNULL(v.id)"
213 .($type_fld ? " AND tbl.`$type_fld`=$type" : '');
216 $sql .= " AND tbl.`$ref_fld`=".db_escape(trim($ref));
218 $sql .= " AND tbl.`$tno_fld` != ".db_escape($trans_no);
220 $sql .= " AND tbl.`$tno_fld`=".db_escape($trans_no);
223 $result = db_query($sql, "could not test for unique reference");
227 $result = db_fetch_row($result);
231 function is_new_reference($ref, $type, $trans_no=0)
233 return !$this->_get($type, $trans_no, $ref);
237 // Get default reference for new transaction.
239 function get_next($type, $line=null, $context=null)
243 $refline = $this->reflines->get($line);
245 $refs = $this->reflines->get_all("trans_type=".db_escape($type)." AND `default`");
246 $refline = db_fetch($refs);
249 if ($this->_legacy_line($refline))
250 return $refline['pattern'];
252 return $this->_parse_next($type, $refline['prefix'].$refline['pattern'], $context);
256 * Normalize reference to format allowed by refline (optionally selected by prefix).
257 * FIXME: currently this is fake function which return either input reference or
258 * next reference when no line has been recognized.
260 function normalize($ref, $type, $context, $line=null)
262 if (!isset($type)) // inquiries
266 $line = $this->reflines->find_refline_id($ref, $type);
268 return $this->is_valid($ref, $type, $context, $line) ? $ref : $this->get_next($type, $line, $context);
272 // Check reference is valid before add/update transaction.
273 // FIXME: does not check leading zeros in number
275 function is_valid($reference, $type, $context=null, $line=null)
278 $line = $this->reflines->find_refline_id($reference, $type, true);
283 $refline = $this->reflines->get($line);
285 if ($this->_legacy_line($refline)) //legacy non-templated line
286 return strlen(trim($reference)) > 0;
288 $regex = preg_quote($refline['prefix'].$refline['pattern']);
289 if (!is_array($context))
290 $context = array('date'=>$context);
292 $context['pos'] = $_SESSION["wa_current_user"]->pos;
294 if (is_date(@$context['date']))
296 list($year4, $month, $day) = explode("-", date2sql($context['date']));
297 $year2 = substr($year4, 2);
298 $f_year = explode("-", date2sql(get_fiscalyear_begin_for_date($context['date'])));
299 $f_year2 = substr($f_year[0], 2);
300 $f_year4 = $f_year[0];
309 $cust = @$context['customer'] ? $context['customer'] : '\d+';
310 $supp = @$context['supplier'] ? $context['supplier'] : '\d+';
311 $branch = @$context['branch'] ? $context['branch'] : '\d+';
312 $location = @$context['location'] ? $context['location'] : '[a-z0-9]+';
313 $pos = @$context['pos'] ? $context['pos'] : '\d+';
314 $user = sprintf("%02d", $_SESSION['wa_current_user']->user);
316 $regex = preg_replace(
318 '/\\\{/', // unquote placeholders
350 $regex = '"^'.$regex.'"i';
352 return preg_match($regex, $reference, $match) ? 1 : 0;
356 // Save reference (and prepare next) on write transaction.
358 function save($type, $id, $reference, $line = null)
360 if ($reference == 'auto')
363 $sql = "REPLACE ".TB_PREF."refs SET reference=".db_escape($reference)
364 .", type=".db_escape($type).", id=".db_escape($id);
365 db_query($sql, "could not update reference entry");
369 $line = $this->reflines->find_refline_id($reference, $type);
372 $refline = $this->reflines->get($line);
373 // legacy code used with simple templates
374 if ($this->_legacy_line($refline) && ($reference == $this->get_next($type, $line))) { // if reference was not changed from default
375 $next = $this->_increment($reference); // increment default
376 $this->reflines->save_next($type, $next, $line);
380 // Restore previous reference (if possible) after voiding transaction.
382 function restore_last($type, $id)
384 // get refline for removed document reference
385 $reference = get_reference($type, $id);
386 $line = $this->reflines->find_refline_id($reference, $type);
387 $refline = $this->reflines->get($line);
389 if ($this->_legacy_line($refline)) // legacy code used with simple templates
391 $last = $this->_increment($this->get_next($type, $line), true); // find last reference used in this line
392 if ($reference == $last)
394 // save last reference as next
395 $sql = "UPDATE ".TB_PREF."reflines SET pattern=SUBSTRING(" . db_escape(trim($last)) .", LENGTH(`prefix`)+1)"
396 . " WHERE trans_type = ".db_escape($type) . " AND `id`=".db_escape($line);
398 db_query($sql, "The next transaction ref for $type could not be updated");
403 //-----------------------------------------------------------------------
405 // Increments (or decrements if $back==true) reference template
407 function _increment($reference, $back=false)
409 // Legacy code used when no palceholder is in use:
410 // WA036 will increment to WA037 and so on.
411 // If $reference contains at least one group of digits,
412 // extract first didgits group and add 1, then put all together.
413 // NB. preg_match returns 1 if the regex matches completely
414 // also $result[0] holds entire string, 1 the first captured, 2 the 2nd etc.
416 if (preg_match('/^(\D*?)(\d+)(.*)/', $reference, $result) == 1)
418 list($all, $prefix, $number, $postfix) = $result;
419 $dig_count = strlen($number); // How many digits? eg. 0003 = 4
420 $fmt = '%0' . $dig_count . 'd'; // Make a format string - leading zeroes
421 $val = intval($number + ($back ? ($number<1 ? 0 : -1) : 1));
422 $nextval = sprintf($fmt, $val); // Add one on, and put prefix back on
424 return $prefix.$nextval.$postfix;
432 //----------------------------------------------------------------------------
434 // Check if reference was not used so far (for other transaction than $trans_no)
436 function is_new_reference($ref, $type, $trans_no=0)
440 return $Refs->is_new_reference($ref, $type, $trans_no);
443 function get_reference($type, $trans_no)
447 return $Refs->_get($type, $trans_no);