Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- # -*- coding: utf-8 -*-
- # Python 3
- import os
- import sys
- import sqlite3
- import bbot.discord
- SCRIPT_PATH : str = os.path.dirname(os.path.realpath(sys.argv[0]))
- local_db_path :str = "/home/pi/Scripts/blastardbot/bot.db" ## Production
- # local_db_path :str = "/home/pi/Scripts/blastardbot/bot-dev.db" ## Development
- # Define queries
- MEMBER_SELECT = '''
- SELECT
- rosterID
- clan_id,
- name,
- psn_id,
- status,
- join_date,
- exit_date,
- return_date,
- rec_id
- FROM
- member
- WHERE
- status IN ('Member','Alumni')
- ORDER BY
- name;
- '''
- MEMBER_CAREER_SELECT = '''
- SELECT
- name,
- psn_id,
- start_date,
- event,
- end_date,
- status,
- rosterID
- FROM
- member_career
- WHERE
- rosterID = :rosterID;
- '''
- CAREER_INSERT = '''
- INSERT INTO
- career (
- rosterID,
- start_date,
- end_date,
- event,
- status
- )
- VALUES (
- :rosterID,
- :start_date,
- :end_date,
- :event,
- :status
- )
- '''
- CAREER_UPDATE_EXIT = '''
- UPDATE
- career
- SET
- end_date = :end_date,
- status = :status
- WHERE
- rosterID = :rosterID
- AND
- start_date = :start_date
- '''
- CAREER_UPDATE_START = '''
- UPDATE
- career
- SET
- start_date = :new_start_date
- WHERE
- rosterID = :rosterID
- AND
- start_date = :old_start_date
- '''
- CAREER_DELETE_RETURN = '''
- DELETE FROM
- career
- WHERE
- rosterID = :rosterID
- AND
- start_date = :return_date
- AND
- event = "Return"
- '''
- CAREER_UPDATE_RETURN = '''
- UPDATE
- career
- SET
- start_date = :return_date,
- status = "Active",
- event = "Return"
- WHERE
- rosterID = :rosterID
- AND
- start_date = :event_date
- '''
- CAREER_CREATE_RETURN = '''
- INSERT INTO
- career (start_date, event, rosterID, status)
- VALUES (
- :return_date,
- 'Return',
- :rosterID,
- 'Active'
- )
- '''
- # define classes
- class CareerSummary():
- rosterID = None
- name = None
- missing = True
- prev_start = None
- join_date = None
- exit_date = None
- return_date = None
- status = None
- last_event = None
- event_count = 0
- last_event_date = None
- career_data = None
- db = None
- def __init__(self, db, rosterID, name):
- self.rosterID = rosterID
- self.name = name
- self.db = db
- self.career_load()
- def career_load(self):
- self.missing = True
- self.prev_start = None
- self.join_date = None
- self.exit_date = None
- self.return_date = None
- self.status = None
- self.last_event = None
- self.event_count = 0
- self.last_event_date = None
- self.career_data = db_select(self.db, MEMBER_CAREER_SELECT, {'rosterID' : self.rosterID})
- if len(self.career_data) > 0:
- self.missing = False
- self.event_count = len(self.career_data)
- self.last_event = self.career_data[self.event_count-1]['event']
- self.last_event_date = self.career_data[self.event_count-1]['start_date']
- self.join_date = self.career_data[0]['start_date']
- self.exit_date = self.career_data[0]['end_date']
- self.status = self.career_data[self.event_count-1]['status']
- if self.event_count > 1:
- self.prev_start = self.career_data[self.event_count-2]['start_date']
- else:
- self.prev_start = self.career_data[0]['start_date']
- else:
- print("No career events found.")
- for career_event in self.career_data:
- if not self.exit_date:
- self.exit_date = career_event['end_date']
- else:
- if career_event['end_date'] and self.exit_date < career_event['end_date']:
- self.exit_date = career_event['end_date']
- if career_event['start_date']>self.join_date and career_event['start_date']>noneToEmpty(self.return_date):
- self.return_date = career_event['start_date']
- print(self)
- def career_create(self, join_date):
- options = {
- 'rosterID' : self.rosterID,
- 'start_date' : join_date,
- 'end_date' : None,
- 'event' : 'Join',
- 'status' : 'Active'
- }
- db_execute(self.db, CAREER_INSERT, options)
- print(f"Join event on {join_date} created for {self.name} ({self.rosterID}).")
- self.career_load()
- def join_update(self, join_date):
- options = {
- 'rosterID' : self.rosterID,
- 'old_start_date' : self.join_date,
- 'new_start_date' : join_date
- }
- db_execute(self.db, CAREER_UPDATE_START, options)
- print(f"Join event update from {self.join_date} to {join_date} for {self.name} ({self.rosterID})")
- self.career_load()
- def exit_update(self, exit_date, return_date):
- print(f"Current exit date is: {self.exit_date}")
- print(f" New exit will be: {exit_date}, return date: {return_date}")
- event_date = self.last_event_date
- event_status = "Alumni"
- # It's NOT possible to have a return date and NO exit date
- if return_date and not exit_date:
- print("Error! Return without exit!")
- return
- # Possible that exit date was reverted to None
- if not exit_date:
- event_status = "Active"
- print("Updating exit with ACTIVE status.")
- if return_date and return_date > exit_date:
- # If the return date is AFTER the exit date, the end date should be on last career event
- event_date = self.prev_start
- options = {
- 'rosterID' : self.rosterID,
- 'start_date' : event_date,
- 'end_date' : exit_date,
- 'status' : event_status
- }
- db_execute(self.db, CAREER_UPDATE_EXIT, options)
- print(f"Updating exit date to {exit_date} and status {event_status} for event starting on {event_date} for {self.name} ({self.rosterID}).")
- self.career_load()
- def return_update(self, return_date, exit_date):
- if not return_date:
- # This will be a deletion of the last event in the career
- options = {
- 'rosterID' : self.rosterID,
- 'return_date' : self.return_date
- }
- print(f"Removing return event from {self.name} ({self.rosterID})")
- db_execute(self.db, CAREER_DELETE_RETURN, options)
- else:
- # We need to figure out if we are UPDATING a return date
- # or creating a new returning event
- # If the return_date is BEFORE the exit_date, it's an update
- if (return_date < exit_date):
- # We are updating
- options = {
- 'rosterID' : self.rosterID,
- 'return_date' : self.return_date,
- 'event_date' : self.last_event_date
- }
- db_execute(self.db, CAREER_UPDATE_RETURN, options)
- print(f"Updating return date {self.return_date} for {self.name} ({self.rosterID}).")
- else:
- # If the return date is AFTER the exit date, it's a new return event
- # Just check status to be safe. Only an alumni can return
- if self.status == "Alumni":
- # We are creating a new event
- options = {
- 'rosterID' : self.rosterID,
- 'return_date' : return_date
- }
- db_execute(self.db, CAREER_CREATE_RETURN, options)
- print(f"Creating return event on {return_date} for {self.name} ({self.rosterID}).")
- else:
- print(f"Can't add an exit event as {self.name} (self.rosterID) isn't alumni.")
- self.career_load()
- def event_sync(self, join_date, exit_date, return_date):
- if self.missing:
- # Create career if missing
- print("* No career data found. Creating...")
- self.career_create(join_date)
- else:
- # Otherwise update join date if there's a difference
- if join_date != self.join_date:
- print("* Join date mismatch. Updating join date...")
- self.join_update(join_date)
- if exit_date != self.exit_date:
- print("* Exit data mismatch. Updating exit date...")
- self.exit_update(exit_date, return_date)
- if return_date != self.return_date:
- print("* Return date mismatch. Updating return date...")
- self.return_update(return_date, exit_date)
- def __str__(self):
- return f"Career for {self.name} ({self.rosterID}) status {self.status} - join: {self.join_date}, exit: {self.exit_date}, return: {self.return_date}, events: {self.event_count}"
- # Define functions
- def noneOrNull(v):
- if v is None:
- return True
- if len(v) is 0:
- return True
- return False
- def noneToEmpty(v):
- if v is None:
- return ""
- else:
- return v
- def emptyToNone(v):
- if v == "":
- return None
- return v
- def db_open():
- print("Opening database: ",local_db_path)
- db = sqlite3.connect(local_db_path)
- db.row_factory = sqlite3.Row
- return db
- def db_close(db):
- print("Closing data base.")
- db.commit()
- db.close()
- def db_select(db, query, options = None):
- cur = db.cursor()
- if options is not None:
- rows = cur.execute(query, options).fetchall()
- else:
- rows = cur.execute(query).fetchall()
- data=[{k: item[k] for k in item.keys()} for item in rows]
- return data
- def db_execute(db, query, options):
- cur = db.cursor()
- cur.execute(query, options)
- db.commit()
- def members_fetch(db):
- print("Loading all current and previous members.")
- members=db_select(db, MEMBER_SELECT)
- return members
- def member_check(db, members):
- member_count = 0
- for m in members:
- member_count = member_count + 1
- # Career rows are
- # rosterID
- # start_date
- # event (Join, Return)
- # end_date
- # status (Active, Alumni)
- # Rules for career updates
- # Info in members must have:
- # join_date must be the earliest date
- # When return_date is defined:
- # If return date is > exit_date, member is active
- # If return date is < exit_date, member is alumni
- # exit_date must come after join_date (if it exists)
- # If the member row doesn't follow this,
- # it won't be processed as it is considered invalid.
- member_name = m['name']
- rosterID = m['clan_id']
- join_date = emptyToNone(m['join_date'])
- exit_date = emptyToNone(m['exit_date'])
- return_date = emptyToNone(m['return_date'])
- print(f"===== Roster data for #{member_count}, {member_name} ({rosterID}) join: {join_date} exit: {exit_date} return: {return_date} =====")
- summary = CareerSummary(db, rosterID, member_name)
- summary.event_sync(join_date, exit_date, return_date)
- #
- # MAIN PROCEDURE
- #
- db = db_open()
- members = members_fetch(db)
- member_check(db, members)
- print("\n----\n")
- db_close(db)
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement