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 ***********************************************************************/
13 //--------------------------------------------------------------------------------------
15 function get_sql_for_view_transactions($filtertype, $from, $to, &$trans_ref)
17 $db_info = get_systype_db_info($filtertype);
22 $table_name = $db_info[0];
23 $type_name = $db_info[1];
24 $trans_no_name = $db_info[2];
25 $trans_ref = $db_info[3];
26 $trans_date = $db_info[4];
28 $sql = "SELECT DISTINCT t.$trans_no_name as trans_no";
31 $sql .= " ,t.$trans_ref as ref ";
33 $sql .= ", r.reference as ref";
34 $sql .= ",t.$trans_date as trans_date";
36 $sql .= ", t.$type_name as type";
37 $sql .= " FROM $table_name t LEFT JOIN ".TB_PREF."voided v ON"
38 ." t.$trans_no_name=v.id AND v.type=$filtertype";
40 $sql .= " LEFT JOIN ".TB_PREF."refs r ON t.$trans_no_name=r.id AND r.type=$filtertype";
42 $sql .= " WHERE ISNULL(v.`memo_`)";
43 if ($from != null && $to != null)
45 $sql .= " AND t.$trans_no_name >= ".db_escape($from). "
46 AND t.$trans_no_name <= ".db_escape($to);
47 if ($type_name != null)
48 $sql .= " AND t.`$type_name` = ".db_escape($filtertype);
50 elseif ($type_name != null)
51 $sql .= " AND t.`$type_name` = ".db_escape($filtertype);
53 $sql .= " ORDER BY t.$trans_no_name";