#encoding: utf-8

import MySQLdb

from gconf import DB_HOST, DB_PORT, DB_USER, DB_PASSWD, DB_DBNAME, DB_CHARSET
from utils import md5

SQL_USER_LIST = 'select id,name,cls,tel,addr from account where isdel=0'
SQL_USER_GET = 'select id,name,cls,tel,addr,age from account where id=%s'
SQL_USER_GET_PASSWORD = 'select id,name,password from account where id=%s'
SQL_USER_INSERT = 'insert into account(name, cls, tel, addr, password) values(%s, %s, %s, %s, %s)'
SQL_USER_LOGIN = 'select id, name from account where name=%s and password=%s and isdel=0'
SQL_USER_DELETE = 'update account set isdel=1 where id=%s'
SQL_USER_UPDATE = 'update account set cls=%s, tel=%s, addr=%s, age=%s where id=%s'
SQL_USER_PASSWD_UPDATE = 'update account set password=%s where id = %s'


def get_user(uid):
    conn = MySQLdb.connect(host=DB_HOST, port=DB_PORT, user=DB_USER, passwd=DB_PASSWD, db=DB_DBNAME, charset=DB_CHARSET)
    cur = conn.cursor()
    cur.execute(SQL_USER_GET, (uid,))
    users = cur.fetchall()
    cur.close()
    conn.close()
    return users

def get_password(uid):
    conn = MySQLdb.connect(host=DB_HOST, port=DB_PORT, user=DB_USER, passwd=DB_PASSWD, db=DB_DBNAME, charset=DB_CHARSET)
    cur = conn.cursor()
    cur.execute(SQL_USER_GET_PASSWORD, (uid,))
    users = cur.fetchall()
    cur.close()
    conn.close()
    return users
    

def get_users():
    conn = MySQLdb.connect(host=DB_HOST, port=DB_PORT, user=DB_USER, passwd=DB_PASSWD, db=DB_DBNAME, charset=DB_CHARSET)
    cur = conn.cursor()
    cur.execute(SQL_USER_LIST)
    users = cur.fetchall()
    cur.close()
    conn.close()
    return users


def add_user(name, clazz, tel, addr, password):
    conn = MySQLdb.connect(host=DB_HOST, port=DB_PORT, user=DB_USER, passwd=DB_PASSWD, db=DB_DBNAME, charset=DB_CHARSET)
    cur = conn.cursor()
    cur.execute(SQL_USER_INSERT, (name, clazz, tel, addr, md5(password)))
    conn.commit()
    cur.close()
    conn.close()


def validate_login(name, password):
    conn = MySQLdb.connect(host=DB_HOST, port=DB_PORT, user=DB_USER, passwd=DB_PASSWD, db=DB_DBNAME, charset=DB_CHARSET)
    cur = conn.cursor()
    cur.execute(SQL_USER_LOGIN, (name, md5(password)))
    user = cur.fetchone()
    cur.close()
    conn.close()
    if user:
        return {'id': user[0], 'name' : user[1]}
    else:
        return None

def del_user(uid):
    conn = MySQLdb.connect(host=DB_HOST, port=DB_PORT, user=DB_USER, passwd=DB_PASSWD, db=DB_DBNAME, charset=DB_CHARSET)
    cur = conn.cursor()
    cur.execute(SQL_USER_DELETE, (uid, ))
    conn.commit()
    cur.close()
    conn.close()


def save_users(users, path):
    fhandler = open(path, 'w')
    for user in users:
        fhandler.write('%s\n' % ','.join(user))
    fhandler.close()


def update_user(uid, clazz, tel, addr, age):
    conn = MySQLdb.connect(host=DB_HOST, port=DB_PORT, user=DB_USER, passwd=DB_PASSWD, db=DB_DBNAME, charset=DB_CHARSET)
    cur = conn.cursor()
    cur.execute(SQL_USER_UPDATE, (clazz, tel, addr, age, uid))
    conn.commit()
    cur.close()
    conn.close()


def change_password(uid, password):
    conn = MySQLdb.connect(host=DB_HOST, port=DB_PORT, user=DB_USER, passwd=DB_PASSWD, db=DB_DBNAME, charset=DB_CHARSET)
    cur = conn.cursor()
    cur.execute(SQL_USER_PASSWD_UPDATE, (password, uid))
    conn.commit()
    cur.close()
    conn.close()
