Solved

get fiscal quarters based off fiscal year start in PHP

Posted on 2011-09-15
27
1,835 Views
Last Modified: 2012-06-22
I need to build a system that allows users to query information by quarter, and those quarters are set up based of the fiscal year start.  I have an idea of how to do it, but not sure the exact syntax.

$fiscalStart = (from database select)

$q1 = $fiscalStart (but less than 3?)
$q2 = $fiscalStart (+3 but less than 6?)
$q3 = $fiscalStart (+6 but less than 9?)
$q4 = $fiscalStart (+9 but less than 12?)

Am i on the right track?
0
Comment
Question by:axessJosh
  • 14
  • 13
27 Comments
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 36542812
You can probably use something like "now + 3 months."  I'll experiment with it a little bit and give you a solution, then I'll add the solution into this article.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 36542877
http://www.laprbass.com/RAY_temp_josh.php
<?php // RAY_temp_josh.php
error_reporting(E_ALL);


// FISCAL QUARTERS BASED ON FISCAL YEARS


// REQUIRED FOR PHP 5.1+
date_default_timezone_set('America/Chicago');

// SOME TEST DATES FOR THE FISCAL YEARS
$fys = array
( 'CALENDAR' => 'January 1'
, 'ACADEMIC' => 'July 1'
, 'FEDERAL'  => 'October 1'
)
;

// COMPUTE THE QUARTERS
foreach ($fys as $key => $start)
{
    $res["q1"] = date('c', strtotime($start));
    $res["q2"] = date('c', strtotime($start . ' + 3 Months'));
    $res["q3"] = date('c', strtotime($start . ' + 6 Months'));
    $res["q4"] = date('c', strtotime($start . ' + 9 Months'));

    // SAVE THESE ELEMENTS
    $new[$key] = $res;
}

// SHOW THE WORK PRODUCT
echo "<pre>";
var_dump($new);

Open in new window

0
 
LVL 2

Author Comment

by:axessJosh
ID: 36544802
Actually may be easier than that.  I have a table in my DB that has all the months listed.  I really only need to know what month each report is being filed for.  Then, when the admin queries the data, i need to lump 3 months into a quarter, and decide the months of each quarter based on the month their fiscal year starts.  
0
 
LVL 108

Accepted Solution

by:
Ray Paseur earned 250 total points
ID: 36545212
We can get the first day and last day of each quarter.  Then you can use a SELECT query with the DATETIME columns BETWEEN start and end of quarter.  Does that make sense?
<?php // RAY_temp_josh.php
error_reporting(E_ALL);


// FISCAL QUARTERS BASED ON FISCAL YEARS


// REQUIRED FOR PHP 5.1+
date_default_timezone_set('America/Chicago');

// SOME TEST DATES FOR THE FISCAL YEARS
$fys = array
( 'CALENDAR' => 'January 1'
, 'ACADEMIC' => 'July 1'
, 'FEDERAL'  => 'October 1'
)
;

// COMPUTE THE QUARTERS
foreach ($fys as $key => $start)
{
    // THE END OF THE YEAR
    $nextyear           = date('c', strtotime($start . ' + 12 Months'));

    // THE FIRST DAY OF THE QUARTER
    $res["q1"]["alpha"] = date('c', strtotime($start));
    $res["q2"]["alpha"] = date('c', strtotime($start . ' + 3 Months'));
    $res["q3"]["alpha"] = date('c', strtotime($start . ' + 6 Months'));
    $res["q4"]["alpha"] = date('c', strtotime($start . ' + 9 Months'));

    // THE LAST DAY OF THE QUARTER
    $res["q1"]["omega"] = date('c', strtotime($res["q2"]["alpha"] . ' - 1 Day'));
    $res["q2"]["omega"] = date('c', strtotime($res["q3"]["alpha"] . ' - 1 Day'));
    $res["q3"]["omega"] = date('c', strtotime($res["q4"]["alpha"] . ' - 1 Day'));
    $res["q4"]["omega"] = date('c', strtotime($nextyear           . ' - 1 Day'));

    // SAVE THESE ELEMENTS
    $new[$key] = $res;
}

