def update_attribute(attribute_id, name):
sql_text = '''
UPDATE attributes
SET name = '{}'
WHERE ID = {}''' \
.format(name, attribute_id)
execute(sql_text)
def update_supplier_unpaid(supplier_id: int, unpaid: Decimal):
sql_text = '''update supplier
set unpaid = unpaid + {:.2f}
where id = {}
''' \
.format(unpaid, supplier_id)
execute(sql_text)
def update_model_id(stock_id: int, model_id: int):
sql_text = '''
UPDATE stock_info
SET model_id = {}
WHERE ID = {}''' \
.format(model_id, stock_id)
execute(sql_text)
def update_second_service_name(service_id, service_name):
sql_text = '''
UPDATE STOCK_INFO
SET second_service_name = '{}'
WHERE second_service_id = {}''' \
.format(service_name, service_id)
execute(sql_text)
def update_brand_id(stock_id: int, brand_id: int):
sql_text = '''
UPDATE stock_info
SET brand_id = {}
WHERE ID = {}''' \
.format(brand_id, stock_id)
execute(sql_text)
def update_item_id(old_attr_id, new_attr_id):
sql_text = '''
UPDATE sales_item
SET attribute_id = {}
WHERE attribute_id = {}''' \
.format(new_attr_id, old_attr_id)
execute(sql_text)
def update_brand_name(stock_id: int, brand_name: str):
sql_text = '''
UPDATE stock_info
SET name = '{}' || '-' || model_name,
brand_name = '{}'
WHERE ID = {}''' \
.format(brand_name, brand_name, stock_id)
execute(sql_text)
def create_worker():
execute('''CREATE TABLE Worker
(id INTEGER PRIMARY KEY AUTOINCREMENT,
workerName VARCHAR(20) NOT NULL,
sex VARCHAR (4) NOT NULL ,
idCard VARCHAR (20) NOT NULL ,
createdTime DATETIME NOT NULL
);''')
def update_stock_balance(stock_id: int, balance: int, total: Decimal):
if stock_id:
sql_text = '''
UPDATE stock_info
SET balance = balance + {},
total_cost = total_cost + {:.2f}
WHERE ID = {}'''.format(balance, total, stock_id)
execute(sql_text)
def update_customer_by_car_id(customer: Customer):
sql_text = '''UPDATE user
set carModel = '{}',
carPhone = '{',
userName = '******'
WHERE carId = '{}\'''' \
.format(customer.car_model(), customer.phone(), customer.username(), customer.car_id())
execute(sql_text)
def create_sale_item():
execute('''
CREATE TABLE sales_item
(
sale_id VARCHAR(35),
attribute_id INTEGER,
attribute_value VARCHAR(50)
)
''')
execute('''CREATE INDEX sales_item_sale_id ON sales_item (sale_id)''')
def create_brand():
execute('''
CREATE TABLE brand
(
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
brand_name VARCHAR(50),
create_time VARCHAR(30),
create_op VARCHAR(30),
delete_state INT DEFAULT 0 NOT NULL
)
''')
def create_customer():
execute('''
CREATE TABLE User
(
id INTEGER PRIMARY KEY AUTOINCREMENT,
userName VARCHAR(20),
carModel VARCHAR(30),
carPhone VARCHAR(15),
carId VARCHAR(10),
createdTime DATETIME
)''')
execute('''CREATE UNIQUE INDEX carid ON User (carId)''')
def create_return_visit_info():
execute('''
CREATE TABLE return_visit
(id INTEGER PRIMARY KEY AUTOINCREMENT,
create_Time DATETIME NOT NULL ,
next_visit_time DATETIME NOT NULL ,
phone VARCHAR(50) NOT NULL ,
carId VARCHAR(10) NOT NULL ,
username VARCHAR(50) NOT NULL ,
state INT(2) NOT NULL
)
''')
def create_return_visit_info():
execute('''
CREATE TABLE CallBack
(id INTEGER PRIMARY KEY AUTOINCREMENT,
createdTime DATETIME NOT NULL ,
callbackTime DATETIME NOT NULL ,
phone VARCHAR(50) NOT NULL ,
carId VARCHAR(10) NOT NULL ,
username VARCHAR(50) NOT NULL ,
state VARCHAR (2) NOT NULL
)
''')
def create_device():
execute('''
CREATE TABLE Device
(id INTEGER PRIMARY KEY AUTOINCREMENT,
createdTime DATETIME NOT NULL ,
name VARCHAR (20) NOT NULL ,
ip VARCHAR (20) NOT NULL ,
state INT
)
''')
execute('''CREATE UNIQUE INDEX ip ON Device (ip)''')
def create_service_item():
execute('''
CREATE TABLE service_item
(
id INTEGER PRIMARY KEY AUTOINCREMENT,
service_id INTEGER,
attribute_id INTEGER,
attribute_name VARCHAR(20),
create_time VARCHAR(50),
create_op INTEGER
)
''')
execute('''CREATE UNIQUE INDEX service_item_id_uindex ON service_item (id)''')
def create_stock_detail():
execute('''
CREATE TABLE stock_detail
(
Id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
stock_id INTEGER,
changed_id INTEGER NOT NULL,
changed_money INT(10, 2),
changed_number INTEGER,
type INT(2),
update_time VARCHAR(50),
update_op INTEGER
)
''')
def create_service_item():
execute('''
CREATE TABLE service_item
(
id INTEGER NOT NULL,
service_id INTEGER,
attribute_id INTEGER,
attribute_name VARCHAR(20),
create_time VARCHAR(50),
create_op INTEGER
)
''')
execute(
'''CREATE UNIQUE INDEX service_item_id_uindex ON service_item (id)''')
def create_service():
execute('''
CREATE TABLE service
(
id INTEGER PRIMARY KEY
AUTOINCREMENT,
createdTime DATETIME NOT NULL,
name VARCHAR(20) NOT NULL,
father INTEGER NOT NULL,
level INT
)
''')
execute('''CREATE UNIQUE INDEX father ON service (father, name)''')
def get_stock_buy_info(stock: Stock, start_date: str, end_date: str):
sql_text = '''
SELECT
FIRST_SERVICE_NAME,
SECOND_SERVICE_NAME,
BRAND_NAME,
MODEL_NAME,
BALANCE,
ifnull(sum(s.number), 0) AS sale_number
FROM stock_info si
LEFT JOIN stock_detail sd on sd.stock_id = si.id
LEFT JOIN Sales s on s.id = sd.changed_id and sd.type in ({}, {},{})''' \
.format(StockDetail.by_write_off(), StockDetail.by_negative(), StockDetail.by_bought())
if start_date != end_date:
sql_text += ''' AND s.sale_date BETWEEN '{}' AND '{}\''''.format(start_date, end_date)
if stock.second_service_id():
sql_text += ''' WHERE si.second_service_id = {}'''.format(stock.second_service_id())
if stock.first_service_id():
sql_text += ''' AND si.first_service_id = {}'''.format(stock.first_service_id())
if stock.brand_name():
sql_text += ''' AND si.brand_name like '%{}%\''''.format(stock.brand_name())
if stock.model_name():
sql_text += ''' AND si.model_name like '%{}%\''''.format(stock.model_name())
sql_text += ''' GROUP BY FIRST_SERVICE_NAME,
SECOND_SERVICE_NAME,
BRAND_NAME,
MODEL_NAME,
BALANCE '''
result = execute(sql_text)
return result
def get_model_by_name(model_name: str, brand_id: int):
sql_text = '''SELECT ID, MODEL_NAME FROM model WHERE DELETE_STATE = 0 AND brand_id = {} AND model_name = '{}\'''' \
.format(brand_id, model_name)
result = execute(sql_text, True)
return result
def update_model(model_id: int, model_name):
sql_text = '''UPDATE model
SET model_name = '{}'
WHERE id = {}''' \
.format(model_name, model_id)
return execute(sql_text)
def update_brand(brand_id: int, brand_name: str):
sql_text = '''UPDATE BRAND SET brand_name = '{}' WHERE ID = {}'''.format(
brand_name, brand_id)
result = execute(sql_text)
return result
def add_payment_detail(payment):
sql_text = '''
INSERT INTO PAYMENT_DETAIL(
BUY_ID,
PAYMENT_METHOD,
PAID,
UNPAID,
CREATE_TIME,
CREATE_OP,
refund_type,
supplier_id,
note
)
VALUES (
{},
{},
{:.2f},
{:.2f},
'{}',
{},
{},
{},
'{}'
)''' \
.format(payment.buy_id(), payment.payment_method(), payment.paid(), payment.unpaid(),
get_now(), Common.config.login_user_info[0], payment.refund_type(), payment.supplier_id(),
payment.note())
result = execute(sql_text)
return result
def get_brand_by_name(brand_name: str):
sql_text = '''SELECT ID, BRAND_NAME FROM brand WHERE DELETE_STATE = 0 and brand_name ='{}\'''' \
.format(brand_name)
result = execute(sql_text, True)
return result
def get_order_no(today):
month = str(today.month)
day = str(today.day)
year = today.year
if len(month) < 2:
month = "0" + month
if len(day) < 2:
day = "0" + day
start_time = '{}-{}-{} 00:00:00'.format(year, month, day)
end_time = '{}-{}-{} 23:59:59'.format(year, month, day)
sql_text = '''SELECT count(1) FROM Sales WHERE createdTime BETWEEN \'{}\' AND \'{}\''''.format(
start_time, end_time)
data = execute(sql_text)
number = str(len(data) + 1)
# number格式为百位数，如001，002，100，120
if len(number) < 2:
number = "00" + number
elif len(number) < 3:
number = "0" + number
order_no = "{}{}{}{}".format(year, month, day, number)
return order_no
def get_negative_on_hand():
sql_text = '''
SELECT
s.id,
s.sale_date,
si.brand_name,
si.model_name,
s.number,
si.balance,
si.unit,
si.brand_id,
si.model_id,
si.id,
si.first_service_name,
si.first_service_id,
si.second_service_name,
si.second_service_id,
'点击销负'
FROM stock_info si,
Sales s,
stock_detail sd
WHERE sd.changed_id = s.id
AND sd.type = {}
AND sd.stock_id = si.id
GROUP BY s.id,s.sale_date,si.brand_name, si.model_name, s.number, si.balance, si.unit''' \
.format(StockDetail.by_negative())
result = execute(sql_text)
return result
def undo_delete_attribute_logical(attribute_id: int):
sql_text = '''
UPDATE attributes
SET delete_state = 0
WHERE ID = {}''' \
.format(attribute_id)
return execute(sql_text)
def add_attribute(attribute: Attribute):
sql_text = '''
INSERT INTO attributes(name,is_required,create_time,create_op,delete_state)
VALUES ('{}', {}, '{}', {}, {})''' \
.format(attribute.name(), attribute.is_required(), attribute.create_time(), attribute.create_op(),
attribute.delete_state())
return execute(sql_text)
