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 add_stock_info(model, brand, model_id, brand_id, unit, second_service_id):
second_service = service_handler.get_service_by_id(second_service_id)
stock_info = Stock()
stock_info.model_id(model_id).model_name(model)
stock_info.brand_id(brand_id).brand_name(brand)
stock_info.first_service_id(second_service['father_id']).first_service_name(second_service['father_name'])
stock_info.second_service_id(second_service_id).second_service_name(second_service['name'])
stock_info.unit(unit)
stock_info.name(brand + '-' + model)
stock_info.create_op(config.login_user_info[0]).create_time(time_utils.get_now())
stock_id = stock_handler.add_stock_info(stock_info)
stock_info.id(stock_id)
return stock_info
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(self, model, brand, model_id, brand_id, line_number, total, number, unit):
service_id = getattr(self, 'service_' + line_number).currentData()
service_name = getattr(self, 'service_' + line_number).currentText()
stock_info = Stock()
services = service_name.split('-')
service_ids = service_id.split('-')
first_service_id = int(service_ids[0])
first_service_name = services[0]
second_service_id = int(service_ids[1])
second_service_name = services[1]
stock_info.model_id(model_id).model_name(model)
stock_info.brand_id(brand_id).brand_name(brand)
stock_info.first_service_id(first_service_id).first_service_name(first_service_name)
stock_info.second_service_id(second_service_id).second_service_name(second_service_name)
stock_info.unit(unit).name(brand + '-' + model)
stock_info.create_op(config.login_user_info[0]).create_time(time_utils.get_now())
stock_info.total_cost(total).balance(number)
stock_id = stock_handler.add_stock_info(stock_info)
return stock_id
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