// SHOW THE WORK PRODUCT
echo "<pre>";
var_dump($new);

Open in new window

0
 
LVL 2

Author Comment

by:axessJosh
ID: 36545243
So would my SELECT statement use DATETIME Between q1 alpha and q1 omega for instance?
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 36545512
Exactly!  Let's say you have a row with a DATETIME column called "mydate"  -- your query might look something like this.
SELECT sum(cash_out) FROM mytable WHERE mydate BETWEEN '$q1_alpha' AND '$q1_omega'
0
 
LVL 2

Author Comment

by:axessJosh
ID: 36570070
I'm wondering if this can be even simpler for my purpose.  

I really only need the months to remind the user which months to input based on the quarter.  I am going to have a pull down menu to select the quarter (1,2,3,4) then put the months included based on math from the start month of the fiscal year.

If possible, I'd like to just pull something like:

$fiscalStart = form database
$q1Begin = $fiscalStart
$q1End = $fiscalStart + 3
$q2Begin = $fiscalStart + 4
$q2End = $fiscalStart +6
$q3Begin = $fiscalStart +7
$q3End = $fiscalStart + 9
$q4Begin = $fiscalStart + 10
$q4End = $fiscalStart + 12

SELECT monthID, monthName from tblMonths

can it be that simple?

0
 
LVL 2

Author Comment

by:axessJosh
ID: 36570392
realized its not that simple, if the $fiscalStart is more than 1, eventually the quarter variable will go over 12.

how can i have it set to restart at 1 if it goes over 12, but continue to add from $fiscalStart?
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 36573040
See this article, Practical Application number 11, or try running the code I posted above.  It really is as simple as the examples will show.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html

Not sure I get the idea of having a DB table of month names.  Months don't change very often, and there are twelve of them.  If you have a full date, getting the month is as simple as using date('m') or something like that.

Best of luck with your project, ~Ray
0
 
LVL 2

Author Comment

by:axessJosh
ID: 36573832
I have no doubt that I am trying to make it more difficult that it should be.  Thanks for your patience.

I tried the code in application 11.  I don't need the entire timestamp, but rather, just the month name.

I'm a bit confused at how to proceed.  I need to output to a select menu the following:

Quarter 1 (fiscalstart Month name - q1 end month name)
Quarter 2 (q2 month name - q2end month name)
and so on...

i need the values to be either 1, 2, 3, 4 which I can get by setting them as static to the value field.  Essentially, I just need the months for explanation purposes and not much more.
0
 
LVL 2

Author Comment

by:axessJosh
ID: 36573922
Figured out a bit more.  I used the code above to get the start and end.  However, now all the starts are correct, but the end months all show Jan.


// Get quarter Data 
$start = $row_rsFiscalStrt['fiscStart'];


 // SOME TEST DATES FOR THE FISCAL YEARS
$fys = array
( 'CALENDAR' => 'January 1'
, 'ACADEMIC' => 'July 1'
, 'FEDERAL'  => 'October 1'
)
;

// COMPUTE THE QUARTERS
foreach ($fys as $key => $start)
{
    // THE END OF THE YEAR
    $nextyear           = date('M', strtotime($start . ' + 12 Months'));

    // THE FIRST DAY OF THE QUARTER
    $res["q1"]["alpha"] = date('M', strtotime($start));
    $res["q2"]["alpha"] = date('M', strtotime($start . ' + 3 Months'));
    $res["q3"]["alpha"] = date('M', strtotime($start . ' + 6 Months'));
    $res["q4"]["alpha"] = date('M', strtotime($start . ' + 9 Months'));

    // THE LAST DAY OF THE QUARTER
    $res["q1"]["omega"] = date('M', strtotime($res["q2"]["alpha"] . ' - 1 Day'));
    $res["q2"]["omega"] = date('M', strtotime($res["q3"]["alpha"] . ' - 1 Day'));
    $res["q3"]["omega"] = date('M', strtotime($res["q4"]["alpha"] . ' - 1 Day'));
    $res["q4"]["omega"] = date('M', strtotime($nextyear           . ' - 1 Day'));

    // SAVE THESE ELEMENTS
    $new[$key] = $res;
}
?>

