title: Sqlite calendar recurrent event logic
body: <p>I'm using a custom calendar (Hijri Calendar) to display events in my app. I have a simple event database where the user inputs the details and the custom calendar shows the output.</p>
  
  <p>This is the Event form:</p>
  
  <pre><code>Name
  Description
  Repetition (with options: Monthly, Yearly and Never)
  Date (custom date)
  </code></pre>
  
  <p>The form is submitted and the data is inserted into the database. This is the database schema:</p>
  
  <pre><code>id|e_name|e_desc|repeat|day|month|year
  </code></pre>
  
  <p>Since it is the Hijri date which is being entered into the database, I have split the date into day|month|year for convenience.</p>
  
  <p>The problem comes when I want to display all events on a specific date. If there are simple one day events, it all works fine. But if there are multiple events on one date with different repeat values, it never displays the correct data.</p>
  
  <p>For example:
  Two events on one date (I'll use the gregorian calendar for simplicity here):</p>
  
  <pre><code>29/5/2013 -1- Monthly event
  29/5/2013 -2- Yearly event
  </code></pre>
  
  <p>This should display both the events on 29/5/2013. Event 1, however should reoccur monthly. This is the query:</p>
  
  <pre><code>select * from user_events WHERE day = '29' AND month = '5'
  </code></pre>
  
  <p>This will show all the events for the date regardless of the repeat condition.
  However, if I want to add a <strong>monthly</strong> event on <code>29/4/2013</code>. If I query this:</p>
  
  <p><code>select * from user_events WHERE day = '29'</code></p>
  
  <p>. .it will show the events monthly but it will display the Yearly event as Monthly too.</p>
  
  <p>and if I query with the month condition: <code>AND month = '5'</code>, this will only show Yearly and Monthly events for May, but it won't display the monthly event of 29/4.</p>
  
  <p><strong>In short, the month of May should display 2 monthly events (29/4 event and 29/5) and one yearly event (29/5).</strong></p>
  
  <p>How do I go about solving this? </p>