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 //--------------------------------------------------------------------------------
14 // Base function for adding a GL transaction
15 // $date_ is display date (non-sql)
16 // $amount is in $currency currency
17 // if $currency is not set, then defaults to no conversion
19 function add_gl_trans($type, $trans_id, $date_, $account, $dimension, $dimension2, $memo_,
20 $amount, $currency=null, $person_type_id=null, $person_id=null, $err_msg="", $rate=0)
22 global $use_audit_trail;
24 $date = date2sql($date_);
25 if ($currency != null)
28 $amount_in_home_currency = to_home_currency($amount, $currency, $date_);
30 $amount_in_home_currency = round2($amount * $rate, user_price_dec());
33 $amount_in_home_currency = round2($amount, user_price_dec());
34 if ($dimension == null || $dimension < 0)
36 if ($dimension2 == null || $dimension2 < 0)
38 if (isset($use_audit_trail) && $use_audit_trail)
40 if ($memo_ == "" || $memo_ == null)
41 $memo_ = $_SESSION["wa_current_user"]->username;
43 $memo_ = $_SESSION["wa_current_user"]->username . " - " . $memo_;
45 if (!is_subledger_account($account))
46 $person_id = $person_type_id = null;
48 $sql = "INSERT INTO ".TB_PREF."gl_trans ( type, type_no, tran_date,
49 account, dimension_id, dimension2_id, memo_, amount";
51 if ($person_type_id != null)
52 $sql .= ", person_type_id, person_id";
56 $sql .= "VALUES (".db_escape($type).", ".db_escape($trans_id).", '$date',
57 ".db_escape($account).", ".db_escape($dimension).", "
58 .db_escape($dimension2).", ".db_escape($memo_).", "
59 .db_escape($amount_in_home_currency);
61 if ($person_type_id != null)
62 $sql .= ", ".db_escape($person_type_id).", ". db_escape($person_id);
67 $err_msg = "The GL transaction could not be inserted";
69 db_query($sql, $err_msg);
70 return $amount_in_home_currency;
73 //--------------------------------------------------------------------------------
75 // GL Trans for standard costing, always home currency regardless of person
76 // $date_ is display date (non-sql)
77 // $amount is in HOME currency
79 function add_gl_trans_std_cost($type, $trans_id, $date_, $account, $dimension, $dimension2,
80 $memo_, $amount, $person_type_id=null, $person_id=null, $err_msg="")
83 return add_gl_trans($type, $trans_id, $date_, $account, $dimension, $dimension2, $memo_,
84 $amount, null, $person_type_id, $person_id, $err_msg);
89 // Function for even out rounding problems
90 function add_gl_balance($type, $trans_id, $date_, $amount, $person_type_id=null, $person_id=null)
92 $amount = round2($amount, user_price_dec());
94 return add_gl_trans($type, $trans_id, $date_, get_company_pref('exchange_diff_act'), 0, 0, "",
95 $amount, null, $person_type_id, $person_id, "The balanced GL transaction could not be inserted");
100 //--------------------------------------------------------------------------------
102 function get_gl_transactions($from_date, $to_date, $trans_no=0,
103 $account=null, $dimension=0, $dimension2=0, $filter_type=null,
104 $amount_min=null, $amount_max=null)
106 global $show_voided_gl_trans;
108 $from = date2sql($from_date);
109 $to = date2sql($to_date);
111 $sql = "SELECT ".TB_PREF."gl_trans.*, "
112 .TB_PREF."chart_master.account_name FROM "
114 LEFT JOIN ".TB_PREF."voided v ON "
115 .TB_PREF."gl_trans.type_no=v.id AND v.type=".TB_PREF."gl_trans.type,"
116 .TB_PREF."chart_master"
117 ." WHERE ".TB_PREF."chart_master.account_code=".TB_PREF."gl_trans.account
119 AND tran_date >= '$from'
120 AND tran_date <= '$to'";
121 if (isset($show_voided_gl_trans) && $show_voided_gl_trans == 0)
122 $sql .= " AND ".TB_PREF."gl_trans.amount <> 0";
124 $sql .= " AND ".TB_PREF."gl_trans.type_no LIKE ".db_escape('%'.$trans_no);
126 if ($account != null)
127 $sql .= " AND ".TB_PREF."gl_trans.account = ".db_escape($account);
130 $sql .= " AND ".TB_PREF."gl_trans.dimension_id = ".($dimension<0?0:db_escape($dimension));
132 if ($dimension2 != 0)
133 $sql .= " AND ".TB_PREF."gl_trans.dimension2_id = ".($dimension2<0?0:db_escape($dimension2));
135 if ($filter_type != null AND is_numeric($filter_type))
136 $sql .= " AND ".TB_PREF."gl_trans.type= ".db_escape($filter_type);
138 if ($amount_min != null)
139 $sql .= " AND ABS(".TB_PREF."gl_trans.amount) >= ABS(".db_escape($amount_min).")";
141 if ($amount_max != null)
142 $sql .= " AND ABS(".TB_PREF."gl_trans.amount) <= ABS(".db_escape($amount_max).")";
144 $sql .= " ORDER BY tran_date, counter";
146 return db_query($sql, "The transactions for could not be retrieved");
150 //--------------------------------------------------------------------------------
152 function get_gl_trans($type, $trans_id)
154 $sql = "SELECT gl.*, cm.account_name, IF(ISNULL(refs.reference), '', refs.reference) AS reference FROM "
155 .TB_PREF."gl_trans as gl
156 LEFT JOIN ".TB_PREF."chart_master as cm ON gl.account = cm.account_code
157 LEFT JOIN ".TB_PREF."refs as refs ON (gl.type=refs.type AND gl.type_no=refs.id)"
158 ." WHERE gl.type= ".db_escape($type)
159 ." AND gl.type_no = ".db_escape($trans_id)
160 ." AND gl.amount <> 0"
161 ." ORDER BY counter";
162 return db_query($sql, "The gl transactions could not be retrieved");
165 //--------------------------------------------------------------------------------
167 function get_gl_wo_cost_trans($trans_id, $cost_type=-1)
169 $sql = "SELECT costing.*, gl.*, chart.account_name, com.memo_ FROM "
170 .TB_PREF."wo_costing costing, "
171 .TB_PREF."gl_trans gl LEFT JOIN ".TB_PREF."comments com ON gl.type=com.type AND gl.type_no=com.id,"
172 .TB_PREF."chart_master chart
174 costing.workorder_id=".db_escape($trans_id)
175 ." AND chart.account_code=gl.account
176 AND gl.type=costing.trans_type
177 AND gl.type_no=costing.trans_no";
178 if ($cost_type != -1)
179 $sql .= " AND costing.cost_type=".db_escape($cost_type);
180 $sql .= " AND amount < 0";
182 return db_query($sql, "The gl transactions could not be retrieved");
185 function get_gl_balance_from_to($from_date, $to_date, $account, $dimension=0, $dimension2=0)
187 $from = date2sql($from_date);
188 $to = date2sql($to_date);
190 $sql = "SELECT SUM(amount) FROM ".TB_PREF."gl_trans
191 WHERE account='$account'";
192 if ($from_date != "")
193 $sql .= " AND tran_date > '$from'";
195 $sql .= " AND tran_date < '$to'";
197 $sql .= " AND dimension_id = ".($dimension<0?0:db_escape($dimension));
198 if ($dimension2 != 0)
199 $sql .= " AND dimension2_id = ".($dimension2<0?0:db_escape($dimension2));
201 $result = db_query($sql, "The starting balance for account $account could not be calculated");
203 $row = db_fetch_row($result);
207 //--------------------------------------------------------------------------------
209 function get_gl_trans_from_to($from_date, $to_date, $account, $dimension=0, $dimension2=0)
211 $from = date2sql($from_date);
212 $to = date2sql($to_date);
214 $sql = "SELECT SUM(amount) FROM ".TB_PREF."gl_trans
215 WHERE account='$account'";
216 if ($from_date != "")
217 $sql .= " AND tran_date >= '$from'";
219 $sql .= " AND tran_date <= '$to'";
221 $sql .= " AND dimension_id = ".($dimension<0?0:db_escape($dimension));
222 if ($dimension2 != 0)
223 $sql .= " AND dimension2_id = ".($dimension2<0?0:db_escape($dimension2));
225 $result = db_query($sql, "Transactions for account $account could not be calculated");
227 $row = db_fetch_row($result);
228 return (float)$row[0];
231 //----------------------------------------------------------------------------------------------------
232 function get_balance($account, $dimension, $dimension2, $from, $to, $from_incl=true, $to_incl=true)
234 $sql = "SELECT SUM(IF(amount >= 0, amount, 0)) as debit,
235 SUM(IF(amount < 0, -amount, 0)) as credit, SUM(amount) as balance
236 FROM ".TB_PREF."gl_trans,".TB_PREF."chart_master,"
237 .TB_PREF."chart_types, ".TB_PREF."chart_class
238 WHERE ".TB_PREF."gl_trans.account=".TB_PREF."chart_master.account_code AND "
239 .TB_PREF."chart_master.account_type=".TB_PREF."chart_types.id
240 AND ".TB_PREF."chart_types.class_id=".TB_PREF."chart_class.cid AND";
242 if ($account != null)
243 $sql .= " account=".db_escape($account)." AND";
245 $sql .= " dimension_id = ".($dimension<0?0:db_escape($dimension))." AND";
246 if ($dimension2 != 0)
247 $sql .= " dimension2_id = ".($dimension2<0?0:db_escape($dimension2))." AND";
248 $from_date = date2sql($from);
250 $sql .= " tran_date >= '$from_date' AND";
252 $sql .= " tran_date > IF(ctype>0 AND ctype<".CL_INCOME.", '0000-00-00', '$from_date') AND";
253 $to_date = date2sql($to);
255 $sql .= " tran_date <= '$to_date' ";
257 $sql .= " tran_date < '$to_date' ";
259 $result = db_query($sql,"No general ledger accounts were returned");
261 return db_fetch($result);
264 //--------------------------------------------------------------------------------
266 function get_budget_trans_from_to($from_date, $to_date, $account, $dimension=0, $dimension2=0)
269 $from = date2sql($from_date);
270 $to = date2sql($to_date);
272 $sql = "SELECT SUM(amount) FROM ".TB_PREF."budget_trans
273 WHERE account=".db_escape($account);
274 if ($from_date != "")
275 $sql .= " AND tran_date >= '$from' ";
277 $sql .= " AND tran_date <= '$to' ";
279 $sql .= " AND dimension_id = ".($dimension<0?0:db_escape($dimension));
280 if ($dimension2 != 0)
281 $sql .= " AND dimension2_id = ".($dimension2<0?0:db_escape($dimension2));
282 $result = db_query($sql,"No budget accounts were returned");
284 $row = db_fetch_row($result);
287 //-------------------------------------------------------------------------------------
289 function exists_gl_budget($date_, $account, $dimension, $dimension2)
291 $sql = "SELECT account FROM ".TB_PREF."budget_trans WHERE account=".db_escape($account)
292 ." AND tran_date='$date_' AND
293 dimension_id=".db_escape($dimension)." AND dimension2_id=".db_escape($dimension2);
294 $result = db_query($sql, "Cannot retreive a gl transaction");
296 return (db_num_rows($result) > 0);
299 function add_update_gl_budget_trans($date_, $account, $dimension, $dimension2, $amount)
301 $date = date2sql($date_);
303 if (exists_gl_budget($date, $account, $dimension, $dimension2))
304 $sql = "UPDATE ".TB_PREF."budget_trans SET amount=".db_escape($amount)
305 ." WHERE account=".db_escape($account)
306 ." AND dimension_id=".db_escape($dimension)
307 ." AND dimension2_id=".db_escape($dimension2)
308 ." AND tran_date='$date'";
310 $sql = "INSERT INTO ".TB_PREF."budget_trans (tran_date,
311 account, dimension_id, dimension2_id, amount, memo_) VALUES ('$date',
312 ".db_escape($account).", ".db_escape($dimension).", "
313 .db_escape($dimension2).", ".db_escape($amount).", '')";
315 db_query($sql, "The GL budget transaction could not be saved");
318 function delete_gl_budget_trans($date_, $account, $dimension, $dimension2)
320 $date = date2sql($date_);
322 $sql = "DELETE FROM ".TB_PREF."budget_trans WHERE account=".db_escape($account)
323 ." AND dimension_id=".db_escape($dimension)
324 ." AND dimension2_id=".db_escape($dimension2)
325 ." AND tran_date='$date'";
326 db_query($sql, "The GL budget transaction could not be deleted");
329 function get_only_budget_trans_from_to($from_date, $to_date, $account, $dimension=0, $dimension2=0)
332 $from = date2sql($from_date);
333 $to = date2sql($to_date);
335 $sql = "SELECT SUM(amount) FROM ".TB_PREF."budget_trans
336 WHERE account=".db_escape($account)
337 ." AND tran_date >= '$from' AND tran_date <= '$to'
338 AND dimension_id = ".db_escape($dimension)
339 ." AND dimension2_id = ".db_escape($dimension2);
340 $result = db_query($sql,"No budget accounts were returned");
342 $row = db_fetch_row($result);
346 //--------------------------------------------------------------------------------
347 // Stores journal/bank transaction tax details if applicable
349 function add_gl_tax_details($gl_code, $trans_type, $trans_no, $amount, $ex_rate, $date, $memo, $included=0, $net_amount = null)
351 $tax_type = is_tax_account($gl_code);
352 if(!$tax_type) return; // $gl_code is not tax account
354 $tax = get_tax_type($tax_type);
355 //if ($gl_code == $tax['sales_gl_code'])
356 if ($trans_type == ST_SALESINVOICE || $trans_type == ST_CUSTDELIVERY || $trans_type == ST_CUSTCREDIT)
358 // we have to restore net amount as we cannot know the base amount
359 if ($net_amount===null) {
360 if ($tax['rate'] == 0) {
361 // display_warning(_("You should not post gl transactions
362 // to tax account with zero tax rate."));
365 // calculate net amount
366 $net_amount = $amount/$tax['rate']*100;
369 add_trans_tax_details($trans_type, $trans_no, $tax['id'], $tax['rate'], $included,
370 $amount, $net_amount, $ex_rate, $date, $memo);
374 //--------------------------------------------------------------------------------
376 // Store transaction tax details for fiscal purposes with 'freezed'
377 // actual tax type rate.
379 function add_trans_tax_details($trans_type, $trans_no, $tax_id, $rate, $included,
380 $amount, $net_amount, $ex_rate, $tran_date, $memo)
383 $sql = "INSERT INTO ".TB_PREF."trans_tax_details
384 (trans_type, trans_no, tran_date, tax_type_id, rate, ex_rate,
385 included_in_price, net_amount, amount, memo)
386 VALUES (".db_escape($trans_type)."," . db_escape($trans_no).",'"
387 .date2sql($tran_date)."',".db_escape($tax_id).","
388 .db_escape($rate).",".db_escape($ex_rate).",".($included ? 1:0).","
389 .db_escape($net_amount).","
390 .db_escape($amount).",".db_escape($memo).")";
392 db_query($sql, "Cannot save trans tax details");
395 //----------------------------------------------------------------------------------------
397 function get_trans_tax_details($trans_type, $trans_no)
399 $sql = "SELECT ".TB_PREF."trans_tax_details.*, "
400 .TB_PREF."tax_types.name AS tax_type_name, "
401 .TB_PREF."trans_tax_details.rate AS effective_rate, "
402 .TB_PREF."tax_types.rate AS rate
403 FROM ".TB_PREF."trans_tax_details,".TB_PREF."tax_types
404 WHERE trans_type = ".db_escape($trans_type)."
405 AND trans_no = ".db_escape($trans_no)."
406 AND (net_amount != 0 OR amount != 0)
407 AND ".TB_PREF."tax_types.id = ".TB_PREF."trans_tax_details.tax_type_id";
409 return db_query($sql, "The transaction tax details could not be retrieved");
412 //----------------------------------------------------------------------------------------
414 function void_trans_tax_details($type, $type_no)
416 $sql = "UPDATE ".TB_PREF."trans_tax_details SET amount=0, net_amount=0
417 WHERE trans_no=".db_escape($type_no)
418 ." AND trans_type=".db_escape($type);
420 db_query($sql, "The transaction tax details could not be voided");
423 //----------------------------------------------------------------------------------------
425 function clear_trans_tax_details($type, $type_no)
427 $sql = "DELETE FROM ".TB_PREF."trans_tax_details
428 WHERE trans_no=".db_escape($type_no)
429 ." AND trans_type=".db_escape($type);
431 db_query($sql, "The transaction tax details could not be cleared");
434 function get_tax_summary($from, $to)
436 $fromdate = date2sql($from);
437 $todate = date2sql($to);
440 SUM(IF(trans_type=".ST_CUSTCREDIT." || trans_type=".ST_SUPPINVOICE
441 ." || trans_type=".ST_JOURNAL.",-1,1)*
442 IF(trans_type=".ST_BANKDEPOSIT." || trans_type=".ST_SALESINVOICE
443 ." || (trans_type=".ST_JOURNAL ." AND amount<0)"
444 ." || trans_type=".ST_CUSTCREDIT.", net_amount*ex_rate,0)) net_output,
446 SUM(IF(trans_type=".ST_CUSTCREDIT." || trans_type=".ST_SUPPINVOICE
447 ." || trans_type=".ST_JOURNAL.",-1,1)*
448 IF(trans_type=".ST_BANKDEPOSIT." || trans_type=".ST_SALESINVOICE
449 ." || (trans_type=".ST_JOURNAL ." AND amount<0)"
450 ." || trans_type=".ST_CUSTCREDIT.", amount*ex_rate,0)) payable,
452 SUM(IF(trans_type=".ST_CUSTCREDIT." || trans_type=".ST_SUPPINVOICE.",-1,1)*
453 IF(trans_type=".ST_BANKDEPOSIT." || trans_type=".ST_SALESINVOICE
454 ." || (trans_type=".ST_JOURNAL ." AND amount<0)"
455 ." || trans_type=".ST_CUSTCREDIT.", 0, net_amount*ex_rate)) net_input,
457 SUM(IF(trans_type=".ST_CUSTCREDIT." || trans_type=".ST_SUPPINVOICE.",-1,1)*
458 IF(trans_type=".ST_BANKDEPOSIT." || trans_type=".ST_SALESINVOICE
459 ." || (trans_type=".ST_JOURNAL ." AND amount<0)"
460 ." || trans_type=".ST_CUSTCREDIT.", 0, amount*ex_rate)) collectible,
464 FROM ".TB_PREF."tax_types ttype,
465 ".TB_PREF."trans_tax_details taxrec
466 WHERE taxrec.tax_type_id=ttype.id
467 AND taxrec.trans_type != ".ST_CUSTDELIVERY."
468 AND taxrec.tran_date >= '$fromdate'
469 AND taxrec.tran_date <= '$todate'
471 //display_error($sql);
472 return db_query($sql,"Cannot retrieve tax summary");
475 //--------------------------------------------------------------------------------
476 // Write/update journal entries.
478 function write_journal_entries(&$cart, $reverse, $use_transaction=true)
482 $date_ = $cart->tran_date;
483 $ref = $cart->reference;
484 $memo_ = $cart->memo_;
485 $trans_type = $cart->trans_type;
486 $new = $cart->order_id == 0;
489 $cart->order_id = get_next_trans_no($trans_type);
491 $trans_id = $cart->order_id;
493 if ($use_transaction)
497 void_journal_trans($trans_type, $trans_id, false);
499 foreach ($cart->gl_items as $journal_item)
501 // post to first found bank account using given gl acount code.
502 $is_bank_to = is_bank_account($journal_item->code_id);
504 add_gl_trans($trans_type, $trans_id, $date_, $journal_item->code_id,
505 $journal_item->dimension_id, $journal_item->dimension2_id,
506 $journal_item->reference, $journal_item->amount, null,
507 $journal_item->person_type_id,
508 $journal_item->person_id);
512 add_bank_trans($trans_type, $trans_id, $is_bank_to, $ref,
513 $date_, $journal_item->amount, 0, "", get_company_currency(),
514 "Cannot insert a destination bank transaction");
516 // store tax details if the gl account is a tax account
517 add_gl_tax_details($journal_item->code_id,
518 ST_JOURNAL, $trans_id, $journal_item->amount, 1, $date_, $memo_);
521 $Refs->save($trans_type, $trans_id, $ref);
523 add_comments($trans_type, $trans_id, $date_, $memo_);
525 update_comments($trans_type, $trans_id, null, $memo_);
528 add_audit_trail($trans_type, $trans_id, $date_);
532 //$reversingDate = date(user_date_display(),
533 // Mktime(0,0,0,get_month($date_)+1,1,get_year($date_)));
534 $reversingDate = begin_month(add_months($date_, 1));
536 $trans_id_reverse = get_next_trans_no($trans_type);
538 foreach ($cart->gl_items as $journal_item)
540 $is_bank_to = is_bank_account($journal_item->code_id);
542 add_gl_trans($trans_type, $trans_id_reverse, $reversingDate,
543 $journal_item->code_id, $journal_item->dimension_id, $journal_item->dimension2_id,
544 $journal_item->reference, -$journal_item->amount,
545 null, $journal_item->person_type_id, $journal_item->person_id);
549 add_bank_trans($trans_type, $trans_id_reverse, $is_bank_to, $ref,
550 $reversingDate, -$journal_item->amount,
551 0, "", get_company_currency(),
552 "Cannot insert a destination bank transaction");
554 // store tax details if the gl account is a tax account
555 add_gl_tax_details($journal_item->code_id,
556 ST_JOURNAL, $trans_id, $journal_item->amount, 1, $reversingDate, $memo_);
559 add_comments($trans_type, $trans_id_reverse, $reversingDate, $memo_);
561 $Refs->save($trans_type, $trans_id_reverse, $ref);
562 add_audit_trail($trans_type, $trans_id_reverse, $reversingDate);
565 if ($use_transaction)
566 commit_transaction();
571 //--------------------------------------------------------------------------------------------------
573 function exists_gl_trans($type, $trans_id)
575 $sql = "SELECT type_no FROM ".TB_PREF."gl_trans WHERE type=".db_escape($type)
576 ." AND type_no=".db_escape($trans_id);
577 $result = db_query($sql, "Cannot retreive a gl transaction");
579 return (db_num_rows($result) > 0);
582 //--------------------------------------------------------------------------------------------------
584 function void_gl_trans($type, $trans_id, $nested=false)
589 $sql = "UPDATE ".TB_PREF."gl_trans SET amount=0 WHERE type=".db_escape($type)
590 ." AND type_no=".db_escape($trans_id);
592 db_query($sql, "could not void gl transactions for type=$type and trans_no=$trans_id");
595 commit_transaction();
598 //----------------------------------------------------------------------------------------
600 function clear_gl_trans($type, $trans_id, $nested=false)
605 $sql = "DELETE FROM ".TB_PREF."gl_trans WHERE type=".db_escape($type)
606 ." AND type_no=".db_escape($trans_id);
608 db_query($sql, "could not clear gl transactions for type=$type and trans_no=$trans_id");
611 commit_transaction();
614 //----------------------------------------------------------------------------------------
616 function void_journal_trans($type, $type_no, $use_transaction=true)
618 if ($use_transaction)
621 void_bank_trans($type, $type_no, true);
622 // void_gl_trans($type, $type_no, true); // this is done above
623 // void_trans_tax_details($type, $type_no); // ditto
625 if ($use_transaction)
626 commit_transaction();
629 function get_sql_for_journal_inquiry($filter, $from, $to, $ref='', $memo='', $alsoclosed=false)
632 $sql = "SELECT IF(ISNULL(a.gl_seq),0,a.gl_seq) as gl_seq,
637 SUM(IF(gl.amount>0, gl.amount,0)) as amount,
639 IF(ISNULL(u.user_id),'',u.user_id) as user_id
640 FROM ".TB_PREF."gl_trans as gl
641 LEFT JOIN ".TB_PREF."audit_trail as a ON
642 (gl.type=a.type AND gl.type_no=a.trans_no)
643 LEFT JOIN ".TB_PREF."comments as com ON
644 (gl.type=com.type AND gl.type_no=com.id)
645 LEFT JOIN ".TB_PREF."refs as refs ON
646 (gl.type=refs.type AND gl.type_no=refs.id)
647 LEFT JOIN ".TB_PREF."users as u ON
649 WHERE gl.tran_date >= '" . date2sql($from) . "'
650 AND gl.tran_date <= '" . date2sql($to) . "'
653 $sql .= " AND reference LIKE ". db_escape("%$ref%");
656 $sql .= " AND com.memo_ LIKE ". db_escape("%$memo%");
659 $sql .= " AND gl.type=".db_escape($filter);
662 $sql .= " AND gl_seq=0";
665 $sql .= " AND NOT ISNULL(a.gl_seq)";
667 $sql .= " GROUP BY tran_date, gl_seq, gl.type, gl.type_no";