missing something simple, i just know it

[richei]

I've been at this for two weeks now with nothing really to show for it, other than a horribly disfigured script that used to work.

 

I was asked to take an existing script that gets start and end dates (below)

<?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[] = "<select id='itunes_date' name='itunes_date' onchange='filter_result();'>";
                    $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'];
                        
                        if($prev_yr != $yr) {
                            $sub[] = "<optgroup label='$yr'>";
                            $prev_yr = $tm[2];
                        }
                        $sub[] = "<option value='$dte'>$dte</option>";
                        if($prev_yr != $yr) {
                            $sub[] = "</optgroup>";
                            $prev_yr = $tm[2];
                        }
                    }
                    $sub[] = "</select>";    
                                            
                    for($k = 0; $k < count($sub); $k++) {
                        $com_cont.= $sub[$k];
                    }
                ?>

I was asked to add annual totals to the optgroup output.  I've managed get a somewhat working script.  I figured it would be easier to get totals for each month, then total those to get the annual sales.  I can't get the array right though.  For whatever reason, its not putting the sales figures into the right month.  It either does 1 month and nothing else, or does all the months and put that under the first month.

 

This is the code that i have so far for that.

<?php
session_start();

$_SESSION['uid'] = 62;

function display($arg) {
    echo "<pre>";
    print_r($arg);
    echo "</pre>";
}

include 'config.php';
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();

$sub[] = "<select id='itunes_date' name='itunes_date' onchange='filter_result();'>";
$com_cont.='Select Date Range: ';
$a=0;
$pv = '';
$mont = array();
while($r = mysql_fetch_assoc($qry)) {
    
    $tm = explode('/', $r['start_date']);
    $yr = $tm[2];
    $dte = $r['start_date']. " - " .$r['end_date'];
    
    $month['startdate'][] = $r['start_date'];
    
    foreach($month as $date) {
        //echo $date[$a]."<br />";
        
        $qry_b = "SELECT start_date, partner_share_currency, extended_partner_share_currency psc FROM venzo_app_sales
                WHERE start_date = '".$date[$a]."' 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)) {
            if($pv != $r['start_date']) {
                $month['startdate']['psc_int'][] = $row['psc'];
                $pv = $r['start_date'];
            }
        }
        $a++;
    }
}

display($month);
//echo $sort."<br />";
    
    /*if($prev_yr != $yr) {
                
        //$sub[] = "<optgroup label='$yr ($".number_format($row['total'], 2).")'>";
        $sub[] = "<optgroup label='$yr'>";
        $prev_yr = $yr;

    }

    $sub[] = "<option value='$dte'>$dte</option>";
    
    if($prev_yr != $yr) {
        $sub[] = "</optgroup>";
        $prev_yr = $tm[2];
        }*/
//}
//display($month);
$sub[] = "</select>";    

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

?>

This code gives me this output

Array(    [startdate] => Array        (            [0] => 09/01/2013            [psc_int] => Array                (                    [0] => 2.58                    [1] => 50.31                    [2] => 10.32                    [3] => 10.32                    [4] => 11.18                    [5] => 27.95                    [6] => 27.95                    [7] => 10.32                    [8] => 18.90                    [9] => 0.70                    [10] => 0.70                    [11] => 0.70                    [12] => 0.70                    [13] => 1.40                    [14] => 1.27                    [15] => 1.40                    [16] => 40.32                    [17] => 76.86                    [18] => 0.70                    [19] => 1.40                )            [1] => 06/30/2013            [2] => 06/02/2013            [3] => 05/05/2013            [4] => 03/31/2013            [5] => 03/03/2013            [6] => 02/03/2013            [7] => 12/30/2012            [8] => 12/02/2012            [9] => 11/04/2012            [10] => 08/05/2012            [11] => 07/01/2012            [12] => 06/03/2012            [13] => 05/06/2012            [14] => 04/01/2012            [15] => 03/04/2012            [16] => 02/05/2012            [17] => 01/01/2012            [18] => 11/27/2011            [19] => 10/30/2011        ))