<tr>
    <td><p>Current Quarter:</p>      </td>
    <td><select name="fiscalSelect">
      <option value="1">Quarter 1 (<?php echo $res['q1'] ['alpha']; ?> - <?php echo $res['q1'] ['omega']; ?>)</option>
      <option value="1">Quarter 2 (<?php echo $res['q2'] ['alpha']; ?> - <?php echo $res['q2'] ['omega']; ?>)</option>
      <option value="1">Quarter 3 (<?php echo $res['q3'] ['alpha']; ?> - <?php echo $res['q3'] ['omega']; ?>)</option>
      <option value="1">Quarter 4 (<?php echo $res['q4'] ['alpha']; ?> - <?php echo $res['q4'] ['omega']; ?>)</option>
    </select>      </td>
  </tr>

Open in new window

0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 36580053
I don't need the entire timestamp, but rather, just the month name. -- That is not exactly true.

A timestamp is the number of seconds since the beginning of the Unix Epoch.  The Unix Epoch began on January 1, 1970 at midnight GMT.

You have to start with the entire timestamp in order to get the month name.  And since you are dealing with fiscal years instead of calendar years you probably need to know the month and year together.  You need to create the $res array in the manner I showed using the date('c') pattern instead of the date('M') pattern.  After it is created you will have the "entire timestamp" in the form of an ISO-8601 datetime string.  Then you can use the elements of the array with strtotime() and date() to extract the months for use in the HTML form.  The EE article mentioned earlier explains it in detail and gives examples.

In the code snippet posted immediately above, the $start variable is set on line 2 and overwritten on line 14.  Not sure if that is what you want to do.
0
 
LVL 2

Author Comment

by:axessJosh
ID: 36587508
Got it, looks like my double variable as you mentioned is my problem.  

I am pulling the group fiscal start month from my database as an integer, and converting that to the appropriate month.  That should be the start to compute the quarters.

I am not sure what the foreach loop is doing that is creating the start variable.  What should I do there to add my selected start date to the loop?
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 108

Expert Comment

by:Ray Paseur
ID: 36587620
Hmm... The "double variable" may be part of the problem.  However using the output from date('M') in any subsequent computations is a good way to ensure that catastrophe is not left to chance.  One key to success here will be to use the ISO-8601 DATETIME strings for all internal representations of date and time values.  It's a widely accepted standard for a reason!

If you have an integer month, you might use mktime() together with date() to create the ISO-8601 date.  Or you might use something like this:

$m = 7;
$d = date('c', strtotime( date('Y') . '-' . $m . '-' . '1'));
var_dump($d);

The code will assemble a string that says something like 2011-7-1 which is not a valid ISO-8601 date, but strtotime is smart enough to interpret it correctly.
http://www.laprbass.com/RAY_strtotime.php?s=2011-9-1

And date('c') will turn the resulting timestamp into a valid ISO-8601 date string.  Then you can use the algorithm I posted above to compute the boundaries of the fiscal quarters.  
0
 
LVL 2

Author Comment

by:axessJosh
ID: 36587928
ok, makes sense.

What I dont see is where to put the code you mentioned here to make the connection in the algorithm on when to start based on the user criteria.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 36588500
I didn't "mention" the code, I wrote and tested the code!  Where would we find the user criteria?
0
 
LVL 2

Author Comment

