FrontAccounting forum — Error while trying to edit batched invoice

Re: Error while trying to edit batched invoice

2014-06-30T01:54:12Z

Hi,

I have a gotten a similar error message but under different circumstances.

I 'transferred' information from my old software and this error happened when:

sales_order_details.id does not match up with debtor_trans_details.src_id (which i initially left as 0).

After I adjusted the src_id, eveything was fine and dandy again

Re: Error while trying to edit batched invoice

2013-10-21T11:52:19Z

Sorry, still cannot reproduce the problem. Replying the scenario does not give any errors. I think you have somewhat broken FA database. You can eventually post the bug report to mantis providing SQL dump from your database (unless it contains any confidentaila data).
Janusz

Re: Error while trying to edit batched invoice

2013-10-11T03:08:48Z

The said function that does the job is in sales/includes/db/cust_trans_db.inc

function get_customer_trans_version($type, $trans_no) {
    if (!is_array($trans_no))
        $trans_no = array( $trans_no );

    $sql= 'SELECT trans_no, version FROM '.TB_PREF. 'debtor_trans
            WHERE type='.db_escape($type).' AND (';

    foreach ($trans_no as $key=>$trans)
        $trans_no[$key] =     'trans_no='.db_escape($trans_no[$key]);

    $sql .= implode(' OR ', $trans_no) . ')';

    $res = db_query($sql, 'document version retreival');
    
    $vers = array();
    while($mysql=db_fetch($res)) {
        $vers[$mysql['trans_no']] = $mysql['version'];
    }
    return $vers;
}

If there are no transaction or just one transaction only then what would happen to the orphaned OR?

Also, there may be a typo in the field name 'type' being 'tpe' since both exist. The table definition is:

CREATE TABLE IF NOT EXISTS `0_debtor_trans` (
  `trans_no` int(11) unsigned NOT NULL default '0',
  `type` smallint(6) unsigned NOT NULL default '0',
  `version` tinyint(1) unsigned NOT NULL default '0',
  `debtor_no` int(11) unsigned default NULL,
  `branch_code` int(11) NOT NULL default '-1',
  `tran_date` date NOT NULL default '0000-00-00',
  `due_date` date NOT NULL default '0000-00-00',
  `reference` varchar(60) NOT NULL default '',
  `tpe` int(11) NOT NULL default '0',
  `order_` int(11) NOT NULL default '0',
  `ov_amount` double NOT NULL default '0',
  `ov_gst` double NOT NULL default '0',
  `ov_freight` double NOT NULL default '0',
  `ov_freight_tax` double NOT NULL default '0',
  `ov_discount` double NOT NULL default '0',
  `alloc` double NOT NULL default '0',
  `rate` double NOT NULL default '1',
  `ship_via` int(11) default NULL,
  `dimension_id` int(11) NOT NULL default '0',
  `dimension2_id` int(11) NOT NULL default '0',
  `payment_terms` int(11) default NULL,
  PRIMARY KEY  (`type`,`trans_no`),
  KEY `debtor_no` (`debtor_no`,`branch_code`),
  KEY `tran_date` (`tran_date`)
) ENGINE=InnoDB;

Should the line:

        $trans_no[$key] =     'trans_no='.db_escape($trans_no[$key]);

        $trans_no[$key] =     'trans_no='.db_escape($value);

Re: Error while trying to edit batched invoice

2013-10-04T21:02:55Z

Ok so here was the process:

1) I created three delivery notes
2) I created a batch invoice for these three notes.

I realized that I made a mistake at this point and wanted to edit the text of the invoice so I went to "Customer Transactions" found the transaction and then hit the edit button. This was when I got the error. I feel like it has to do with the fact that the invoice covered multiple delivery notes, as I'm able to edit other invoices.

Re: Error while trying to edit batched invoice

2013-10-04T20:39:49Z

I cannot reproduce the problem. What was exact order of operations resulting in this error?
Janusz

Error while trying to edit batched invoice

2013-10-04T02:30:10Z

I'm receiving the following error when going to */sales/customer_invoice.php?ModifyInvoice=2

DATABASE ERROR : document version retreival
error code : 1064
error message : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 2
sql that failed was : SELECT trans_no, version FROM 0_debtor_trans WHERE type='13' AND ()

Seems like the error is in the () at the end, since that isn't good SQL but not sure how to go about fixing this.

My Setup:
FrontAccounting 2.3.18
MySQL 5.0.8
PHP 5.3.27
Apache 2.2.25