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 refresh_stock_info(sale_id: int, brand: str, model: str, sale_number: int, unit: str, second_service_id: int):
if not brand or not model:
return
brand_id = brand_and_model_service.get_brand_by_name(brand)
model_id = brand_and_model_service.get_model_by_name(brand_id, model)
stock_info = get_stock_by_model(model_id)
if not stock_info:
stock_info = add_stock_info(model, brand, model_id, brand_id, unit, second_service_id)
stock_id = stock_info.id()
balance_in_db = stock_info.balance()
total_cost_in_db = stock_info.total_cost()
else:
stock_id = stock_info['id']
balance_in_db = stock_info['balance']
total_cost_in_db = stock_info['total_cost']
unit = stock_info['unit']
change_balance = 0 - sale_number
if not balance_in_db:
changed_cost = 0.0
change_type = StockDetail.by_negative()
else:
changed_cost = 0.0 - round(abs(total_cost_in_db / balance_in_db) * sale_number, 2)
change_type = StockDetail.by_sold()
# 更新库存量和库存金额
update_stock_info(stock_id, change_balance, changed_cost)
# 新增库存明细表
add_stock_detail(stock_id, sale_id, changed_cost, sale_number, change_type)
# 更新进货批次中的剩余量
buy_service.decrease_buy_left(stock_id, sale_number)
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 get_operation_by_time(start_date: str, end_date: str):
sql_text = '''
SELECT
first_srv.name AS first_name,
second_srv.name AS second_name,
count(1) AS order_count,
count(1) AS car_count,
sum(sal.number) AS salnumber,
sum(sal.number * sal.unit_price) AS total_price,
sum(sal.number * sal.unit_price) - buy.total_buy AS gross_profit
FROM Sales sal,
service second_srv,
service first_srv,
(SELECT b.changed_money AS total_buy,
changed_id
FROM stock_detail b
WHERE b.type in ({},{},{})) buy
WHERE sal.service_id = second_srv.id
AND sal.createdTime BETWEEN '{}' AND '{}'
AND second_srv.father = first_srv.id
AND buy.changed_id = sal.sale_id
GROUP BY sal.project''' \
.format(StockDetail.by_sold(), StockDetail.by_negative(), StockDetail.by_write_off(), start_date, end_date)
result = db_common_handler.execute(sql_text)
return result
def get_sale_info_by_one_key(key, value, remote=False):
if remote:
sql_str = '''
SELECT createdTime,
orderNo,
carId,
carUser,
carPhone,
carModel,
workerName,
project,
attribute,
pcId,
orderCheckId,
pcSign,
unit_price,
unit,
number,
subtotal,
total,
note,
id
FROM Sales
WHERE {}='{}'
AND userId != ''
AND userId IS NOT NULL
ORDER BY createdTime DESC''' \
.format(key, value)
else:
sql_str = '''SELECT createdTime,
orderNo,
carId,
carUser,
carPhone,
carModel,
workerName,
project,
attribute,
pcId,
orderCheckId,
pcSign,
unit_price,
si.unit,
number,
subtotal,
total,
note,
si.id,
sale_id,
s.brand_name,
s.model_name
FROM Sales si
LEFT JOIN stock_detail sd on si.sale_id = sd.changed_id and sd.type in ({},{},{})
LEFT JOIN stock_info s on s.id = sd.stock_id
WHERE {}='{}'
ORDER BY createdTime DESC''' \
.format(StockDetail.by_sold(), StockDetail.by_negative(), StockDetail.by_write_off(), key, value)
data = execute(sql_str)
return data
def update_negative_info(sale_id: int, total: float):
sql_text = '''UPDATE stock_detail
SET type = {},
changed_money = {:.2f}
WHERE type = {}
AND changed_id = {}''' \
.format(StockDetail.by_write_off(), total, StockDetail.by_negative(), sale_id)
return execute(sql_text)
def write_off_negative_on_hand(stock_id: int, buy_id: int, buy_price: float,
sale_id: int):
sql_text = '''
UPDATE stock_detail
SET changed_id = {},
changed_money = {},
type = {}
WHERE stock_id = {}
and changed_id = {}
''' \
.format(buy_id, buy_price, StockDetail.by_write_off(), StockDetail.sold(), stock_id,
StockDetail.by_negative(), sale_id)
result = execute(sql_text)
return result