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