Populate Drop down with Yearly Quarters? [SOLVED]

[eluser]ywftdg[/eluser]
Hey guys, I am curious if anyone has built a sort of tricky drop down in CI? I have old code (very ugly stuff) that builds out the current 3 previous payment quarters for my users and the current. Basically the system checks the current month, then does some fancy footsteps to say ok, this quarter is the 3rd quarter ( July-Sept 2008 ). My guys get paid every 4 quarters -> Jan-Mar, Apr-June, July-Sept, and Oct-Dec.

Does anyone know any smart -simple ways about going to populate a form drop down that would generate this, and the year? I have attached an image of the current systems drop down to help make sense of this.

I am really at a loss for thoughts on this one, as it needs to get the current date, compare that to see what quarter we are in, then generate that as the current quarter, and then generate values for the three previous quarters. I will then use these dates for generating sales reports for my guys. I am rebuilding the entire system in CI, and this is the last main hurdle in my way.

Any help would be great if anyone has been down this road before.

[eluser]xwero[/eluser]
In mysql you have the quarter function to check which quarter it is.

SELECT * FROM table WHERE QUARTER(date) = QUARTER(CURDATE())

I hope this helps.

[eluser]ywftdg[/eluser]
Wow, didn't know that one. Thanks! I see how this can help when getting the stats back, but still a little stumped on the filling out the Drop down.

[eluser]xwero[/eluser]
Why not use the values; 1-2008, 2-2008, 3-2008, 4-2008 for the dropdown options. You can generate the year in the dropdown if needed. Then you can do

Code:

if( ! isset($_POST['quarter']) )
{
    $this->db->where('QUARTER(date)','QUARTER(CURDATE())');
    $this->db->where('YEAR(date)','YEAR(CURDATE())');
}
else
{
    list($quarter,$year) = explode('-',$_POST['quarter']);
    $this->db->where('QUARTER(date)',$quarter);
    $this->db->where('YEAR(date)',$year);
}

$query = $this->db->get('table');

Why do things in php if they can be done in mysql.

[eluser]ywftdg[/eluser]
Ok, will try. One thing I am having an issue with is trying to do some grouping and sums of my results. Right now I am not focusing on the quarter results until last stage when i start applying by dates for the results. But my concern is trying to do like count THISROW as MYNAME, but in CI I don't totally get it, since I am not sure where you can say name the count this, then pass it back in the result and still have it sow p in the view. Right now my model has:

Code:

function get_products($id)    {
        $this->db->select('pSku,pName,rTimeStamp');
        $this->db->where('products.pDesignerId', $id);
        
        $this->db->from('products');
        
        $this->db->join('orderdetail', 'orderdetail.oSku = products.pSku');
        $this->db->join('receipt', 'receipt.rOrderNum = orderdetail.oOrderNum');
        
        
        $query = $this->db->get();
        return $query->result();
       }

Now this works and returns all the orders, from the receipts for that designer. But I want to say, ok group by the product pSku, count that which would account for items sold.I think if I can figure this much out, I could proceed with counting of the products price, etc etc. Any ideas?

[eluser]ywftdg[/eluser]
Ok all is working great now, but one little issue I am not sure about how to calculate with the DATE feature in mysql. Where I run into a problem is doing this:

Code:

if ($datepost == '2') {        // Last Quarter
            $this->db->where('QUARTER(rTimeStamp) = QUARTER(CURDATE())-1');
            $this->db->where('YEAR(rTimeStamp) = YEAR(CURDATE())');
        }

The problem with this, is when I was doing the "This Quarter" link, well then it would just lie above, without the '-1' on the quarter. Now ok, the code above would be for the "Previous Quarter'. Well the problem with this, is the yar part. Say you were in JAN-MAR quarter, clicked this link, it just wouldn't work, because it would try to go back a quarter, but at same time try to be in the same year. Any ideas how to say go back a quarter, but also take into mind the change in years?

[eluser]ywftdg[/eluser]
Two days later, I still cannot get this to work, any ideas? This is just trying to get 'Last Month' to work. I have to take into mind if the month goes back, say they are in January, it would also read the year rolling back as well. This just comes back with no values. Driving me nuts...

Code:

if ($datepost == '6') {     // Last Month BUG
            $lastmonth = date("Ym", mktime(0,0,0,date("m")-1,date("d"),date("Y")))."01000000";
            $this->db->where('MONTH(rTimeStamp)' , 'MONTH('.$lastmonth.')');
            $this->db->where('YEAR(rTimeStamp)' , 'YEAR('.$lastmonth.')');
        }

[eluser]ywftdg[/eluser]
EDIT SOLVED
Solved changing the $querydate to getting date using just php. God, what a nightmare this model has been, as you can see I have basically begun talking to myself on this thread.

Code:

$this->querydate = date("m");

/////////////////////
Going to try and ask one more time in this thread, maybe someone out there can lend some guidance. I have found a method to work around the previous year problem, but for the love og god, I cannot figuire out CI and how it works with variables in models and queries. This code returns nothing :

Start of Model

Code:

var $rollbackyear = '';
var $querydate = '';

My model function has:

Code:

$this->querydate = $this->db->select( 'MONTH(CURRENT_DATE())' );

if ($datepost == '6') {     // Last Month BUG
            if ( $this->querydate == '1' ) {    
                $this->rollbackyear = '-1';
            }
            
            $this->db->where('MONTH(rTimeStamp) = MONTH(CURRENT_DATE())-1');
            $this->db->where('YEAR(rTimeStamp) = YEAR(CURDATE())' . $this->rollbackyear . '');
            
        }

Any ideas, will be my last attempt I assume, i'm 2-3 days stuck on this, about to give up and start using some crazy long PHP code to get the proper date.