ST_JOURNAL, $trans_id, $journal_item->amount, 1, $date_, $memo_);
}
+ $Refs->save($trans_type, $trans_id, $ref);
if ($new) {
add_comments($trans_type, $trans_id, $date_, $memo_);
- $Refs->save($trans_type, $trans_id, $ref);
} else {
update_comments($trans_type, $trans_id, null, $memo_);
- $Refs->update($trans_type, $trans_id, $ref);
}
add_audit_trail($trans_type, $trans_id, $date_);
commit_transaction();
}
-function get_sql_for_journal_inquiry()
+function get_sql_for_journal_inquiry($filter, $from, $to, $ref='', $memo='', $alsoclosed=false)
{
-/*
- // Tom Hallman 11 Nov 2009
- // IF(gl.type = 1... statement is for deposits/payments that may not actually result
- // in a deposit, such as when a fix is made. Without that statement (and the
- // joining of the bank_trans table), the fix deposit/payment amount would show up
- // incorrectly as only the positive side of the fix.
- $sql = "SELECT IF(ISNULL(a.gl_seq),0,a.gl_seq) as gl_seq,
- gl.tran_date,
- gl.type,
- gl.type_no,
- refs.reference,
- IF(gl.type = 1 OR gl.type = 2,
- bank_trans.amount,
- SUM(IF(gl.amount>0, gl.amount,0))) as amount,
- com.memo_,
- IF(ISNULL(u.user_id),'',u.user_id) as user_id
- FROM ".TB_PREF."gl_trans as gl
- LEFT JOIN ".TB_PREF."audit_trail as a ON
- (gl.type=a.type AND gl.type_no=a.trans_no)
- LEFT JOIN ".TB_PREF."comments as com ON
- (gl.type=com.type AND gl.type_no=com.id)
- LEFT JOIN ".TB_PREF."refs as refs ON
- (gl.type=refs.type AND gl.type_no=refs.id)
- LEFT JOIN ".TB_PREF."users as u ON
- a.user=u.id
- LEFT JOIN ".TB_PREF."bank_trans as bank_trans ON
- (gl.type=bank_trans.type AND gl.type_no=bank_trans.trans_no)
- WHERE gl.tran_date >= '" . date2sql($_POST['FromDate']) . "'
- AND gl.tran_date <= '" . date2sql($_POST['ToDate']) . "'
- AND gl.amount!=0";
- if (isset($_POST['Ref']) && $_POST['Ref'] != "") {
- $sql .= " AND reference LIKE '%". $_POST['Ref'] . "%'";
- }
- if (get_post('filterType') != -1) {
- $sql .= " AND gl.type=".get_post('filterType');
- }
- if (!check_value('AlsoClosed')) {
- $sql .= " AND gl_seq=0";
- }
- $sql .= " GROUP BY gl.type, gl.type_no";
-*/
$sql = "SELECT IF(ISNULL(a.gl_seq),0,a.gl_seq) as gl_seq,
gl.tran_date,
(gl.type=refs.type AND gl.type_no=refs.id)
LEFT JOIN ".TB_PREF."users as u ON
a.user=u.id
- WHERE gl.tran_date >= '" . date2sql($_POST['FromDate']) . "'
- AND gl.tran_date <= '" . date2sql($_POST['ToDate']) . "'
+ WHERE gl.tran_date >= '" . date2sql($from) . "'
+ AND gl.tran_date <= '" . date2sql($to) . "'
AND gl.amount!=0";
- if (isset($_POST['Ref']) && $_POST['Ref'] != "") {
- $sql .= " AND reference LIKE '%". $_POST['Ref'] . "%'";
+ if ($ref) {
+ $sql .= " AND reference LIKE ". db_escape("%$ref%");
}
- if (isset($_POST['Memo']) && $_POST['Memo'] != "") {
- $sql .= " AND com.memo_ LIKE '%". $_POST['Memo'] . "%'";
+ if ($memo) {
+ $sql .= " AND com.memo_ LIKE ". db_escape("%$memo%");
}
- if (get_post('filterType') != -1) {
- $sql .= " AND gl.type=".get_post('filterType');
+ if ($filter != -1) {
+ $sql .= " AND gl.type=".db_escape($filter);
}
- if (!check_value('AlsoClosed')) {
+ if (!$alsoclosed) {
$sql .= " AND gl_seq=0";
}
$sql .= " GROUP BY gl.type, gl.type_no";
ICON_EDIT) : '';
}
-$sql = get_sql_for_journal_inquiry();
+$sql = get_sql_for_journal_inquiry(get_post('filterType', -1), get_post('FromDate'),
+ get_post('ToDate'), get_post('Ref'), get_post('Memo'), check_value('AlsoClosed'));
$cols = array(
_("#") => array('fun'=>'journal_pos', 'align'=>'center'),
elseif ($date_system == 2)
list($year, $month, $day) = islamic_to_gregorian($year, $month, $day);
- // Pad with 0s if needed
- if (strlen($month) == 1)
- $month = "0$month";
- if (strlen($day) == 1)
- $day = "0$day";
-
- return $year."-".$month."-".$day;
+ return sprintf("%04d-%02d-%02d", $year, $month, $day);
}// end of function
function date1_greater_date2 ($date1, $date2)
return $row['reference'];
}
-//--------------------------------------------------------------------------------------------------
-
-function add_reference($type, $id, $reference)
-{
- $sql = "INSERT INTO ".TB_PREF."refs (type, id, reference)
- VALUES (".db_escape($type).", ".db_escape($id).", "
- . db_escape(trim($reference)) . ")";
-
- db_query($sql, "could not add reference entry");
-}
-
//--------------------------------------------------------------------------------------------------
function update_reference($type, $id, $reference)
{
- $sql = "UPDATE ".TB_PREF."refs SET reference=".db_escape($reference)
- ." WHERE type=".db_escape($type)." AND id=".db_escape($id);
+ $sql = "REPLACE ".TB_PREF."refs SET reference=".db_escape($reference)
+ .", type=".db_escape($type).", id=".db_escape($id);
db_query($sql, "could not update reference entry");
}
function delete_reference($type, $id)
{
- $sql = "DELETE FROM ".TB_PREF."refs WHERE type=$type AND id=".db_escape($id);
+ $sql = "DELETE FROM ".TB_PREF."refs WHERE type=".db_escape($type)." AND id=".db_escape($id);
return db_query($sql, "could not delete from reference table");
}
*/
function find_custom_file($rep)
{
- global $installed_extensions, $comp_path, $path_to_root;
+ global $installed_extensions, $path_to_root;
// customized per company version
$path = company_path();
See the License here <http://www.gnu.org/licenses/gpl-3.0.html>.
***********************************************************************/
include_once($path_to_root . "/includes/db/references_db.inc");
+//---------------------------------------------------------------------------------------------
+//
+// For now (2.3) the references system has somewhat inconsistent design scheme.
+// Most transactions store references in respective table, but this is not the case
+// for journal entries. All references regardless of type are stored also in refs table.
+// Reference uniquness now can be checked with is_new_reference() for all transactions.
+// In near future this should be fixed either with removing reference fields
+// in transaction tables, or adding ref in bank transaction/journal and removing refs table.
+//
class references
{
-
- function save($type, $id, $reference)
- {
- add_reference($type, $id, $reference);
- if ($reference != 'auto')
- $this->save_last($reference, $type);
- }
-
+ //
+ // Get reference from refs table for given transaction.
+ // Used for transactions which do not hold references (journal and bank).
+ //
function get($type, $id)
{
return get_reference($type, $id);
- }
-
- function delete($type, $id)
- {
- delete_reference($type, $id);
- }
-
- function update($type, $id, $reference)
- {
- update_reference($type, $id, $reference);
}
- // check if reference is used for any non voided transaction (used for ST_JOURNALENTRY type)
+ //
+ // Check if reference is used for any non voided transaction (used for ST_JOURNALENTRY type)
+ //
function exists($type, $reference)
{
return (find_reference($type, $reference) != null);
}
-
- function save_last($reference, $type)
- {
- $next = $this->increment($reference);
- save_next_reference($type, $next);
- }
-
+ //
+ // Get default reference on new transaction creation.
+ //
function get_next($type)
{
return get_next_reference($type);
}
//
- // Restore previous reference if voided trans ref was the last one.
+ // Check reference is valid before add/update transaction.
//
- function restore_last($type, $id)
+ function is_valid($reference)
+ {
+ return strlen(trim($reference)) > 0;
+ }
+ //
+ // Save reference (and prepare next) on write transaction.
+ //
+ function save($type, $id, $reference)
{
- $reference = $this->get($type, $id);
- $last = $this->increment($this->get_next($type), true); //decrement
- if ($reference==$last) {
- save_next_reference($type, $last);
+ update_reference($type, $id, $reference); // store in refs table
+ if ($reference == $this->get_next($type)) { // if reference was not changed from default
+ $next = $this->_increment($reference); // increment default
+ save_next_reference($type, $next);
}
}
-
- //------------------------------------
-
- function is_valid($reference)
+ //
+ // Restore previous reference (if possible) after voiding transaction.
+ //
+ function restore_last($type, $id)
{
- return strlen(trim($reference)) > 0;
+ $reference = get_reference($type, $id);
+ $prev = $this->_increment($this->get_next($type), true); //decrement
+ if ($reference==$prev) {
+ save_next_reference($type, $prev);
+ }
}
+ //-----------------------------------------------------------------------
//
// Increments (or decrements if $back==true) reference template
//
- function increment($reference, $back=false)
+ function _increment($reference, $back=false)
{
// New method done by Pete. So f.i. WA036 will increment to WA037 and so on.
// If $reference contains at least one group of digits,
else
return $reference;
}
- //------------------------------------
}
//----------------------------------------------------------------------------
$db_trans = $db_info[2];
$db_ref = $db_info[3];
- if ($db_ref != null)
- {
- if ($db_type != null) {
- $sql = "SELECT $db_ref FROM $db_name
- LEFT JOIN ".TB_PREF."voided v ON
- $db_name.$db_type=v.type AND $db_name.$db_trans=v.id
- WHERE $db_name.$db_ref='$ref' AND ISNULL(v.id)
- AND $db_name.$db_type=$type";
- } else {
- $sql = "SELECT $db_ref FROM $db_name
- LEFT JOIN ".TB_PREF."voided v ON
- v.type=$type AND $db_name.$db_trans=v.id
- WHERE $db_ref='$ref' AND ISNULL(v.id)";
- }
- $result = db_query($sql, "could not test for unique reference");
-
- return (db_num_rows($result) == 0);
- }
+ $ref = db_escape(trim($ref));
+ $type = db_escape($type);
- // it's a type that doesn't use references - shouldn't be calling here, but say yes anyways
- return true;
+ if ($db_ref == null) { // journal or bank trans store references in refs table
+ $db_ref = TB_PREF."refs";
+ $db_type = 'type';
+ $db_trans = 'id';
+ $rb_ref = 'reference';
+ }
+
+ if ($db_type != null) {
+ $sql = "SELECT $db_ref FROM $db_name
+ LEFT JOIN ".TB_PREF."voided v ON
+ $db_name.$db_type=v.type AND $db_name.$db_trans=v.id
+ WHERE $db_name.$db_ref=$ref AND ISNULL(v.id)
+ AND $db_name.$db_type=$type";
+ } else {
+ $sql = "SELECT $db_ref FROM $db_name
+ LEFT JOIN ".TB_PREF."voided v ON
+ v.type=$type AND $db_name.$db_trans=v.id
+ WHERE $db_ref=$ref AND ISNULL(v.id)";
+ }
+
+ $result = db_query($sql, "could not test for unique reference");
+
+ return (db_num_rows($result) == 0);
+
}
projects / fa-stable.git / commitdiff