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_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