It's much more fun, when you can discuss your problems with others...
You are not logged in. Please login or register.
FrontAccounting forum → Banking and General Ledger → Journal Inquiry Dates
The journal Inquiry From and To dates is defaulting to 30 days and not taking into account the User Preferences of the number of Transaction Days..
All other Inquiry selections (GL, Bank Account, Sales, Purchases..etc..) seem to pick the dates from the User Preferences..
Is this normal?
I guess you are right. Will have a look into this.
Joe
This has been fixed and committed to 2.4.7 stable repo. Thanks @rafat for detecting this.
A fixed file can be downloaded here.
/Joe
Thanks @joe,
Its working fine..
One other thing that keeps bothering me is on an (Inquiry) screen why the Person/Item does not show unless its a customer or a supplier.
Hope you can shed a reason.
@rafat what do you expect to show other than a Customer or Supplier?
@boxygen,
There are Miscellaneous entries that are mainly Bank Payments/Deposits/Transfers to others than a supplier/customer, things like Bills and expenses.
This information is already entered when we post the entry..yet its not showing when we inquire it.
Actually Customers / Suppliers are basically Subsidiary accounts of Accounts Receivable / Accounts Payable . While Banks and Cash or any other accounts are main gl Accounts. So this particular column is for Sub Accounts
@boxygen
I dont think you answered my query. Is there a reason of why the Counter Party that I inputted is not shown on an (inquiry screen that supposedly does nothing but shows what is stored on the DB).? The info is there.. and Inquiry only asks to display it.. there is no transaction taking place. I hope I am right here..unless proven otherwise..
Please be more specific about what kind of transaction on which inquiry screen and what kind of name (misc/cust/supp/qe) does not appear.
There have been fixes in this area so include your FA version as well.
Names are difficult to pull out of the FA database because they are inconveniently null in gl_trans. Thus the code has to look for them elsewhere which is quite ugly.
For example, if you wanted to see a miscellaneous name from a bank payment using journal inquiry, the code does not display it. Bank inquiry would display it because the name is in the banking trans file. But if you are using g/l inquiry or journal inquiry, the sql is not as easy.
Say you wanted to change the code in journal inquiry to display a banking payment name. You might try:
+++ b/core/gl/includes/db/gl_db_trans.inc
@@ -613,7 +613,8 @@ function get_sql_for_journal_inquiry($filter, $from, $to, $ref='', $memo='', $al
gl.tran_date,
gl.type as trans_type,
gl.type_no as trans_no,
- IFNULL(MAX(supp.supp_name), MAX(cust.name)) as name,
+ IFNULL(IFNULL(supp.supp_name, cust.name), bt.person_id) as person_name,
+
IF(ISNULL(st.supp_reference), '', st.supp_reference) AS supp_reference,
refs.reference,"
.($dimension ? " -SUM(IF(dim.dimension in(gl.dimension_id,gl.dimension2_id), gl.amount, 0)) as amount,":" SUM(IF(gl.amount>0, gl.amount,0)) as amount,")
@@ -633,6 +634,8 @@ function get_sql_for_journal_inquiry($filter, $from, $to, $ref='', $memo='', $al
(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 bt ON bt.type=gl.type AND bt.trans_no=gl.type_no AND bt.amount!=0
+ AND (bt.person_id != '' AND !ISNULL(bt.person_id))
LEFT JOIN ".TB_PREF."debtor_trans dt ON dt.type=gl.type AND gl.type_no=dt.trans_no
LEFT JOIN ".TB_PREF."debtors_master cust ON gl.person_type_id=2 AND gl.person_id=cust.debtor_no
LEFT JOIN ".TB_PREF."supp_trans st ON st.type=gl.type AND gl.type_no=st.trans_noThanks Braath
I tried the above and its showing the Misc Bank Fields in the journal Inquiry but its dropping the Customer and Supplier from non Banking transactions (Supplier/Customer Invoices..Payments ..etc).
I tried on both 2.4.4 and 2.4.6 with the same results.
Well I guess the sql still needs to use the MAX function (because one of the two gl_trans entries is null):
IFNULL(IFNULL(MAX(supp.supp_name), MAX(cust.name)), bt.person_id) as person_name,Note that quick entries do not display the name, just the quick entry number. I will leave it as an exercise for the reader to write the sql for that.
Note that g/l inquiry does display the quick entry names. It is able to do this because its sql returns the person type/person id and then it uses a function to display them. This is what journal inquiry could do as well because it is much more powerful and easier to write. In my fork, I changed the function to display hyperlinks and a magnifying glass for each name. The hyperlink takes you to the customer/supplier/qe info page and the magnifying glass shows the g/l detail for just that customer/supplier/qe using the selected date range.
@Braath
Things seem to be alright...need to do some more testing..your MAX function seem to work ok to what I was looking for.
Let me test more and will come back to you. From the first impression its amazing how a three code line changes can make a huge difference in terms of a user interface/experience.
BTW I dont use QE yet.. so I cant test it.
Thanks @rafat for pointing this out. I didn't get your query earlier. Bundle of thanks to @BraathWate for this modification. Now I can see the Value entered in the Miscellaneous Box is now printed in the CounterParty column.
Should we fix this in the core?
Joe
Yes.. I believe it should be.
rafat
My suggestion is to replace the name conjuring with a function instead of sql. This adds quick entry support as well.
--- a/core/gl/includes/db/gl_db_trans.inc
+++ b/core/gl/includes/db/gl_db_trans.inc
function get_sql_for_journal_inquiry($filter, $from, $to, $ref='', $memo='', $alsoclosed=false,
$user_id=null, $contractor_id=null, $dimension=null)
{
@@ -613,7 +614,7 @@ function get_sql_for_journal_inquiry($filter, $from, $to, $ref='', $memo='', $al
- IFNULL(MAX(supp.supp_name), MAX(cust.name)) as name,
+ IFNULL(MAX(gl.person_id), IFNULL(bt.person_id, dt.debtor_no)) as person_id,
IF(ISNULL(st.supp_reference), '', st.supp_reference) AS supp_reference,
refs.reference,"
.($dimension ? " -SUM(IF(dim.dimension in(gl.dimension_id,gl.dimension2_id), gl.amount, 0)) as amount,":" SUM(IF(gl.amount>0, gl.amount,0)) as amount,")
@@ -624,6 +625,8 @@ function get_sql_for_journal_inquiry($filter, $from, $to, $ref='', $memo='', $al
$sql.= ", st.supplier_id, dt.debtor_no ";
}
+ $sql.=", IFNULL(MAX(gl.person_type_id), IFNULL(bt.person_type_id, ".PT_CUSTOMER.")) as person_type_id";
+
$sql.= " 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)
@@ -633,10 +636,10 @@ function get_sql_for_journal_inquiry($filter, $from, $to, $ref='', $memo='', $al
(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 bt ON bt.type=gl.type AND bt.trans_no=gl.type_no AND bt.amount!=0
+ AND (bt.person_id != '' AND !ISNULL(bt.person_id))
LEFT JOIN ".TB_PREF."debtor_trans dt ON dt.type=gl.type AND gl.type_no=dt.trans_no
- LEFT JOIN ".TB_PREF."debtors_master cust ON gl.person_type_id=2 AND gl.person_id=cust.debtor_no
- LEFT JOIN ".TB_PREF."supp_trans st ON st.type=gl.type AND gl.type_no=st.trans_no
- LEFT JOIN ".TB_PREF."suppliers supp ON gl.person_type_id=3 AND gl.person_id=supp.supplier_id"
+ LEFT JOIN ".TB_PREF."supp_trans st ON st.type=gl.type AND gl.type_no=st.trans_no "
.($dimension ?
" LEFT JOIN (SELECT type, type_no, MAX(IFNULL(dimension_id, dimension2_id)) dimension FROM ".TB_PREF."gl_trans GROUP BY type, type_no) dim
ON gl.type=dim.type AND gl.type_no=dim.type_no" : '')
diff --git a/core/gl/inquiry/journal_inquiry.php b/core/gl/inquiry/journal_inquiry.php
index 70338b4..2a727eb 100644
--- a/core/gl/inquiry/journal_inquiry.php
+++ b/core/gl/inquiry/journal_inquiry.php
@@ -78,6 +78,11 @@ function systype_name($dummy, $type)
return $systypes_array[$type];
}
+function person_link($row)
+{
+ return payment_person_name($row["person_type_id"],$row["person_id"]);
+}
+
function view_link($row)
{
return get_trans_view_str($row["trans_type"], $row["trans_no"]);
@@ -121,7 +126,7 @@ $cols = array(
_("Date") =>array('name'=>'tran_date','type'=>'date','ord'=>'desc'),
_("Type") => array('fun'=>'systype_name'),
_("Trans #") => array('fun'=>'view_link'),
- _("Counterparty") => array('ord' => ''),
+ _("Counterparty") => array('fun' => 'person_link'),
_("Supplier's Reference") => 'skip',
_("Reference"),
_("Amount") => array('type'=>'amount'),Thanks @Braath Waate. I will fix this later this evening.
Joe
This has now been fixed in core. Committed to 2.4.7 stable.
/Joe
If I downloaded 2.4.7 from Sourceforge last week or so, how would I make sure I have the latest version because of this change (or, any change)?
One Bug found after this update.
The Amount for Bank Transfers is showing double. See in this image http://prnt.sc/onf5v9
Another Bug is that if in Bank Payment or Deposit nothing is defined in Miscellaneous Box then it shows like Customer [000] as shown in image below
@boxygen
Thanks for testing.
This next version should fix those problems, remove unused code, and re-add support for names on PO Deliveries.
function get_sql_for_journal_inquiry($filter, $from, $to, $ref='', $memo='', $alsoclosed=false,
$user_id=null)
{
$sql = "SELECT IFNULL(a.gl_seq,0) as gl_seq,
gl.tran_date,
gl.type as trans_type,
gl.type_no as trans_no,
IFNULL(gl.person_id, IFNULL(st.supplier_id, IFNULL(grn.supplier_id, IFNULL(dt.debtor_no, bt.person_id)))) as person_id,
IF(ISNULL(st.supp_reference), '', st.supp_reference) AS supp_reference,
refs.reference,
IF(gl.type=".ST_BANKTRANSFER.",MAX(gl.amount),SUM(IF(gl.amount>0, gl.amount,0))) as amount,
com.memo_,
IF(ISNULL(u.user_id),'',u.user_id) as user_id,
IF(gl.person_id, gl.person_type_id, IF(!ISNULL(st.supplier_id) OR !ISNULL(grn.supplier_id),". PT_SUPPLIER . "," . "IF(dt.debtor_no," . PT_CUSTOMER . "," .
"IF(bt.person_id != '' AND !ISNULL(bt.person_id), bt.person_type_id, -1)))) as person_type_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."grn_batch grn ON grn.id=gl.type_no AND gl.type=".ST_SUPPRECEIVE."
LEFT JOIN ".TB_PREF."bank_trans bt ON bt.type=gl.type AND bt.trans_no=gl.type_no AND bt.amount!=0
AND (bt.person_id != '' AND !ISNULL(bt.person_id))
LEFT JOIN ".TB_PREF."debtor_trans dt ON dt.type=gl.type AND gl.type_no=dt.trans_no
LEFT JOIN ".TB_PREF."supp_trans st ON st.type=gl.type AND gl.type_no=st.trans_no
WHERE gl.tran_date >= '" . date2sql($from) . "'
AND gl.tran_date <= '" . date2sql($to) . "'
AND gl.amount!=0";
if ($ref) {
$sql .= " AND refs.reference LIKE ". db_escape("%$ref%");
}
if ($memo) {
$sql .= " AND com.memo_ LIKE ". db_escape("%$memo%");
}
if ($filter != -1) {
$sql .= " AND gl.type=".db_escape($filter);
}
if (!$alsoclosed) {
$sql .= " AND gl_seq=0";
}
else
$sql .= " AND NOT ISNULL(a.gl_seq)";
if ($user_id != null)
$sql .= " AND user_id = ".db_escape($user_id);
$sql .= " GROUP BY gl.tran_date, a.gl_seq, gl.type, gl.type_no";
return $sql;
}diff
-- a/core/gl/includes/db/gl_db_trans.inc
+++ b/core/gl/includes/db/gl_db_trans.inc
@@ -606,25 +606,21 @@ function clear_gl_trans($type, $trans_id, $nested=false)
}
function get_sql_for_journal_inquiry($filter, $from, $to, $ref='', $memo='', $alsoclosed=false,
- $user_id=null, $contractor_id=null, $dimension=null)
+ $user_id=null)
{
-
$sql = "SELECT IFNULL(a.gl_seq,0) as gl_seq,
gl.tran_date,
gl.type as trans_type,
gl.type_no as trans_no,
- IFNULL(MAX(supp.supp_name), MAX(cust.name)) as name,
+ IFNULL(gl.person_id, IFNULL(st.supplier_id, IFNULL(grn.supplier_id, IFNULL(dt.debtor_no, bt.person_id)))) as person_id,
IF(ISNULL(st.supp_reference), '', st.supp_reference) AS supp_reference,
- refs.reference,"
- .($dimension ? " -SUM(IF(dim.dimension in(gl.dimension_id,gl.dimension2_id), gl.amount, 0)) as amount,":" SUM(IF(gl.amount>0, gl.amount,0)) as amount,")
- ."com.memo_,
- IF(ISNULL(u.user_id),'',u.user_id) as user_id";
-
- if ($contractor_id > 0) {
- $sql.= " FROM ".TB_PREF."gl_trans as gl
+ refs.reference,
+ IF(gl.type=".ST_BANKTRANSFER.",MAX(gl.amount),SUM(IF(gl.amount>0, gl.amount,0))) as amount,
+ com.memo_,
+ IF(ISNULL(u.user_id),'',u.user_id) as user_id,
+ IF(gl.person_id, gl.person_type_id, IF(!ISNULL(st.supplier_id) OR !ISNULL(grn.supplier_id),". PT_SUPPLIER . "," . "IF(dt.debtor_no," . PT_CUSTOMER . "," .
+ "IF(bt.person_id != '' AND !ISNULL(bt.person_id), bt.person_type_id, -1)))) as person_type_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
@@ -633,14 +629,12 @@ function get_sql_for_journal_inquiry($filter, $from, $to, $ref='', $memo='', $al
(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."grn_batch grn ON grn.id=gl.type_no AND gl.type=".ST_SUPPRECEIVE."
+ LEFT JOIN ".TB_PREF."bank_trans bt ON bt.type=gl.type AND bt.trans_no=gl.type_no AND bt.amount!=0
+ AND (bt.person_id != '' AND !ISNULL(bt.person_id))
LEFT JOIN ".TB_PREF."debtor_trans dt ON dt.type=gl.type AND gl.type_no=dt.trans_no
- LEFT JOIN ".TB_PREF."debtors_master cust ON gl.person_type_id=2 AND gl.person_id=cust.debtor_no
LEFT JOIN ".TB_PREF."supp_trans st ON st.type=gl.type AND gl.type_no=st.trans_no
- LEFT JOIN ".TB_PREF."suppliers supp ON gl.person_type_id=3 AND gl.person_id=supp.supplier_id"
- .($dimension ?
- " LEFT JOIN (SELECT type, type_no, MAX(IFNULL(dimension_id, dimension2_id)) dimension FROM ".TB_PREF."gl_trans GROUP BY type, type_no) dim
- ON gl.type=dim.type AND gl.type_no=dim.type_no" : '')
- ." WHERE gl.tran_date >= '" . date2sql($from) . "'
+ WHERE gl.tran_date >= '" . date2sql($from) . "'
AND gl.tran_date <= '" . date2sql($to) . "'
AND gl.amount!=0";
if ($ref) {
@@ -661,14 +655,6 @@ function get_sql_for_journal_inquiry($filter, $from, $to, $ref='', $memo='', $al
if ($user_id != null)
$sql .= " AND user_id = ".db_escape($user_id);
- if ($contractor_id > 0) {
- $sql.= " AND (dt.debtor_no =".$contractor_id;
- $sql.= " OR st.supplier_id =".$contractor_id.") ";
- }
-
- if ($dimension != null)
- $sql .= " AND dim.dimension = ".db_escape($dimension);
-
$sql .= " GROUP BY gl.tran_date, a.gl_seq, gl.type, gl.type_no";
return $sql;@Braath Waate and @boxygen
The last change by @Brath Waate has been committed to 2.4.7 stable. Thank you both for testing and fixing this.
A new file can be downloaded here.
/Joe
FrontAccounting forum → Banking and General Ledger → Journal Inquiry Dates
Powered by PunBB, supported by Informer Technologies, Inc.
Currently installed 4 official extensions. Copyright © 2003–2009 PunBB.