Need help with getting year totals

richei · October 29, 2013, 7:05am

ok, I have a script that that gets date ranges from a database to display as part of another script. I need to modify this script to display annual totals for the optgroup. This is the code as I have it now
[php]$com_cont = ‘’;
$qry_a = mysql_query(“SELECT start_date, end_date FROM venzo_app_sales
WHERE title IN (SELECT appname FROM venzo_user_apps WHERE uid = '”.$_SESSION[‘uid’]."’)
GROUP BY start_date ORDER BY STR_TO_DATE(start_date,’%m/%d/%Y’) desc"
) or die(mysql_error());

$sub = array();
$sub[] = “”;
$com_cont.='Select Date Range: ';

while($r = mysql_fetch_array($qry_a)) {

$tm = explode('/', $r['start_date']);
$yr = $tm[2];								
$dte = $r['start_date']. " - " .$r['end_date'];
				
$qry = mysql_query("SELECT partner_share_currency, extended_partner_share_currency AS psc FROM venzo_app_sales 
WHERE title IN (SELECT appname FROM venzo_user_apps WHERE uid = '".$_SESSION['uid']."')");
			
while($as = mysql_fetch_array($qry)) {
	//$as = mysql_fetch_array($qry);
	$as['total'] = $as['psc'] * $arr_currency[$as['partner_share_currency']];
				
}
//if($prev_yrs == $yr) {
$abs_total += $as['total'];
//	$prev_yrs = $tm[2];
//}
					
					
if($prev_yr != $yr) {
			
	$sub[] = "<optgroup label='$yr ($".number_format($abs_total, 2).")'>";
	$prev_yr = $tm[2];
}
$sub[] = "<option value='$dte'>$dte</option>";									
if($prev_yr != $yr) {
	$sub[] = "</optgroup>";
	$prev_yr = $tm[2];
}

}
$sub[] = “”;

for($k = 0; $k < count($sub); $k++) {
$com_cont.= $sub[$k];
}[/php]
This displays something like
2013
09/01/2013 - 09/30/2013
08/01/2013 - 08/31/2013
etc
2012
etc
2011
and so on

What I have to do is get the annual totals to display next to the year, so I end up with
2013 ($1000.00)
09/01/2013 - 09/30/2013
08/01/2013 - 08/31/2013
etc
2012 ($800.00)
etc
2011 ($100.00)
and so on

I’ve gotten it to return values, but they’re never correct. I know i’m close, but can’t figure out what i’m doing wrong. Any help would be greatly appreciated.

Topcoder · October 29, 2013, 2:19pm

Richei, wouldn’t it be easier just to sum it up in the mysql query?

It also looks like your first query gets a date range, but the query inside the loop doesn’t limit it to the data range you selected, so it appears to be totaling everything.

richei · October 29, 2013, 4:54pm

No, because I don’t want to limit the calculation to just one month. I can’t use SUM in the query because the results to have to converted to US currency first

Topcoder · October 29, 2013, 6:20pm

And you can’t convert the currency in the query?

richei · October 29, 2013, 6:27pm

dont think so, wouldnt know how to it though. theres several counties in there too.

Topcoder · October 29, 2013, 6:42pm

What are you using to convert the currency, I don’t see anything that resembles that in what you posted.

richei · October 29, 2013, 7:03pm

theres an Included file that contains the code for that. the conversion is there though, as[psc]=

Topcoder · October 29, 2013, 8:02pm

I guess what I’m struggling with is this.

[php]$qry = mysql_query(“SELECT partner_share_currency, extended_partner_share_currency AS psc FROM venzo_app_sales
WHERE title IN (SELECT appname FROM venzo_user_apps WHERE uid = '”.$_SESSION[‘uid’]."’)");

while($as = mysql_fetch_array($qry)) {
	//$as = mysql_fetch_array($qry);
	$as['total'] = $as['psc'] * $arr_currency[$as['partner_share_currency']];
				
}[/php]

Are you expecting more then 1 row to come back? if you are, you keep resetting $as[‘total’] to the last row that’s returned from the query. I’m assuming you only get 1 row back.

richei · October 29, 2013, 8:05pm

Dude, you’re amazing That’s supposed to be a += I’ll switch it when i get home tonight and see how it goes

richei · October 29, 2013, 9:21pm

That didn’t do the trick, was hoping that’s all it was

richei · October 30, 2013, 8:34am

Well, I made a tiny bit of progress.

I decided to put all the dates into an array, took me 4 hours to do that, but i’m still not any further a long. I know what I need to do, just have no clue how to go about doing it. Updated code is below [php]<?php
session_start();
include ‘config.php’;
//ini_set(‘display_errors’,1);
//ini_set(‘display_startup_errors’,1);
//error_reporting(-1);
require ‘currency_values.php’;
$arr_currency=get_all_currency_values();

$com_cont = ‘’;
$prev_year = ‘’;
$prev_yr = ‘’;

$qry_a = mysql_query(“SELECT start_date, end_date FROM venzo_app_sales
WHERE title IN (SELECT appname FROM venzo_user_apps WHERE uid = '”.$_SESSION[‘uid’]."’)
GROUP BY start_date ORDER BY STR_TO_DATE(start_date,’%m/%d/%Y’) desc"
) or die(mysql_error());

$sub = array();
$drange = array();

$sub[] = “”;
$com_cont.='Select Date Range: ';

while($r = mysql_fetch_assoc($qry_a)) {

$tm = explode('/', $r['start_date']);
$yr = $tm[2];
$dte = $r['start_date']. " - " .$r['end_date'];

$sdate[] = $r['start_date'];
$months = array_unique($sdate);

if($prev_yr != $yr) {
	
	for($j=0; $j < count($months); $j++) {
		$qry = mysql_query("SELECT label, partner_share_currency, extended_partner_share_currency AS psc 
				 FROM venzo_app_sales WHERE sales_or_returns = 'S' AND title IN (SELECT appname FROM venzo_user_apps WHERE uid = '".$_SESSION['uid']."')
				 AND start_date = '$months[$j]'");
	 }
	
	while($row = mysql_fetch_assoc($qry)) {
		$row['psc_int'] = $row['psc'] * $arr_currency[$row['partner_share_currency']]; // currency conversion
		$rows[$row['label']][] = $row;
	}
	
	foreach($rows as $key => $row) {
		for($i = 0; $i < count($row); $i++) {
			$rows[$key]['total'] += $row[$i]['psc_int'];
			$total += $row['total'];					
			echo $total."<br />";
		}
	}
	
	$sub[] = "<optgroup label='$yr ($".number_format($total, 2).")'>";
	$prev_yr = $yr;
}

$sub[] = "<option value='$dte'>$dte</option>";

if($prev_yr != $yr) {
	$sub[] = "</optgroup>";
	$prev_yr = $tm[2];
}

}
$sub[] = “”;

for($k = 0; $k < count($sub); $k++) {
$com_cont.= $sub[$k];
}
echo $com_cont;

?>[/php]

Topcoder · October 30, 2013, 2:13pm

You’re still doing that loop without adding…

[php] while($row = mysql_fetch_assoc($qry)) {
$row[‘psc_int’] = $row[‘psc’] * $arr_currency[$row[‘partner_share_currency’]]; // currency conversion
$rows[$row[‘label’]][] = $row;
}[/php]

So are you really only get 1 row back per query?

richei · October 30, 2013, 5:45pm

it works in the other scripts, but its probably not working as it should.

im on my phonit works in the other scripts, but its probably not working as it should.

I think the totals script needs an overhaul at this point. what im using now is part of a date picking script for a sales script

Topcoder · October 30, 2013, 6:27pm

I understand it a little better now, but I don’t understand where $row[‘total’] gets populated. Wouldn’t $total always be blank… If you make the change below wouldn’t that sum up the totals?

Change:

[php] foreach($rows as $key => $row) {
for($i = 0; $i < count($row); $i++) {
$rows[$key][‘total’] += $row[$i][‘psc_int’];
$total += $row[‘total’];
echo $total."
";
}
}[/php]

To:

[php] foreach($rows as $key => $row) {
for($i = 0; $i < count($row); $i++) {
$rows[$key][‘total’] += $row[$i][‘psc_int’];
$total += $row[$i][‘psc_int’];
echo $total."
";
}
}[/php]

richei · October 30, 2013, 7:24pm

Didn’t work.

I’m thinking maybe its the second query that’s messing things up. Maybe instead of trying to do it month by month, there’s a way to do it with a given range? I already have an array with the months in it, if that can be used to create 2 other variables, a fresh start date and an end date.

idk, i’m thinking out loud now. If you had this to do, how you would go about doing it?

Topcoder · October 30, 2013, 8:25pm

I would try to do it all in a sql statement and where ever you are pulling the data from for the currency conversion I would toss that in a database as well and get the results you want with 1 query.

richei · October 30, 2013, 8:53pm

the conversion rates are in a table. ill post the code for it.

richei · November 1, 2013, 7:21am

Well, I made a tiny bit of progress on this. I have part of the array created, but it gets wiped out when add in the conversions. This is the array print_r[code]Array
(
[2013] => Array
(
[startdate] => Array
(
[0] => 09/01/2013
[1] => 06/30/2013
[2] => 06/02/2013
[3] => 05/05/2013
[4] => 03/31/2013
[5] => 03/03/2013
[6] => 02/03/2013
)

    )

[2012] => Array
    (
        [startdate] => Array
            (
                [0] => 12/30/2012
                [1] => 12/02/2012
                [2] => 11/04/2012
                [3] => 08/05/2012
                [4] => 07/01/2012
                [5] => 06/03/2012
                [6] => 05/06/2012
                [7] => 04/01/2012
                [8] => 03/04/2012
                [9] => 02/05/2012
                [10] => 01/01/2012
            )

    )

[2011] => Array
    (
        [startdate] => Array
            (
                [0] => 11/27/2011
                [1] => 10/30/2011
            )

    )

)[/code]and this is the code[php]<?php
session_start();
$_SESSION[‘uid’] = 62;
function display($arg) {
echo “

”;

print_r($arg);

echo “

”;
}

include ‘config.php’;
//ini_set(‘display_errors’,1);
//ini_set(‘display_startup_errors’,1);
error_reporting(-1);
require ‘currency_values.php’;
$arr_currency=get_all_currency_values();

$com_cont = ‘’;
$total = 0;

$qry = mysql_query(“SELECT start_date, end_date FROM venzo_app_sales WHERE title IN (SELECT appname FROM venzo_user_apps WHERE uid = '”.$_SESSION[‘uid’]."’) GROUP BY start_date ORDER BY STR_TO_DATE(start_date,’%m/%d/%Y’) desc") or die(mysql_error());

$sub = array();
$dt = array();
$dates = array();
$sort = array();

$sub[] = “”;
$com_cont.='Select Date Range: ';

while($r = mysql_fetch_assoc($qry)) {

$tm = explode('/', $r['start_date']);
$yr = $tm[2];
$dte = $r['start_date']. " - " .$r['end_date'];
$month[$yr]['startdate'][] = $r['start_date'];

}
display($month);
$sub[] = “”;

for($k = 0; $k < count($sub); $k++) {
$com_cont.= $sub[$k];
}[/php]This is what I need the array to look like[code]
[2011] => Array
(
[startdate] => Array
(
[0] => 11/27/2011
[psc_int] => Array
(
[0] => 1.00
[1] => 2.00
)
[1] => 10/30/2011
[psc_int] => Array
(
[0] => 1.00
[1] => 2.00
)
)

    )[/code]

But when I add in[php]foreach ($month as $year => $start) {
$dates = array_shift($month[$year]);
}
foreach($dates as $dt) {
for($q=0; $q < count($dt); $q++) {
$qry_b = “SELECT partner_share_currency, extended_partner_share_currency psc FROM venzo_app_sales
WHERE start_date = '”.$dt[$q]."’ AND title IN (SELECT appname FROM venzo_user_apps WHERE uid = “.$_SESSION[‘uid’].”)
ORDER BY STR_TO_DATE(start_date,’%m/%d/%Y’) desc";
$qry_c = mysql_query($qry_b) or die(mysql_error());
}
}

while($row = mysql_fetch_assoc($qry_c)) {
	$month[$yr]['startdate']['psc_int'][] = $row['psc'];
}[/php]$month[$yr]['startdate']['psc_int'][] = $row['psc']; wipes out the array completely, leaving just the year

richei · November 5, 2013, 7:26am

ok, well I’ve been told that what I need done can be done in the query if switch the data type to date, which I’ve done (which has pretty much broken the sales page for now). But I don’t know how to set up the query to get the results I need.