includes/references.inc

   1 <?php
   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
  20 //
  21 // FIXME:
  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
  24 //              - see fixmes below
  25 //              - remove refs table and create view instead (need e.g. CREATE VIEW support in db_import/db_export)
  26
  27 $refline_options = array(
  28         ST_JOURNAL => array('date', 'user'),
  29         ST_COSTUPDATE => array('date', 'user'),
  30
  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'),
  36
  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'),
  42
  43         ST_LOCTRANSFER => array('date', 'location', 'user'),
  44         ST_INVADJUST => array('date', 'location', 'user'),
  45
  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'),
  50
  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'),
  55 );
  56
  57 $refline_placeholders = array(
  58         'MM' => 'date',
  59         'YY' => 'date',
  60         'YYYY' => 'date',
  61         'FF' => 'date', // fiscal year
  62         'FFFF' => 'date',
  63         'UU' => 'user',
  64         'P' => 'pos',
  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.
  67 //      'C' => 'customer',
  68 //      'B' => 'branch',
  69 //      'S' => 'supplier',
  70 //      'L' => 'location'
  71 );
  72
  73 class references
  74 {
  75         var $reflines;
  76
  77         function __construct()
  78         {
  79                 $this->reflines = new reflines_db();
  80         }
  81
  82         function _legacy_line($refline)
  83         {
  84                 return strpbrk($refline['pattern'], '{}') == false;
  85         }
  86
  87         function _parse_next($type, $template, $context=null)
  88         {
  89                 global $refline_options, $refline_placeholders;
  90
  91                 // date based placeholders are always allowed, so default for non-array context is date
  92                 if (!isset($context))
  93                         $context = new_doc_date();
  94
  95                 if (is_string($context))
  96                         $context = array('date' => $context);
  97
  98                 $context['user'] = $_SESSION['wa_current_user']->user;
  99                 $context['pos'] = $_SESSION['wa_current_user']->pos;
 100                 $out = '';
 101
 102                 while(($start = strpos($template, '{')) !==false) {
 103
 104                         $out .= substr($template, 0, $start);
 105                         $stop = strpos($template, '}');
 106                         if ($stop === false) {
 107                                 display_warning(_("Invalid refline template."));
 108                                 $out .= $template; // debug
 109                                 break;
 110                         }
 111                         $ph = substr($template, $start+1, $stop-$start-1);
 112                         $template = substr($template, $stop+1);
 113
 114                         if (isset($refline_placeholders[$ph])) {
 115                                 if (!isset($context[$refline_placeholders[$ph]]))
 116                                 {
 117                                         display_warning(sprintf(_("Missing refline context data: '%s'"), $refline_placeholders[$ph]));
 118                                         $out .= $ph; // debug
 119                                 } else {
 120                                         switch ($ph)
 121                                         {
 122                                                 case 'MM':
 123                                                 case 'YY':
 124                                                 case 'YYYY':
 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));
 127                                                         break;
 128                                                 case 'FF':
 129                                                 case 'FFFF':
 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);
 132                                                         break;
 133                                                 case 'C':
 134                                                         $out .= sprintf('%d', $context['customer']);
 135                                                         break;
 136
 137                                                 case 'B':
 138                                                         $out .= sprintf('%d', $context['branch']);
 139                                                         break;
 140
 141                                                 case 'S':
 142                                                         $out .= sprintf('%d', $context['supplier']);
 143                                                         break;
 144
 145                                                 case 'L':
 146                                                         $out .= sprintf('%s', $context['location']);
 147                                                         break;
 148
 149                                                 case 'P':
 150                                                         $out .= sprintf('%s', $context['pos']);
 151                                                         break;
 152
 153                                                 case 'UU':
 154                                                         $out .= sprintf('%02d', $context['user']);
 155                                                 break;
 156                                         }
 157                                 }
 158                         } elseif (is_numeric($ph)) {
 159                                 $out .= '{'.$ph.'}'; // index placeholder
 160                         }
 161                 }
 162
 163                 $out .= $template;      // add postfix
 164                 if (!preg_match('/^([^\{]*)?\{([^\}]*)\}(.*)/', $out, $match)) {        // parse index
 165                         display_error(_("Missing numeric placeholder in refline definition."));
 166                         return $out;
 167                 }
 168
 169                 $prefix = $match[1];
 170                 $postfix = $match[3];
 171
 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);
 178
 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);
 188
 189                         // fill with zeros to the length of original index placeholder
 190                 return $prefix.sprintf('%0'.strlen($match[2]).'d', $result[0]+1).$postfix;
 191         }
 192
 193         //
 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)
 197         //
 198         function _get($type, $trans_no=0, $ref=null)
 199         {
 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];
 205
 206                 $type = db_escape($type);
 207
 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" : '');
 214                 if ($ref)
 215                 {
 216                         $sql .= " AND tbl.`$ref_fld`=".db_escape(trim($ref));
 217                         if ($trans_no)
 218                                 $sql .= " AND tbl.`$tno_fld` != ".db_escape($trans_no);
 219                 } else {
 220                         $sql .= " AND tbl.`$tno_fld`=".db_escape($trans_no);
 221                 }
 222
 223                 $result = db_query($sql, "could not test for unique reference");
 224                 if (!$result)
 225                         return false;
 226
 227                 $result = db_fetch_row($result);
 228                 return $result[0];
 229         }
 230
 231         function is_new_reference($ref, $type, $trans_no=0)
 232         {
 233                 return !$this->_get($type, $trans_no, $ref);
 234         }
 235
 236         //
 237         // Get default reference for new transaction.
 238         //
 239         function get_next($type, $line=null, $context=null)
 240         {
 241
 242                 if (isset($line))
 243                         $refline = $this->reflines->get($line);
 244                 else {
 245                         $refs = $this->reflines->get_all("trans_type=".db_escape($type)." AND `default`");
 246                         $refline = db_fetch($refs);
 247                 }
 248
 249                 if ($this->_legacy_line($refline))
 250                         return $refline['pattern'];
 251
 252                 return $this->_parse_next($type, $refline['prefix'].$refline['pattern'], $context);
 253         }
 254
 255         /**
 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.
 259         **/
 260         function normalize($ref, $type, $context, $line=null)
 261         {
 262                 if (!isset($type)) // inquiries
 263                         return $ref;
 264
 265                 if (!$line)
 266                         $line = $this->reflines->find_refline_id($ref, $type);
 267
 268                 return $this->is_valid($ref, $type, $context, $line) ? $ref : $this->get_next($type, $line, $context);
 269         }
 270
 271         //
 272         // Check reference is valid before add/update transaction.
 273         // FIXME: does not check leading zeros in number
 274         //
 275         function is_valid($reference, $type, $context=null, $line=null)
 276         {
 277                 if (!isset($line))
 278                         $line = $this->reflines->find_refline_id($reference, $type, true);
 279
 280                 if (!isset($line))
 281                         return false;
 282
 283                 $refline = $this->reflines->get($line);
 284
 285                 if ($this->_legacy_line($refline))      //legacy non-templated line
 286                         return strlen(trim($reference)) > 0;
 287
 288                 $regex = preg_quote($refline['prefix'].$refline['pattern']);
 289                 if (!is_array($context))
 290                         $context = array('date'=>$context);
 291
 292                 $context['pos'] = $_SESSION["wa_current_user"]->pos;
 293
 294                 if (is_date(@$context['date']))
 295                 {
 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];
 301                 } else
 302                 {
 303                         $month = '\d{2,}';
 304                         $year2 = '\d{2,}';
 305                         $year4 = '\d{4,}';
 306                         $f_year2 = '\d{2,}';
 307                         $f_year4 = '\d{4,}';
 308                 }
 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);
 315
 316                 $regex = preg_replace(
 317                         array(
 318                                 '/\\\{/',       // unquote placeholders
 319                                 '/\\\}/',
 320                                 '/\{MM\}/',
 321                                 '/\{YY\}/',
 322                                 '/\{YYYY\}/',
 323                                 '/\{FF\}/',
 324                                 '/\{FFFF\}/',
 325                                 '/\{C\}/',
 326                                 '/\{B\}/',
 327                                 '/\{S\}/',
 328                                 '/\{L\}/',
 329                                 '/\{UU\}/',
 330                                 '/\{P\}/',
 331                                 '/\{\d+}/',
 332                         ),
 333                         array(
 334                                 '{',
 335                                 '}',
 336                                 $month,
 337                                 $year2,
 338                                 $year4,
 339                                 $f_year2,
 340                                 $f_year4,
 341                                 $cust,
 342                                 $branch,
 343                                 $supp,
 344                                 $location,
 345                                 $user,
 346                                 $pos,
 347                                 '\d+',
 348                         ), $regex);
 349
 350                 $regex = '"^'.$regex.'"i';
 351
 352                 return preg_match($regex, $reference, $match) ? 1 : 0;
 353         }
 354
 355         //
 356         //      Save reference (and prepare next) on write transaction.
 357         //
 358         function save($type, $id, $reference, $line = null)
 359         {
 360                 if ($reference == 'auto')
 361                         return;
 362
 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");
 366
 367                 if (!isset($line))
 368                 {
 369                         $line = $this->reflines->find_refline_id($reference, $type);
 370                 }
 371
 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);
 377                 }
 378         }
 379         //
 380         // Restore previous reference (if possible) after voiding transaction.
 381         //
 382         function restore_last($type, $id)
 383         {
 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);
 388
 389                 if ($this->_legacy_line($refline)) // legacy code used with simple templates
 390                 {
 391                         $last = $this->_increment($this->get_next($type, $line), true); // find last reference used in this line
 392                         if ($reference == $last)
 393                         {
 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);
 397
 398                                 db_query($sql, "The next transaction ref for $type could not be updated");
 399                         }
 400                 }
 401         }
 402
 403         //-----------------------------------------------------------------------
 404         //
 405         //      Increments (or decrements if $back==true) reference template
 406         //
 407         function _increment($reference, $back=false)
 408         {
 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.
 415         //
 416         if (preg_match('/^(\D*?)(\d+)(.*)/', $reference, $result) == 1)
 417         {
 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
 423
 424                         return $prefix.$nextval.$postfix;
 425         }
 426         else
 427             return $reference;
 428         }
 429
 430 }
 431
 432 //----------------------------------------------------------------------------
 433 //
 434 //      Check if reference was not used so far (for other transaction than $trans_no)
 435 //
 436 function is_new_reference($ref, $type, $trans_no=0)
 437 {
 438         global $Refs;
 439
 440         return $Refs->is_new_reference($ref, $type, $trans_no);
 441 }
 442
 443 function get_reference($type, $trans_no)
 444 {
 445         global $Refs;
 446
 447         return $Refs->_get($type, $trans_no);
 448 }