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


  private   $dayComparisonQuery = "
              CASE
                WHEN RecurringInvoice.PeriodType = 'year'
                THEN 
                  DATE_FORMAT(IF(RecurringInvoice.LastExecutionDate IS NOT NULL,
                              RecurringInvoice.LastExecutionDate,
                              RecurringInvoice.StartingDate) + INTERVAL RecurringInvoice.Period YEAR,'%dateFormat%') %cp% ?
                WHEN RecurringInvoice.PeriodType = 'month'
                THEN 
                  DATE_FORMAT(IF(RecurringInvoice.LastExecutionDate IS NOT NULL,
                              RecurringInvoice.LastExecutionDate,
                              RecurringInvoice.StartingDate) + INTERVAL RecurringInvoice.Period MONTH,'%dateFormat%') %cp% ?
                WHEN RecurringInvoice.PeriodType = 'week'
                THEN 
                  DATE_FORMAT(IF(RecurringInvoice.LastExecutionDate IS NOT NULL,
                              RecurringInvoice.LastExecutionDate,
                              RecurringInvoice.StartingDate) + INTERVAL RecurringInvoice.Period WEEK,'%dateFormat%') %cp% ?
                WHEN RecurringInvoice.PeriodType = 'day'
                THEN 
                  DATE_FORMAT(IF(RecurringInvoice.LastExecutionDate IS NOT NULL,
                              RecurringInvoice.LastExecutionDate,
                              RecurringInvoice.StartingDate) + INTERVAL RecurringInvoice.Period DAY,'%dateFormat%') %cp% ?
              END";


  private $replacements = array('day'=>array('%dateFormat%'=>'%%Y%%m%%d','%cp%'=>'='),
                                'month'=>array('%dateFormat%'=>'%%Y%%m','%cp%'=>'='),
                                'year'=>array('%dateFormat%'=>'%%Y','%cp%'=>'='));


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

  public function toGenerateThisYear($date = null)
  {
    $date = $date ? $date : sfDate::getInstance();
    $year = $date->format('Y');
    $queryString = str_replace(array_keys($this->replacements['year']),array_values($this->replacements['year']),
                               $this->dayComparisonQuery);

    return $this->enabled()
      ->whereCustom('date_format(RecurringInvoice.StartingDate,\'%%Y\') = ?',$year,'is_this_year')
      ->whereCustom($queryString,array($year,$year,$year,$year),'y_interval')
      ->combine(array('y_interval','is_this_year'),'or');

  }

  public function toGenerateThisMonth($date = null)
  {
    $date = $date ? $date : sfDate::getInstance();
    $yearmonth = $date->format('Ym');

    $queryString = str_replace(array_keys($this->replacements['month']),array_values($this->replacements['month']),
                               $this->dayComparisonQuery);

    return $this->enabled()
      ->whereCustom('date_format(RecurringInvoice.StartingDate, \'%%Y%%m\') = ?', $yearmonth,'is_this_month')
      ->whereCustom($queryString,array($yearmonth,$yearmonth,$yearmonth,$yearmonth),'m_interval')
      ->combine(array('m_interval','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();
    $yearmonthday = $date->format('Ymd');
    $this->replacements['day']['%cp%'] = '=';
    $queryString = str_replace(array_keys($this->replacements['day']),array_values($this->replacements['day']),
                               $this->dayComparisonQuery);

    return  $this->enabled()
      ->whereCustom('date_format(RecurringInvoice.StartingDate,\'%%Y%%m%%d\') = ?',
                    $yearmonthday,'is_this_dom')
      ->whereCustom($queryString,array($yearmonthday,$yearmonthday,$yearmonthday,$yearmonthday),'dom_interval')
      ->combine(array('dom_interval','is_this_dom'),'or');
  }

  public function outOfSync()
  {
    $yearmonthday = date('Ymd');
    $this->replacements['day']['%cp%'] = '<';
    $queryString = str_replace(array_keys($this->replacements['day']),array_values($this->replacements['day']),
                               $this->dayComparisonQuery);
    return $this->drafted(false)->where('Enabled','=',true)
      ->where('Desync','=',true,'desync')
      ->whereCustom($queryString,array($yearmonthday,$yearmonthday,$yearmonthday,$yearmonthday),'out of interval')
      ->combine(array('desync','out of interval'),'or');
  }

  /**
   * @param array $search
   *        Common parameters to all Invoice objects:
   *        'query' -> text fields query
   *        'tags'  -> tag search
   *        ----------------------------------------------------------------
   *        '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 'out_of_sync':
          $this->outOfSync();
          break;
        default:
          // all
          break;
      }
    }
    
    return $this;
  }
  
  public function selectStatus()
  {
    return $this->withColumn("if(draft, 'DRAFT', if(enabled, 'ENABLED', 'DISABLED'))", 'Status');
  }
  
}