<?php
class RecurringInvoiceFinder extends InvoiceableFinder
{
  protected $class = 'RecurringInvoice';


  /** 
   * Limits the result to invoices enabled or disabled
   * @param boolean
   * @return RecurringInvoiceFinder  
   * @author JoeZ <jzarate@gmail.com>
   */
  public function enabled($is = true)
  {
    return $this->where('Enabled','=',$is);
  }
  public function drafted($is = true)
  {
    return $this->where('Draft', '=', $is);
  }

  /**
   * Limits the result to recurring invoices "exhausted" or not. that is,
   * recurring templates that have generated more invoices than it's max_occurrences value or not
   * @param boolean indicating if we're looking for exhausted or not exhausted
   * @return RecurringInvoiceFinder
   * @author JoeZ <jzarate@gmail.com>
   */
  public function exhausted($is = true)
  {
    return $this
      ->_if($is)
      ->where('MaxOccurrences','>',0)
      ->whereCustom('RecurringInvoice.MaxOccurrences - RecurringInvoice.Occurrences <= ?',0)
      ->_else()
      ->where('MaxOccurrences','=',0,'max=0')
      ->whereCustom('RecurringInvoice.MaxOccurrences - RecurringInvoice.Occurrences IS NULL','','max_occ null')
      ->whereCustom('RecurringInvoice.MaxOccurrences - RecurringInvoice.Occurrences > ?',0,'max_occ')
      ->combine(array('max=0','max_occ null','max_occ'),'or','final_or')
      ->combine(array('final_or'),'and')
      ->_endif();
  }

  public function toGenerateThisYear($date = null)
  {
    $date = $date ? $date : sfDate::getInstance();
    $year = $date->format('Y');

    $yearComparisonQuery = "
              DATE_FORMAT(RecurringInvoice.LastExecutionDate +
              INTERVAL IF(RecurringInvoice.YearPeriod,RecurringInvoice.YearPeriod,'0') YEAR +
              INTERVAL IF(RecurringInvoice.MonthPeriod,RecurringInvoice.MonthPeriod,'0') MONTH +
              INTERVAL IF(RecurringInvoice.DayPeriod IS NOT NULL,RecurringInvoice.DayPeriod,'0') DAY ,
              '%%Y') = ? ";

    return $this->drafted(false)->enabled()->exhausted(false)
      ->whereCustom('date_format(RecurringInvoice.StartingDate,\'%%Y\') = ?',$year,'is_this_year')
      ->whereCustom('RecurringInvoice.LastExecutionDate IS NOT NULL','','last_exec_not_null')
      ->whereCustom($yearComparisonQuery,$year,'y_interval')
      ->whereCustom("RecurringInvoice.YearPeriod IS NOT NULL",'','y_interval_not_null')
      ->combine(array('last_exec_not_null','y_interval','y_interval_not_null'),'and','interval_conditions')
      ->combine(array('interval_conditions','is_this_year'),'or');
  }

  public function toGenerateThisMonth($date = null)
  {
    $date = $date ? $date : sfDate::getInstance();
    $yearmonth = $date->format('Yn');
    $monthComparisonQuery = "
              DATE_FORMAT(RecurringInvoice.LastExecutionDate +
              INTERVAL IF(RecurringInvoice.YearPeriod,RecurringInvoice.YearPeriod,'0') YEAR +
              INTERVAL IF(RecurringInvoice.MonthPeriod,RecurringInvoice.MonthPeriod,'0') MONTH +
              INTERVAL IF(RecurringInvoice.DayPeriod IS NOT NULL,RecurringInvoice.DayPeriod,'0') DAY ,
              '%%Y%%c') = ? ";
    return $this->drafted(false)->enabled()->exhausted(false)
      ->whereCustom('date_format(RecurringInvoice.StartingDate, \'%%Y%%c\') = ?', $yearmonth,'is_this_month')
      ->whereCustom("RecurringInvoice.LastExecutionDate IS NOT NULL",'','last_exec_not_null')
      ->whereCustom($monthComparisonQuery,$yearmonth,'m_interval')
      ->combine(array('last_exec_not_null','m_interval'),'and','interval_conditions')
      ->combine(array('interval_conditions','is_this_month'),'or');
  }


