accesshist.py

accesshist.py

from openpyxl import load_workbook
import cx_Oracle

ROWID_NO = 0
ROWID_LAST_ACCESS = 1
DB_USER = 'user'
DB_PASSWD = 'password'
DB_URL = '1.1.1.1:1111'
DB_SID = 'SID'


def get_last_login(no):
    try:
        cur = con.cursor()
        cur.execute("""
select MAX(insert_date) from LOGIN_HISTORIES
 where member_no = :member_no """, {'member_no': no})

        row = cur.fetchone()
        if row and row[0]:
            return row[0]
        else:
            return datetime.min
    finally:
        if cur:
            cur.close()


def get_last_order(no):
    try:
        cur = con.cursor()
        cur.execute("""
select MAX(UPDATE_DATE) from ORDERS
 where member_no = :member_no """, {'member_no': no})

        row = cur.fetchone()
        if row and row[0]:
            return row[0]
        else:
            return datetime.min;
    finally:
        if cur:
            cur.close()


wb = load_workbook('last_access.xlsx')
sheet1 = wb[wb.get_sheet_names()[0]]

try:
    con = cx_Oracle.connect(DB_USER, DB_PASSWD, '{0}/{1}'.format(DB_URL, DB_SID))
    for row in sheet1.rows:
        no = row[ROWID_NO].value
        last_login = get_last_login(no)
        last_order = get_last_order(no)

        if last_login > last_order:
            last_access = last_login
        else:
            last_access = last_order

        row[ROWID_LAST_ACCESS].value = last_access.strftime("%Y-%m-%d %H:%M:%S") if last_access > datetime.min else ''
        print("{0} : {1}".format(no, last_access))
finally:
    if con:
        con.close()

wb.save('last_access_modified.xlsx');