#!/usr/bin/env python
# -*- coding:gbk -*-
"""
Abstract:
    batch accounts  import  into discuz with python
History:
    2012-11-28  [email protected]   first release
"""
 
import MySQLdb
 
db = MySQLdb.connect('192.168.1.123', 'root', 'toor', 'discuz',
 init_command = 'set names gbk')
cursor = db.cursor()
seed = 'example'
file = 'bbs_import.log'
error= '0_error.log'
duplicated = '1_duplicated.log'
dismatch = '2_dismatch.log'
existed = '3_existed.log'
fname = '2012AccountImport.xls'
 
 
def reset():
    with open(error, 'w') as f:
        f.truncate()
    with open(duplicated, 'w') as f:
        f.truncate()
    with open(dismatch, 'w') as f:
        f.truncate()
    with open(existed, 'w') as f:
        f.truncate()
 
 
def log(level, message):
    import time
    if level == 0:
        with open(error, 'a') as f:
            f.writelines(message + '\n')
    elif level == 1:
        with open(duplicated, 'a') as f:
            f.writelines(message + '\n')
    elif level == 2:
        with open(dismatch, 'a') as f:
            f.writelines(message + '\n')
    elif level == 3:
        with open(existed, 'a') as f:
            f.writelines(message + '\n')
    with open(file, 'a') as f:
        message = time.strftime('%Y-%m-%d %H:%M:%S ') + '=> ' + message
        f.writelines(message + '\n')
 
 
def readData():
    """read data from excel file which is to be imported"""
    from xlrd import open_workbook
    """read data from excel"""
    wb = open_workbook(fname)
    values = []
    s = wb.sheet_by_index(0)
    for row in range(s.nrows):
        value = []
        for col in range(s.ncols):
            val = s.cell(row, col).value
            value.append([val])
        values.append(value)
    return values
 
 
def existUC(username, email):
    """check whether this user has already existed!"""
    query="""select username, email
    from uc_members m
    where m.username = '%s'
    or m.email = '%s'""" % (username, email)
    cursor.execute(query)
    rows = cursor.fetchall()
    if len(rows) > 0:
        for row in rows:
            if not email == row[1]:
                "different users have the same username"
                log(1, 'username duplicated in UC : %s\t%s\t\t\t%s\t%s' %
                (username, email, row[0], row[1]))
                return True
            elif not username == row[0]:
                log(2, 'username dismatched in UC : %s\t%s\t\t\t%s\t%s' %
                (username, email, row[0], row[1]))
                return True
            elif email == row[1]:
                log(3, 'user existed in UC : %s\t%s' % (username, email))
                return True
            else:
                return False
    else:
        return False
 
 
def existCDB(username, email):
    """check whether this user has already existed!"""
    query="""select username, email
    from cdb_members m
    where m.username = '%s'
    or m.email = '%s'""" % (username, email)
    cursor.execute(query)
    rows = cursor.fetchall()
    if len(rows) > 0:
        for row in rows:
            if not email == row[1]:
                "different users have the same username"
                log(1, 'username duplicated in CDB: %s\t%s\t\t\t%s\t%s' %
                (username, email, row[0], row[1]))
                return True
            elif not username == row[0]:
                log(2, 'username dismatched in CDB: %s\t%s\t\t\t%s\t%s' %
                (username, email, row[0], row[1]))
                return True
            elif email == row[1]:
                log(3, 'user existed in CDB: %s\t%s' % (username, email))
                return True
            else:
                return False
    else:
        return False
 
 
def seedUC(seed):
    """construct a seed from existed rows"""
    query="""select * from uc_members m where m.username = '%s'""" % seed
    cursor.execute(query)
    rows = cursor.fetchall()
    if len(rows) == 1:
        example = rows[0]
    else:
        raise StandardError('user %s does not exist!' % seed)
    return example
 
 
def seedCDB(seed):
    """construct a seed from existed rows"""
    query="""select * from cdb_members m where m.username = '%s'""" % seed
    cursor.execute(query)
    rows = cursor.fetchall()
    if len(rows) == 1:
        example = rows[0]
    else:
        raise StandardError('user %s does not exist!' % seed)
    return example
 
 
def getNextUid():
    """get the next uid to be generated"""
    query="""select max(uid) from uc_members"""
    cursor.execute(query)
    row = cursor.fetchone()
    uid = row[0] + 1
    return uid
 
 
def constructUC(user):
    """construct UC data to be imported"""
    username = user[0][0].encode('gbk')
    email = user[1][0].encode('gbk')
    if not existUC(username, email):
        uc = seedUC(seed)
        uid = getNextUid()
        uc = (uid, ) + uc[1:]
        uc = uc[:1] + (username, ) + uc[2:]
        uc = uc[:3] + (email, ) + uc[4:]
        return uc
    else:
        return None
 
 
def constructCDB(example):
    """construct CDB data to be imported"""
    username = example[1]
    email = example[3]
    uid = example[0]
    if not existCDB(username, email):
        cdb = seedCDB(seed)
        cdb = (uid, ) + cdb[1:]
        cdb = cdb[:1] + (username, ) + cdb[2:]
        cdb = cdb[:28] + (email, ) + cdb[29:]
        return cdb
    else:
        return None
 
 
def importCDB(example):
    """import data into mysql database"""
    insert="insert into cdb_members values\
(%d, '%s', '%s', '%s', %d, %d, %d, %d, '%s', '%s',\
%d, '%s', %d, '%s', %d, %d, %d, %d, %d, %d,\
%d, %d, %d, %d, %d, %d, %d, %d, '%s', '%s',\
%d, %d, %d, %d, %d, %d, %d, %d, %d, %d,\
'%s', %d, %d, %d, %d, %d, %d\
)\
    " % example
    try:
        cursor.execute(insert)
    except StandardError, e:
        log(0, e)
        db.rollback()
    db.commit()
 
 
def importUC(example):
    """import data into mysql database"""
    insert="insert into uc_members values\
(%d, '%s', '%s', '%s', '%s', '%s', '%s', %d, %d, %d,\
'%s', '%s'\
)\
    " % example
    try:
        cursor.execute(insert)
    except StandardError, e:
        log(0, e)
        db.rollback()
    cdb = constructCDB(example)
    if not cdb == None:
        importCDB(cdb)
    db.commit()
 
 
def check():
    reset()
    log(-1, '')
    log(-1, '*' * 80)
    log(-1, 'check data start')
    data = readData()
    for user in data:
        username = user[0][0].encode('gbk')
        email = user[1][0].encode('gbk')
        existUC(username, email)
        existCDB(username, email)
    log(-1, 'check data ended')
    log(-1, '*' * 80)
    log(-1, '')
 
 
def importData():
    reset()
    log(-1, '')
    log(-1, '*' * 80)
    log(-1, 'import data start')
    data = readData()
    for row in data:
        uc = constructUC(row)
        if not uc == None:
            importUC(uc)
    log(-1, 'import data ended')
    log(-1, '*' * 80)
    log(-1, '')
 
if __name__ == '__main__':
    check()