import sqlite3


class SqlCon:
    """
    建立本地sqlite3数据库连接
    """

    def __init__(self, database=r'C:/sqlite/MMS.db'):
        self.db_con = sqlite3.connect(database)
        self.db_cursor = self.db_con.cursor()


if __name__ == '__main__':
    # 初始化数据库，建立表单
    db_test = SqlCon()
    sql_1 = 'CREATE TABLE IF NOT EXISTS location (loc_id INTEGER PRIMARY KEY, loc_name TEXT NOT NULL UNIQUE, ' \
            'loc_status TEXT)'
    sql_2 = 'CREATE TABLE IF NOT EXISTS mat_cls_info (cls_id INTEGER PRIMARY KEY, erp_code TEXT, ' \
            'cls_name TEXT NOT NULL, cls_spec TEXT, cls_spec_int INTEGER, max_quantity INTEGER, ' \
            'cls_status TEXT)'
    sql_3 = 'CREATE TABLE IF NOT EXISTS user (user_id INTEGER PRIMARY KEY, user_name TEXT NOT NULL UNIQUE, ' \
            'user_card TEXT, user_status TEXT)'
    sql_4 = 'CREATE TABLE IF NOT EXISTS mat_info (mat_id INTEGER PRIMARY KEY, cls_id INTEGER NOT NULL, ' \
            'batch_num TEXT, barcode TEXT NOT NULL UNIQUE, mat_quality_status TEXT, mat_quality_detail TEXT)'
    sql_0 = 'CREATE TABLE IF NOT EXISTS mat_flow_info (flow_id INTEGER PRIMARY KEY, mat_id INTEGER, ' \
            'user_id_1 INTEGER, user_id_2 INTEGER, datetime TEXT, mat_status TEXT, loc_id INTEGER)'
    db_test.db_cursor.execute(sql_0)
    db_test.db_cursor.execute(sql_1)
    db_test.db_cursor.execute(sql_2)
    db_test.db_cursor.execute(sql_3)
    db_test.db_cursor.execute(sql_4)
    db_test.db_con.commit()
    # 输入地点
    from location import Location
    # 运保部默认第一个
    loc_name_list = ['运营保障部', '3#涂布机', '4#涂布机', '涂布复合机',
                     '新8色印刷机', 'TC3']
    for i in loc_name_list:
        Location(loc_name=i).loc_register()
    # 输入胶料类
    from material import MatClass
    class_list = [['0101020031', 'X913', '1*160kg', 160, 0], ['0101010145', 'BT100胶稀释液', '雅士 1*16kg', 16, 0],
                  ['0101020011', 'BT100', '雅士 1*17kg', 17, 0], ['0199011019', '树脂MZ01', '', 18, 0],
                  ['0199010133', '乙醇', '1*14kg', 14, 0], ['0101020036', '分装丁酮', '22kg*桶', 22, 0],
                  ['0101020034', 'N100', '1*17kg', 17, 0], ['0101010213', 'N100胶稀释液', '1*17kg', 17, 0],
                  ['0101020033', 'BM100', '雅士 1*17kg', 17, 0], ['0199010797', '消泡剂DF2459', '1*45kg', 45, 0]]
    for i in class_list:
        MatClass(erp_code=i[0], cls_name=i[1], cls_spec=i[2], cls_spec_int=i[3], max_quantity=i[4]).class_register()
    # 输入用户信息
    from user import User
    user_list = [['赵英杰', '52300060'], ['张昊宇', '52300248']]
    for i in user_list:
        User(user_name=i[0], user_card=i[1]).user_register()
    db_test.db_con.close()