I know its bad to run queries inside of a loop, but its the only way i've found that'll give me what i'm looking for.  I'm hoping someone can tell me what i'm overlooking

[mac_gyver]

if your dates were stored as a mysql DATE data type, you could do this directly in the query for any date range. all it would take is about 4 lines of code.

[richei]

Would changing the datatype change the current content of the column?  I ask because right now there's around 120,000 records in the table and it would be an extremely long process to get everything restored.

Edited November 4, 2013 by richei

[mac_gyver]

you would do the following to convert -

 

1) backup your database and test that the backup is valid (restore it to a different location as a test.)

 

2) alter your table and add a new column with a DATE data type.

 

3) run one UPDATE query to populate the new DATE column from your existing column, using the STR_TO_DATE() statement you are currently using to produce a DATE value from the existing data.

 

4) modify and test any existing queries to use the new DATE column. to INSERT or UPDATE, just use the STR_TO_DATE() statement you are currently using to take a value in the existing format being put into the query and produce a DATE value to insert or update. in those cases where you want to SELECT the existing format, just use a DATE_FORMAT() statement in the query to take the DATE value and produce the existing formated value.

 

5) after step #4, you can drop the previous column from your table and just use the newly added DATE column.

[richei]

I did it, but the only problem is this - the script I use to import the data into that table takes date information from a txt file that's formatted like 1/1/13.  Do I have to use the str_to_date function in the insert query for that or can I leave it?

 

The new column has been added and the old column removed.  What's next?  its now formatted as 2013-10-10 (example).  Going to have to alter some other scripts too.  This page selects the dates and once selected, the sales info shows up.  Its all done with jquery.

[richei]

I'm sorry to keep bumping this, but does anyone know what mac_gyver is talking about? I'm kinda in a time crunch to get this change made.

[Barand]

