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)