def _submit(self):
stock = Stock()
brand = self.brand_comboBox
stock.brand_name(brand.currentText()).brand_id(brand.currentData())
model = self.model_combo_box
stock.model_name(model.currentText()).model_id(model.currentData())
first_service = self.first_service_combo_box
stock.first_service_id(first_service.currentData()).first_service_name(
first_service.currentText())
second_service = self.second_service_combo_box
stock.second_service_name(
second_service.currentText()).second_service_id(
second_service.currentData())
name = brand.currentText() + '-' + model.currentText()
stock.name(name)
stock.unit(self.unit.text())
stock.create_op(common.config.login_user_info[0])
stock.create_time(get_now())
stock_id = stock_handler.add_stock_info(stock)
stock.id(stock_id)
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 search(self):
start_date = self.start_date.date().toString('yyyy-MM-dd')
end_date = self.end_date.date().toString('yyyy-MM-dd')
brand_name = self.brand.text()
model_name = self.model.text()
first_srv_id = self.first_srv_combo.currentData()
second_srv_id = self.second_srv_combo.currentData()
stock = Stock()
stock.brand_name(brand_name)
stock.model_name(model_name)
stock.first_service_id(first_srv_id)
stock.second_service_id(second_srv_id)
record = stock_handler.get_stock_buy_info(stock, start_date, end_date)
table_utils.set_table_content_with_merge(self.tableView, record, self.table_title, 0)
self.tableView.resizeColumnsToContents()
def add_stock_info(stock: Stock):
sql_text = '''
INSERT INTO stock_info(
UNIT,
first_service_name,
first_service_id,
model_id,
model_name,
brand_id,
brand_name,
name,
second_service_id,
second_service_name,
balance,
total_cost,
create_time,
create_op
)
VALUES(
'{}',
'{}',
{},
{},
'{}',
{},
'{}',
'{}',
{},
'{}',
{},
{:.2f},
'{}',
{}
)''' \
.format(stock.unit(), stock.first_service_name(), stock.first_service_id(), stock.model_id(),
stock.model_name(), stock.brand_id(), stock.brand_name(), stock.name(), stock.second_service_id(),
stock.second_service_name(), stock.balance(), stock.total_cost(), stock.create_time(),
stock.create_op())
new_stock_id = execute(sql_text)
return new_stock_id