  /**
   * 'duedToGenerateOn' the recurring invoices that are
   * set to generate an invoice on certain date.
   * @param sfDate $date
   * @return RecurringInvoiceFinder
   * @author JoeZ <jzarate@gmail.com>
   */

  public function toGenerateThisDay($date = null)
  {
    $date = $date ? $date : sfDate::getInstance();
    $day_of_week = $date->format('N');
    $day_of_month = $date->format('j');
    $yearmonth = $date->format('Yn');
    $yearmonthday = $date->format('Ynj');
    $monthComparisonQuery = "
              DATE_FORMAT(RecurringInvoice.LastExecutionDate +
              INTERVAL IF(RecurringInvoice.YearPeriod,RecurringInvoice.YearPeriod,'0') YEAR +
              INTERVAL IF(RecurringInvoice.MonthPeriod,RecurringInvoice.MonthPeriod,'0') MONTH +
              INTERVAL IF(RecurringInvoice.DayPeriod IS NOT NULL,RecurringInvoice.DayPeriod,'0') DAY ,
              '%%Y%%c') = ? ";
    $dayComparisonQuery = "
              DATE_FORMAT(RecurringInvoice.LastExecutionDate +
              INTERVAL IF(RecurringInvoice.YearPeriod,RecurringInvoice.YearPeriod,'0') YEAR +
              INTERVAL IF(RecurringInvoice.MonthPeriod,RecurringInvoice.MonthPeriod,'0') MONTH +
              INTERVAL IF(RecurringInvoice.DayPeriod IS NOT NULL,RecurringInvoice.DayPeriod,'0') DAY ,
              '%%Y%%c%%e') = ?";

    return  $this->drafted(false)->enabled()->exhausted(false)
      ->whereCustom('date_format(RecurringInvoice.StartingDate,\'%%Y%%c%%e\') = ?',
                    $yearmonthday,'is_this_dom')
      ->whereCustom("RecurringInvoice.LastExecutionDate IS NOT NULL",'','last_exec_not_null')
      ->whereCustom($dayComparisonQuery,$yearmonthday,'dom_interval')
      ->whereCustom($monthComparisonQuery,$yearmonth,'month_interval')
      ->where('DayOfWeek','=',$day_of_week,'is_this_dow')
      ->combine(array('month_interval','is_this_dow'),'and','dow_interval')
      ->combine(array('dom_interval','dow_interval'),'or','interval_applicable')
      ->combine(array('last_exec_not_null','interval_applicable'),'and','interval')
      ->combine(array('interval','is_this_dom'),'or');
  }

  /**
   * @param array $search
   *        Common parameters to all Invoice objects:
   *        'query' -> text fields query
   *        'tags'  -> tag search
   *        'sort'  -> array with field and method (asc, desc)      common ^
   *        ----------------------------------------------------------------
   *        'name'  -> date range parameters                specific .
   * @return DbFinder's same instance
   * @author Carlos Escribano <carlos@markhaus.com>
   */
  public function search($search)
  {
    $this->commonSearch($search);
    
    if(isset($search['query']) && $search['query'])
    { 

      $query = $search['query'];
      $this->where('RecurringInvoice.Name','like',"%$query%",'name')
        ->where('RecurringInvoiceItem.Description', 'like', "%$query%", 'description')
        ->combine(array('invoice_conditions', 'description','name'), 'or')
        ->distinct();


    }
    
    if (isset($search['status']))
    {
      switch(strtolower($search['status']))
      {
        case 'enabled':
          $this->enabled(true);
          break;
        case 'disabled':
          $this->enabled(false);
          break;
        case 'draft':
          $this->drafted(true);
          break;
        case 'this_year':
          $this->toGenerateThisYear();
          break;
        case 'this_month':
          $this->toGenerateThisMonth();
          break;
        case 'today':
          $this->toGenerateThisDay();
          break;
        case 'exhausted':
          $this->exhausted();
          break;
        case 'active':
          $this->enabled()->drafted(false)->exhausted(false);
          break;
        default:
          // all
          break;
      }
    }
    
    return $this;
  }
  
  public function selectStatus($search)
  {
    return $this
      ->withColumn("if(draft, 'DRAFT', if(enabled, 'ENABLED', 'DISABLED'))", 'Status');
  }
  
}