future events list

events.py

import pymysql
import requests
from datetime import datetime, timedelta

# Function to send a POST request for scheduling a notification
def send_notification(event_date, event_id, notification_type):
    # Adjust the URL and payload as per your Firebase or push notification service
    print(f"Event scheduled for:  {event_date}")
    url = 'https://your-firebase-url.com/schedule-notification'
    payload = {
        'event_id': event_id,
        'notification_type': notification_type,
        'schedule_date': event_date.strftime('%Y-%m-%d')
    }
    print(payload)
    # response = requests.post(url, json=payload)
    # if response.status_code == 200:
    #     print(f'Notification scheduled for {notification_type} - Event ID: {event_id}')
    #     return True
    # else:
    #     print(f'Failed to schedule notification for {notification_type} - Event ID: {event_id}')
    #     return False

# Function to insert post_id into wp_em_notification_scheduled table
def insert_into_notification_scheduled(post_id):
    print(post_id)
    # try:
    #     with connection.cursor() as cursor:
    #         # Define the SQL query to insert post_id
    #         insert_query = "INSERT INTO wp_em_notification_scheduled (post_id) VALUES (%s)"
    #         cursor.execute(insert_query, (post_id,))
    #         connection.commit()
    # except Exception as e:
    #     print(f'Failed to insert post_id into wp_em_notification_scheduled: {e}')

# Establish a database connection
# Define the SQL query to fetch events
# Establish a database connection
connection = pymysql.connect(
    host='your_db_host',
    user='your_db_user',
    password='your_db_password',
    db='your_db_name',
    charset='utf8mb4',
    cursorclass=pymysql.cursors.DictCursor
)
try:
    with connection.cursor() as cursor:
        query = """
            SELECT e.post_id, e.event_start_date, e.event_name
            FROM wp_em_events e
            INNER JOIN wp_term_relationships tr ON e.post_id = tr.object_id
            INNER JOIN wp_terms t ON tr.term_taxonomy_id = t.term_id
            WHERE t.name = 'Training'
                AND e.event_start_date > NOW()
                AND e.post_id NOT IN (SELECT post_id FROM wp_em_notification_scheduled);
        """

        # Fetch all the results
        results = [
            {'post_id': 1, 'event_start_date':datetime(2023, 10, 22)},
            {'post_id': 2, 'event_start_date':datetime(2023, 10, 23)},
            {'post_id': 3, 'event_start_date':datetime(2023, 10, 24)}
        ]

        # Iterate through the results
        for result in results:
            event_date = result['event_start_date']
            event_id = result['post_id']
            previous_sunday = event_date -timedelta(days=event_date.weekday() + 1)
            previous_day = event_date - timedelta(days=1)


            is_sunday = event_date.weekday() == 6
            send_notification(event_date, event_id, 'You have an event today')

            if send_notification(previous_day, event_id, 'You have an event tomorrow'):
                insert_into_notification_scheduled(event_id) # mark the event as scheduled

            same_date = previous_day.date() == previous_sunday.date()

            if not same_date:
                if is_sunday:
                    send_notification(previous_sunday, event_id, 'You have an event next week')
                else:
                    send_notification(previous_sunday, event_id, 'You have an event this week')
finally:
    connection.close()