import sqlite3


def dict_factory(cursor, row):
    d = {}
    for idx, col in enumerate(cursor.description):
        d[col[0]] = row[idx]
    return d


def select_goods(name, model, process):
    conn = sqlite3.connect("kucun.db")
    conn.row_factory = dict_factory
    cursor = conn.cursor()
    sql = 'select * from goods where name like \'%' + name + '%\' and model like \'%' + model + '%\' and process like\'%' + process + '%\';'
    cursor.execute(sql)
    goods = cursor.fetchall()
    cursor.close()
    conn.commit()
    conn.close()
    return goods


def select_records():
    conn = sqlite3.connect('kucun.db')
    conn.row_factory = dict_factory
    cursor = conn.cursor()

    cursor.execute(
        'select time,name,model,factory,process,inorout,change,people from records left join goods on records.id=goods.id order by time desc ;')
    records = cursor.fetchall()
    cursor.close()
    conn.commit()
    conn.close()
    return records


def insert_goods(name, model, factory, process, price):
    conn = sqlite3.connect('kucun.db')
    conn.row_factory = dict_factory
    cursor = conn.cursor()

    cursor.execute('insert into goods values(null,?,?,?,?,?,0,0);', (name, model, factory, process, price,))
    cursor.close()
    conn.commit()
    conn.close()


def del_goods(id):
    conn = sqlite3.connect('kucun.db')
    conn.row_factory = dict_factory
    cursor = conn.cursor()

    cursor.execute('delete from goods where id=?;', (id,))
    cursor.close()
    conn.commit()
    conn.close()


def insert_records(id, kind, change, people):
    conn = sqlite3.connect('kucun.db')
    conn.row_factory = dict_factory
    cursor = conn.cursor()

    if kind == 1:
        cursor.execute('insert into records values(?,datetime(\'now\',\'localtime\'),?,?,?);',
                       (id, '入库', change, people,))
        cursor.execute('update goods set number=number+? where id=?;', (change, id,))
    else:
        cursor.execute('insert into records values(?,datetime(\'now\',\'localtime\'),?,?,?);',
                       (id, '出库', change, people,))
        cursor.execute('update goods set number=number-? where id=?;', (change, id,))
    cursor.close()
    conn.commit()
    conn.close()


def update_goods(id, name, model, process, factory, price,safenumber):
    conn = sqlite3.connect('kucun.db')
    conn.row_factory = dict_factory
    cursor = conn.cursor()

    cursor.execute('update goods set name=? ,model=?,process=?,factory=?,price=?,safenumber = ? where id=?;',
                   (name, model, process, factory, price,safenumber, id,))
    cursor.close()
    conn.commit()
    conn.close()


def count_goods(process, inorout, date1, date2):
    conn = sqlite3.connect('kucun.db')
    conn.row_factory = dict_factory
    cursor = conn.cursor()

    cursor.execute(
        'select goods.id as id,name,model,process,factory,price,number,inorout,sum(change) as sum_change,price*sum(change) as money from goods left join records on goods.id = records.id where process=? and inorout=? and time >=? and time<=? group by inorout,goods.id order by goods.id;',
        (process, inorout, date1, date2), )
    counts = cursor.fetchall()
    cursor.close()
    conn.commit()
    conn.close()
    return counts


def get_names(kind):
    conn = sqlite3.connect('kucun.db')
    conn.row_factory = dict_factory
    cursor = conn.cursor()
    cursor.execute('select {0} from names;'.format(str(kind)),)
    names = cursor.fetchall()
    cursor.close()
    conn.commit()
    conn.close()
    # print(names)
    return names