by:axessJosh
ID: 36588900
I have a DW recordset (i know you don' t like Dreamweaver) pulling the fiscal year from the group table in my DB.

 
$usrVar_rsFiscalStrt = "-1";
if (isset($_SESSION['MM_Username'])) {
  $usrVar_rsFiscalStrt = $_SESSION['MM_Username'];
}
mysql_select_db($database_con_db_local, $con_db_local);
$query_rsFiscalStrt = sprintf("SELECT tblgroup.fiscStart, tblmonths.monthID, tblmonths.monthLabel, tblgroup.gID, tblusers.username FROM tblgroup, tblmonths, tblusers WHERE tblgroup.fiscStart = tblmonths.monthID AND tblusers.username = %s", GetSQLValueString($usrVar_rsFiscalStrt, "text"));
$rsFiscalStrt = mysql_query($query_rsFiscalStrt, $con_db_local) or die(mysql_error());
$row_rsFiscalStrt = mysql_fetch_assoc($rsFiscalStrt);
$totalRows_rsFiscalStrt = mysql_num_rows($rsFiscalStrt);

Open in new window


That is where i was coming up with my month Label the original start variable.

// Get quarter Data
$start = $row_rsFiscalStrt['monthLabel'];


I am also getting the Month ID in that recordset so could just as easily use that instead.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 36589432
Please use var_dump($row_rsFiscalStrt) and show me one of the rows, thanks.
0
 
LVL 2

Author Comment

by:axessJosh
ID: 36589459
Here you go
array(5) { ["fiscStart"]=> string(1) "4" ["monthID"]=> string(1) "4" ["monthLabel"]=> string(5) "April" ["gID"]=> string(1) "1" ["username"]=> string(9) "josh_user" }

Open in new window

0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 36589494
Looks like either the fiscStart or the monthID are the things we want.  Do they both mean "April?"  Which one is the canonical version of the month number?
0
 
LVL 2

Author Comment

by:axessJosh
ID: 36589510
fiscStart is the column name in the Group Table where I am setting the start month so I would like to use that one, although both are probably the same.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 36590217
OK, that should work fine.  Go back to the answer at ID:36587620 and in place of the $m = 7 statement use something like this:

$m = $row_rsFiscalStrt["fiscStart"];
0
 
LVL 2

Author Comment

by:axessJosh
ID: 36600093
Thanks Ray,

The starting month of each quarter is working perfectly.

The ending months are not displaying correctly though.

when i have "M" it shows Jan.  I replaced with 'c' and it gives the default timestamp 1970-01-01.  
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 36600183
That means that the input to the date() function is zero.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 36600299
Have a look at this script near the bottom.  You can see the output by running it on my server.  You can copy it and install it on your server if you want to experiment with it.  You might consider printing the "omega" month values for each fiscal quarter to see what that looks like.
http://www.laprbass.com/RAY_temp_josh.php

The idea is that if you want an internal representation of a DATETIME string, you must carry it in the ISO-8601 format.  Then you can use the PHP functions like strtotime() and date() in lots of meaningful ways.  But once you have converted the DATETIME string into anything other than ISO-8601 format, you cannot use it again except for external display.  I think the problem may be that you took a DATETIME value, turned it into the three-letter name of a month, then tried to use the three-letter name of the month as input to strtotime().  It doesn't work that way.

Go back and read this article carefully.  If you take the time to understand what it shows you will know everything you need to tackle almost any date-related coding task.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html
<?php // RAY_temp_josh.php
error_reporting(E_ALL);


// FISCAL QUARTERS BASED ON FISCAL YEARS


// REQUIRED FOR PHP 5.1+
date_default_timezone_set('America/Chicago');

// SOME TEST DATES FOR THE FISCAL YEARS
$fys = array
( 'CALENDAR' => 'January 1'
, 'ACADEMIC' => 'July 1'
, 'FEDERAL'  => 'October 1'
)
;

// COMPUTE THE QUARTERS
foreach ($fys as $key => $start)
{
    // THE END OF THE YEAR
    $nextyear           = date('c', strtotime($start . ' + 12 Months'));

    // THE FIRST DAY OF THE QUARTER
    $res["q1"]["alpha"] = date('c', strtotime($start));
    $res["q2"]["alpha"] = date('c', strtotime($start . ' + 3 Months'));
    $res["q3"]["alpha"] = date('c', strtotime($start . ' + 6 Months'));
    $res["q4"]["alpha"] = date('c', strtotime($start . ' + 9 Months'));

    // THE LAST DAY OF THE QUARTER
    $res["q1"]["omega"] = date('c', strtotime($res["q2"]["alpha"] . ' - 1 Day'));
    $res["q2"]["omega"] = date('c', strtotime($res["q3"]["alpha"] . ' - 1 Day'));
    $res["q3"]["omega"] = date('c', strtotime($res["q4"]["alpha"] . ' - 1 Day'));
    $res["q4"]["omega"] = date('c', strtotime($nextyear           . ' - 1 Day'));

    // SAVE THESE ELEMENTS
    $new[$key] = $res;
}

// SHOW THE WORK PRODUCT
echo "<pre>";
var_dump($new);
echo PHP_EOL;

// SHOW HOW TO GET JUST THE MONTHS
foreach ($new as $key => $res)
{
    echo PHP_EOL;
    echo "$key ";
    echo "Q1 " . $res["q1"]["alpha"];
    echo " ";
    echo date('M', strtotime($res["q1"]["alpha"]));
    echo PHP_EOL;

    echo PHP_EOL;
    echo "$key ";
    echo "Q2 " . $res["q2"]["alpha"];
    echo " ";
    echo date('M', strtotime($res["q2"]["alpha"]));
    echo PHP_EOL;

    echo PHP_EOL;
    echo "$key ";
    echo "Q3 " . $res["q3"]["alpha"];
    echo " ";
    echo date('M', strtotime($res["q3"]["alpha"]));
    echo PHP_EOL;

    echo PHP_EOL;
    echo "$key ";
    echo "Q4 " . $res["q4"]["alpha"];
    echo " ";
    echo date('M', strtotime($res["q4"]["alpha"]));
    echo PHP_EOL;
}

Open in new window

0
 
LVL 2

Author Comment

by:axessJosh
ID: 36601000
Thanks Ray.

I actually discussed it with the client again and they mentioned they would rather see each month in the quarter displayed.  I adjusted the first part of the code to make it work.  

I appreciate you continuing to help me.  Great learning experience.

 
foreach ($fys as $key => $start)
{
    // THE END OF THE YEAR
    $nextyear           = date('M', strtotime($d . ' + 12 Months'));

    // THE FIRST DAY OF THE QUARTER
    $res["q1"]["alpha"] = date('M', strtotime($d)) . ", " . date('M', strtotime($d . ' + 1 Months')) . ", " . date('M', strtotime($d . ' + 2 Months'));
    $res["q2"]["alpha"] = date('M', strtotime($d . ' + 3 Months')) . ", " . date('M', strtotime($d . ' + 4 Months')) . ", " . date('M', strtotime($d . ' + 5 Months'));
    $res["q3"]["alpha"] = date('M', strtotime($d . ' + 6 Months')) . ", " . date('M', strtotime($d . ' + 7 Months')) . ", " . date('M', strtotime($d . ' + 8 Months'));
    $res["q4"]["alpha"] = date('M', strtotime($d . ' + 9 Months')) . ", " . date('M', strtotime($d . ' + 10 Months')) . ", " . date('M', strtotime($d . ' + 11 Months'));

    

    // SAVE THESE ELEMENTS
    $new[$key] = $res;
}

Open in new window

0
 
LVL 2

Author Closing Comment

by:axessJosh
ID: 36601010
great help!
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Introduction HTML checkboxes provide the perfect way for a web developer to receive client input when the client's options might be none, one or many.  But the PHP code for processing the checkboxes can be confusing at first.  What if a checkbox is…
Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now