Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Dashboard - Payables Due Sort
03-10-2018, 09:26 AM, (This post was last modified: 03-10-2018, 09:28 AM by VortecCPI.)
#1
Dashboard - Payables Due Sort
Not really a Bug or Problem but I changed the payables ("Supplier Invoices") due to sort by duedate prior to supplierid:

ORDER BY supptrans.duedate, suppliers.supplierid

This puts the first payment due at the top of the list, which I find helpful.

I don't have any Overdue Customer Balances or Outstanding Orders so I can not check to see if it may also be applicable there.
https://www.linkedin.com/in/eclipsepaulbecker
Reply
03-10-2018, 10:38 AM,
#2
RE: Dashboard - Payables Due Sort
Change committed.
Reply
05-09-2018, 08:34 AM, (This post was last modified: 05-09-2018, 08:35 AM by VortecCPI.)
#3
RE: Dashboard - Payables Due Sort
A few more small changes to this script...

For the two SQL queries for Supplier Invoices Due within 1 Month I still have things shown a bit out of order. Changing SQL queries to have "ORDER BY supptrans.duedate" and not other fields seems to have squared it away.

So now I have this... NOTE ONLY ONE ORDER BY FIELD!

PHP Code:
    $Sql "SELECT suppliers.supplierid,
                    currencies.decimalplaces AS currdecimalplaces,
                    SUM(supptrans.ovamount + supptrans.ovgst - supptrans.alloc) AS balance
            FROM suppliers INNER JOIN paymentterms
            ON suppliers.paymentterms = paymentterms.termsindicator
            INNER JOIN supptrans
            ON suppliers.supplierid = supptrans.supplierno
            INNER JOIN systypes
            ON systypes.typeid = supptrans.type
            INNER JOIN currencies
            ON suppliers.currcode=currencies.currabrev
            WHERE supptrans.ovamount + supptrans.ovgst - supptrans.alloc !=0
            AND supptrans.hold=0
            GROUP BY suppliers.supplierid,
                    currencies.decimalplaces
            HAVING SUM(supptrans.ovamount + supptrans.ovgst - supptrans.alloc) <> 0
            ORDER BY supptrans.duedate"
;
    
$SuppliersResult DB_query($Sql);

    while(
$SuppliersToPay DB_fetch_array($SuppliersResult)) {

        
$CurrDecimalPlaces $SuppliersToPay['currdecimalplaces'];

        
$Sql "SELECT suppliers.supplierid,
                        suppliers.suppname,
                        systypes.typename,
                        paymentterms.terms,
                        supptrans.suppreference,
                        supptrans.trandate,
                        supptrans.rate,
                        supptrans.transno,
                        supptrans.type,
                        supptrans.duedate,
                        (supptrans.ovamount + supptrans.ovgst - supptrans.alloc) AS balance,
                        (supptrans.ovamount + supptrans.ovgst ) AS trantotal,
                        supptrans.diffonexch,
                        supptrans.id
                FROM suppliers
                INNER JOIN paymentterms ON suppliers.paymentterms = paymentterms.termsindicator
                INNER JOIN supptrans ON suppliers.supplierid = supptrans.supplierno
                INNER JOIN systypes ON systypes.typeid = supptrans.type
                WHERE supptrans.supplierno = '" 
$SuppliersToPay['supplierid'] . "'
                    AND supptrans.ovamount + supptrans.ovgst - supptrans.alloc !=0
                    AND supptrans.duedate <='" 
Date('Y-m-d'mktime(0,0,0Date('n'),Date('j')+30,date('Y'))) . "'
                    AND supptrans.hold = 0
                ORDER BY supptrans.duedate"

https://www.linkedin.com/in/eclipsepaulbecker
Reply


Forum Jump:


Users browsing this thread: 1 Guest(s)