Not too sure yet exactly what you are trying to achieve. Does this come anywhere close (I'm assuming your dates have been converted to yyyy-mm-dd)

$uid = 1;  // set this from your session var

$sql = "SELECT YEAR(start_date) as Year
    , MONTH(start_date) as Month
    , SUM(extended_partner_share_currency) as total_psc
FROM venzo_app_sales s
INNER JOIN
    venzo_user_apps a ON s.title = a.appname
WHERE a.uid = $uid
GROUP BY Year, Month WITH ROLLUP";

$res = $db->query($sql);
$prevYr = 0;
echo "<table border=1>\n";
echo "<tr><td>Year</td><td>Month</td><td>Total PSC</td></tr>\n";

while ($row = $res->fetch_assoc()) {
    if (is_null($row['Year'])) {
        $yr = "All years ";
    }
    elseif ($row['Year']== $prevYr) {
        $yr = ' ';
    }
    else  {
        $yr = $row['Year'];
    }
    $mth = is_null($row['Month']) ? 'Total' : $row['Month'];
    echo "<tr><td>$yr</td><td>$mth</td><td>{$row['total_psc']}</td></tr>\n";
    $prevYr = $row['Year'];
}
echo "</table>\n";

results attached

Edited November 6, 2013 by Barand

[richei]

Extremely close.  The only thing is that the monthly calculations can't be summed in the query since they have to be converted to use US currency.  I also need it in a select field

 

Also doesn't work, I get

 

Notice: Undefined variable: db in /home/venzo/public_html/intranet/salestest.php on line 31

Fatal error: Call to a member function query() on a non-object in /home/venzo/public_html/intranet/salestest.php on line 31

 

Line 31 is $res = $db->query($sql);

 

I used regular MySQL to get around that problem, but ended up with

 

Notice: Undefined variable: row in /home/venzo/public_html/intranet/salestest.php on line 40

Notice: Undefined index: in /home/venzo/public_html/intranet/salestest.php on line 40

 

Line 40 is $rows['psc_int'] = $rows['psc'] * $arr_currency[$row['partner_share_currency']];, which is the currency conversion.

 

So right now, I don't know what's going on.

[Barand]

At top, put

$db = new mysqli('HOST', 'USERNAME', 'PASSWORD', 'DATABASE');   // substitute your values

You can do the currency conversion in the query eg

... , SUM(currency * conversion_rate) as total_psc

[richei]

its kinda working.  Its back to giving me actual numbers, but they aren't right, not even close.  The only problem right now is the conversion.  I need to be able to tell it what country code to use if i'm going to do it inside the query.  Below is the revised code.

 

<?php
session_start();
include 'config.php';
$_SESSION['uid'] = 62;
function display($arg) {
 echo "<pre>";
 print_r($arg);
 echo "</pre>";
}
$qry_curr = mysql_query("select backrate, code from venzo_xe") or die(mysql_error());
$arr_currency=array();
while($row = mysql_fetch_array($qry_curr)) {
 $arr_currency[$row['code']]=$row['backrate'];  
}
//display($arr_currency);
error_reporting(-1);
$com_cont = '';
$total = 0;
$uid = 62;  // set this from your session var
foreach($arr_currency as $country => $rate) {
 
 //echo $country." rate: ".$rate."<br/>";
 $sql = "SELECT YEAR(start_date) as Year
  , MONTH(start_date) as Month, DAY(start_date) as Day, SUM(extended_partner_share_currency * $rate) total_psc
  FROM venzo_app_sales s INNER JOIN venzo_user_apps a ON s.title = a.appname WHERE a.uid = $uid GROUP BY Year, Month WITH ROLLUP";
}
$res = mysql_query($sql) or die(mysql_error());
$prevYr = 0;
$prev_yr = 0;
$sub = array();
$sub[] = "<select id='itunes_date' name='itunes_date' onchange='filter_result();'>";
$com_cont.='Select Date Range: ';
while ($rows = mysql_fetch_assoc($res)) {
 
 $rows['start_date'] = $rows['Month']."/".$rows['Day']."/".$rows['Year'];
 
    if (is_null($rows['Year'])) {
        $yr = "All years ";
    } elseif ($rows['Year']== $prevYr) {
        $yr = ' ';
    } else  {
        $yr = $rows['Year'];
    }
    $mth = is_null($rows['Month']) ? 'Total' : $rows['Month'];
 $prevYr = $rows['Year'];
 
 if($prev_yr != $rows['Year']) {
  $sub[] = "<optgroup label='$rows[Year] ($".number_format($rows['total_psc'], 2).")'>";
  $prev_yr = $rows['Year'];
 }
 $sub[] = "<option value='$rows[start_date]'>$rows[start_date]</option>";
 
 if($prev_yr != $rows['Year']) {
  $sub[] = "</optgroup>";
  $prev_yr = $rows['Year'];
 }
}
$sub[] = "</select>"; 
for($k = 0; $k < count($sub); $k++) {
 $com_cont.= $sub[$k];
}
echo $com_cont;
?>

Edited November 7, 2013 by richei

[richei]

ok, well I fiddled with it some more and am almost there.  for some reason, for the annual total, its displaying the first record for each year.  It displays just fine in the table, and even in phpmyadmin it shows it right, but when I put it into a select box, it quits working on me.

 

This is what I have for code

 

<?php
session_start();
include 'config.php';
$_SESSION['uid'] = 62;
function display($arg) {
 echo "<pre>";
 print_r($arg);
 echo "</pre>";
}
error_reporting(-1);
$com_cont = '';
$total = 0;
$uid = 62;  // set this from your session var
$sql = "SELECT DATE_FORMAT(start_date, '%m/%d/%Y') start_date,
  YEAR(start_date) as Year,
  MONTH(start_date) as Month,
  DAY(start_date) as Day,
  SUM((extended_partner_share_currency * x.backrate) * 0.80) as total_psc,
  end_date
  FROM venzo_app_sales s
  INNER JOIN venzo_xe x ON x.code = s.partner_share_currency
  INNER JOIN venzo_user_apps a ON s.title = a.appname
  WHERE a.uid = $uid GROUP BY Year desc, Month desc
  WITH ROLLUP";
$res = mysql_query($sql) or die(mysql_error());
$prevYr = 0;
$prev_st = '';
$prev_yr = 0;
$sub = array();
$sub[] = "<select id='itunes_date' name='itunes_date' onchange='filter_result();'>\n";
$com_cont.='Select Date Range: ';
echo "<table border=1>\n";
echo "<tr><td>Year</td><td>Month</td><td>Total PSC</td></tr>\n";
while ($rows = mysql_fetch_array($res)) {
 
 $mth = is_null($rows['Month']) ? 'Total' : $rows['Month'];
 
 if (is_null($rows['Year'])) {
  $yr = "All years ";
 } elseif($rows['Year'] == $prevYr) {
  $yr = ' ';
 } else {
  $yr = $rows['Year'];
  
 }
 $prevYr = $rows['Year'];
 echo "<tr>\n<td>$yr</td>\n<td>$mth</td>\n<td>".number_format($rows['total_psc'], 2)."</td>\n</tr>\n";
 
 if($prev_yr != $rows['Year'] && $rows['Year'] != '') {
  $sub[] = "<optgroup label='$rows[Year] ($".number_format($rows['total_psc']min, 2).")'>\n";
  $prev_yr = $rows['Year'];
 }
 if($prev_st != $rows['start_date']) {
  $sub[] = "<option value='$rows[start_date]'>$rows[start_date] - $rows[end_date]</option>\n";
  $prev_st = $rows['start_date'];
 }
 
 if($prev_yr != $rows['Year']) {
  $sub[] = "</optgroup>\n";
  $prev_yr = $rows['Year'];
 } 
 
}
echo "</table>\n";
$sub[] = "</select>"; 
for($k = 0; $k < count($sub); $k++) {
 $com_cont.= $sub[$k];
}
echo $com_cont;
?>

I attached a picture depicting what's happening when I run the query.

Edited November 7, 2013 by richei

[Barand]

Why the " * 0.80" in

SUM((extended_partner_share_currency * x.backrate) * 0.80) as total_psc,

[richei]

we take the other 20% as our cut, the other 80% is the client's royalties. thats the 0.80

[Barand]

This will get you the annual totals instead of value from first

SELECT 
    YEAR(s.start_date) as Year,
    annual_psc,
    DATE_FORMAT(start_date, '%m/%d/%Y') as s_date,
    DATE_FORMAT(end_date, '%m/%d/%Y') as e_date
  FROM venzo_app_sales s
  INNER JOIN venzo_user_apps a ON s.title = a.appname
  INNER JOIN 
    (
    SELECT YEAR(start_date) as yr,
    SUM((extended_partner_share_currency * x.backrate) * 0.80) as annual_psc
    FROM venzo_app_sales s
    JOIN venzo_xe x ON x.code = s.partner_share_currency
    JOIN venzo_user_apps a ON s.title = a.appname
    WHERE a.uid = 1
    GROUP BY yr
    ) as tot 
    ON YEAR(s.start_date) = tot.yr
  WHERE a.uid = 1 
  ORDER BY Year DESC, start_date DESC;

[richei]

that worked, but it indents the first entry for each year.

 

one question, the start date is used as part of another script, do I just need to remove the formatting to get it working again?

[Barand]

SQL queries don't do any indenting.

 

Remove what formatting? Keep dates in db tables as YYYY-MM-DD. Apply any formatting required either in the query (as above) or on output using PHP

[richei]

the date fomatting is changed in the query, do I just need to set the ooption value to the unformatted version of the start date?

 

I know the query doesnt indent, its something with the rest of the code.

[Barand]

The dates listed in your dropdown were m/d/y, eg 3/15/2012 - 3/31/2012.

 

However it is better if the value of the dropdown is y-m-d format so it can be used in queries without reformatting, so you would have something like

<option value="2012-03-15">3/15/2012 - 3/31/2012</option>

Easiest way is select the start_date twice eg

SELECT start_date as rawdate, FORMAT_DATE(start_date, '%m/%d/%Y') as sdate, .....

Edited November 7, 2013 by Barand

[richei]

ok, ill make the changes tonight and see how it goes.

 

thanks for all your help.

[richei]

One more question, but this one might be a tad simpler to answer.  One thing we noticed is that the annual sales aren't adding up to what's on the user's stats page.  Using the same id as an example, everything for the apps sales should add up to $4,652.73, but if you add up to what's in the drop down box, you get 4,636.69.

 

This is the code i'm using for the main page

 

$fl = mysql_query("SELECT appname FROM venzo_user_apps WHERE uid = $_SESSION[uid]");
 
 if(mysql_num_rows($fl) != 0) {
 
  $qury = mysql_query("SELECT extended_partner_share_currency, 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']."')");
 
  while($row = mysql_fetch_assoc($qury)) {
   
   $row['psc_int'] = $row['psc'] * $arr_currency[$row['partner_share_currency']];
   
   $rows[$row['label']][] = $row;
  }
  foreach($rows as $key => $row) {
   for($j = 0 ; $j < count($row); $j++) {
    $rows[$key]['total'] += $row[$j]['psc_int'];
    $rows[$key]['title'] = $row[$j]['title'];
   }
   $dolist = true;
  }
  if(isset($dolist)) {
   foreach($rows as $key => $row) {
?>
  <div class="box one">
   <h1>iOS Sales</h1>
   <span>Total Sales (To-Date):</span>
   <h2>$<?=number_format($rows[$key]['total'] * 0.80, 2); ?></h2>
   <p>Since account was opened (Updated Bi-Monthly)</p>
  </div>
<?php
   }
  }
  unset($rows);
  unset($row);

 

I don't know if the exchange rates aren't adding up somewhere in the select field or what, but the numbers should match up.

 

Also, is there an easy way of archiving these annual sales, so they can remain consistent, because right now, they're going to change based on the current rate for each of the countries we support. (around 25 countries currently).  I'm thinking just a simple 4 column table, id, uid, year, period, and total.  then all I have to do is look up this table and display the data.

[Barand]

In the code above you have

$row['psc_int'] = $row['psc'] * $arr_currency[$row['partner_share_currency']];

with no mention of venzo_xe table or backrate values.

 

 

Whereas in the query I was helping with you had

SUM((extended_partner_share_currency * x.backrate) * 0.80) as annual_psc
FROM venzo_app_sales s
JOIN venzo_xe x ON x.code = s.partner_share_currency

???

[richei]

its in a file thats included at the top of the page.

require 'currency_values.php';
$arr_currency=get_all_currency_values();

function get_all_currency_values()
{
 $qry_curr = mysql_query("select backrate, code from venzo_xe") or die(mysql_error());
 $arr_currency=array();
 while($row = mysql_fetch_array($qry_curr))
 {
  $arr_currency[$row['code']]=$row['backrate'];  
 }
 
 return $arr_currency;
}

Edited November 8, 2013 by richei

[richei]

ok, this is just plain weird.  I figured out why the numbers weren't matching.  I was missing something in the query, but now its off by just one cent.  The werid part is that its not actually off by anything.  on the front page of the user's dashboard, number_format() is rounding up a digit, but on the sales page, its not.  If i drop down to 4 decimal places, the numbers match perfectly, but as soon as i change it back to 2 places, it goes back to being off.

 

Anyone know why that could be messing up?

[Barand]

If in one you are accumulating then rounding, but in the the other you are rounding then accumulating, maybe.

[richei]

the function should work the same though, but on one page, its rounding up, but on the other, its dropping the digits.

 

Also, is there a way of doing this that doesn't involve me changing the data type? Now I have to do this for 3 other sections.

Edited November 9, 2013 by richei