Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
StockUsageGraph.php skipping periods with no sales
11-09-2020, 05:15 AM, (This post was last modified: 11-09-2020, 08:43 AM by TurboPT. Edit Reason: Format code block for PHP )
#1
StockUsageGraph.php skipping periods with no sales
        Hi folks



any specific reason for skipping periods with no sales by not passing $UsageArray ?

PHP Code:
if (!$UsageRow){
    break;


We would rather see everything

Thanks!
Reply
11-09-2020, 08:41 AM, (This post was last modified: 11-09-2020, 08:56 AM by TurboPT.)
#2
RE: StockUsageGraph.php skipping periods with no sales
I can't think of any specific reason, but the code history shows that it has been this way for 15 years.

(doesn't mean we can't change it, just checking the reference to see if there was any relative info)

Would likely be a configurable option if we do, though.
Reply
11-09-2020, 11:33 AM, (This post was last modified: 11-09-2020, 11:48 AM by TurboPT.)
#3
RE: StockUsageGraph.php skipping periods with no sales
@HDeriauFF

So, looking into this last night, the reason that we break with !$UsageRow is because the loop count handling surrounding the row retrieval can be more than the query rows found, so the loop exits.

There is currently no handling to cover gaps within the period range, and that is what I believe you are looking to have, based on the September = 0 in the list of values?
(which would make a plot in the graph for 9/20, if it was handled)
Reply
11-10-2020, 01:38 AM, (This post was last modified: 11-10-2020, 01:42 AM by HDeriauFF.)
#4
RE: StockUsageGraph.php skipping periods with no sales
TurboPT Wrote:@HDeriauFF

So, looking into this last night, the reason that we break with !$UsageRow is because the loop count handling surrounding the row retrieval can be more than the query rows found, so the loop exits.

There is currently no handling to cover gaps within the period range, and that is what I believe you are looking to have, based on the September = 0 in the list of values?
(which would make a plot in the graph for 9/20, if it was handled)

Correct: to avoid retrieving 12 rows no matter what but only periods within the 12 previous periods, it needs to exist

Looking at the queries, it appears that it should be using query (with the CASE statement) the same as stock usage to get the correct counts without skipping any period if zero
Reply
11-10-2020, 02:13 AM, (This post was last modified: 11-10-2020, 11:07 AM by TurboPT. Edit Reason: Format code block for PHP )
#5
RE: StockUsageGraph.php skipping periods with no sales
   

I replaced the queries with the ones used in StockUsage (with minor changes) and now get the expected result 

PHP Code:
$sql "SELECT periods.periodno,
        periods.lastdate_in_period,
        SUM(CASE WHEN (stockmoves.type=10 Or stockmoves.type=11 OR stockmoves.type=28)
                        AND stockmoves.hidemovt=0
                        AND stockmoves.stockid = '" 
$_GET['StockID'] . "'
                        AND stockmoves.loccode='" 
$_GET['StockLocation'] . "'
                    THEN -stockmoves.qty ELSE 0 END) AS qtyused
        FROM periods LEFT JOIN stockmoves
            ON periods.periodno=stockmoves.prd
        WHERE periods.periodno >'" 
. ($CurrentPeriod 24) . "'
        AND stockmoves.loccode='" 
$_GET['StockLocation'] . "'
        GROUP BY periods.periodno,
            periods.lastdate_in_period
        ORDER BY periodno ASC LIMIT 24"

Reply
11-10-2020, 11:11 AM, (This post was last modified: 11-10-2020, 11:34 AM by TurboPT.)
#6
RE: StockUsageGraph.php skipping periods with no sales
Thanks @HDeriauFF for the code sample, I'll try that either tomorrow or by Wednesday night to give it a try.

I'll deal with the configurable aspect of whether to show/hide zero periods for those that do not want it to appear that way.
(the config option will likely default to not show, as it's been that way for 15 years)
Reply
11-11-2020, 07:11 AM,
#7
RE: StockUsageGraph.php skipping periods with no sales
Also, just a quick note about the X axis date format: when viewing a 24 month period, all dates end up overlapping and impossible to read and might even not notice there is a missing month in the sequence. I changed my date format to mm/yy and all 24 indexes are now readable.
Reply
11-12-2020, 11:13 AM,
#8
RE: StockUsageGraph.php skipping periods with no sales
@HDeriauFF ... what do you set for $CurrentPeriod in the WHERE clause change for:

PHP Code:
        WHERE periods.periodno '" . ($CurrentPeriod - 24) . "' 

... I don't see that we have that variable in existing code.
Reply
11-12-2020, 11:23 AM, (This post was last modified: 11-12-2020, 11:26 AM by HDeriauFF.)
#9
RE: StockUsageGraph.php skipping periods with no sales
Oh yes. You need to add this before the queries:

$CurrentPeriod = GetPeriod(Date($_SESSION['DefaultDateFormat']),$db) ;
Reply
11-13-2020, 03:14 PM,
#10
RE: StockUsageGraph.php skipping periods with no sales
Ok, thanks for that info.
You must be running a slightly older webERP version? ... for I believe the 2nd parameter to GetPeriod, is no longer needed.
Reply


Forum Jump:


Users browsing this thread: 1 Guest(s)