You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
118 lines
3.5 KiB
118 lines
3.5 KiB
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
|
|
|
|
|