def get_detail_info(second_srv_id: int, start_time: str, end_time: str):
sql_text = '''
SELECT bi.id,
bi.buy_date,
si.brand_name,
si.model_name,
bi.number,
si.unit,
bi.unit_price,
bi.total,
bi.supplier_id,
sl.supplier_name,
si.first_service_name || '-' || si.second_service_name,
bi.paid,
bi.unpaid,
di.value_desc,
bi.stock_id
FROM buy_info bi, stock_info si, dictionary di, supplier sl
WHERE si.second_service_id = {}
and bi.stock_id = si.id
and bi.buy_type = {}
and di.key_id = bi.buy_type
and di.group_name = 'buy_type'
and sl.id = bi.supplier_id
and bi.buy_date BETWEEN '{}' and '{}'
'''.format(second_srv_id, BuyInfo.bought(), start_time,
end_time)
result = execute(sql_text)
return result
def _add_buy_info(stock_id, supplier_id, price, number, buy_date, unpaid,
paid, total, payment, note, balance):
buy_info = BuyInfo()
buy_info.buy_date(buy_date)
buy_info.stock_id(stock_id)
buy_info.supplier_id(supplier_id)
buy_info.unit_price(price)
buy_info.payment_method(payment)
buy_info.number(abs(number))
create_time = time_utils.get_now()
buy_info.create_time(create_time)
create_op = Common.config.login_user_info[0]
buy_info.create_op(create_op)
buy_info.paid(abs(paid))
buy_info.unpaid(abs(unpaid))
buy_info.total(abs(total))
buy_info.note(note)
buy_info.buy_type(BuyInfo.bought())
# 计算剩余量
if balance < 0:
left_number = number + balance
else:
left_number = number
buy_info.left(left_number)
return buy_handler.add_buy_info(buy_info)
def get_history_buy_info_by_model_id(model_id: int):
sql_text = '''
SELECT
sts.brand_id,
sts.model_id,
sts.brand_name,
sts.model_name,
min_price,
avg_price,
lbi.unit_price
FROM (
SELECT
si.brand_id,
si.model_id,
si.brand_name,
si.model_name,
min(bi.unit_price) min_price,
avg(bi.unit_price) avg_price,
max(bi.id) max_id
FROM buy_info bi,
stock_info si
WHERE bi.stock_id = si.id
AND si.model_id = {}
AND bi.buy_type = {}
GROUP BY si.brand_name, si.model_name
) sts,
buy_info lbi
WHERE lbi.id = max_id''' \
.format(model_id, BuyInfo.bought())
result = execute(sql_text)
return result
def get_buy_info_summary_by_time(start_date: str, end_date: str):
sql_text = '''
SELECT
si.first_service_id,
si.second_service_id,
si.first_service_name,
si.second_service_name,
sum(bi.number) total_number,
sum(bi.total) total_price
FROM buy_info bi,
stock_info si
WHERE bi.stock_id = si.id
AND bi.buy_type = {}
AND bi.buy_date BETWEEN '{}' AND '{}'
GROUP BY si.first_service_id,
si.first_service_name,
si.second_service_id,
si.second_service_name
ORDER BY si.first_service_id,
si.first_service_name,
si.second_service_id,
si.second_service_name''' \
.format(BuyInfo.bought(), start_date, end_date)
result = execute(sql_text)
return result
def get_compare_info(model_id: int):
sql_text = '''
SELECT
sp.supplier_name,
si.brand_name,
si.model_name,
avg(bi.unit_price) avg_price,
count(1) buy_times,
sum(bi.number) total_number
FROM buy_info bi,
stock_info si,
supplier sp
WHERE bi.stock_id = si.id
AND si.model_id = {}
AND bi.buy_type = {}
AND bi.supplier_id = sp.id
GROUP BY sp.supplier_name,
si.brand_name,
si.model_name
ORDER BY sp.supplier_name,
si.brand_name,
si.model_name
''' \
.format(model_id, BuyInfo.bought())
result = execute(sql_text)
return result
def get_all_buy_info():
sql_text = '''
SELECT
bi.id,
bi.buy_date,
si.brand_name,
si.model_name,
bi.number,
si.unit,
bi.unit_price,
bi.total,
bi.supplier_id,
sl.supplier_name,
si.first_service_name || '-' || si.second_service_name,
bi.paid,
bi.unpaid,
di.value_desc,
bi.stock_id
FROM buy_info bi, stock_info si, supplier sl, service sr, dictionary di
WHERE bi.stock_id = si.id
AND bi.supplier_id = sl.id
AND sr.id = si.second_service_id
AND di.key_id = bi.buy_type
AND di.group_name = 'buy_type'
AND bi.buy_type in({},{})
ORDER BY buy_date DESC'''.format(BuyInfo.bought(),
BuyInfo.returned())
result = execute(sql_text)
return result
def get_unpaid_gt_zero(stock_id: int):
sql_text = '''SELECT ID, unpaid, paid
FROM buy_info
WHERE stock_id = {}
AND unpaid > 0.0
AND buy_type = {}
order by buy_date''' \
.format(stock_id, BuyInfo.bought())
return execute(sql_text)
def get_left_gt_zero(stock_id: int):
sql_text = '''SELECT ID, number, left_number
FROM buy_info
WHERE stock_id = {}
AND left_number > 0
AND buy_type = {}
order by buy_date''' \
.format(stock_id, BuyInfo.bought())
return execute(sql_text)
def add_buy_info(stock_id,
supplier_id,
price,
number,
buy_date,
unpaid,
paid,
total,
payment,
note,
left_number,
op=None,
buy_type=None,
state=0):
buy_info = BuyInfo()
buy_info.buy_date(buy_date)
buy_info.stock_id(stock_id)
buy_info.supplier_id(supplier_id)
buy_info.unit_price(price)
buy_info.payment_method(payment)
if buy_type == BuyInfo.calibrated():
buy_info.number(number)
buy_info.total(total)
else:
buy_info.number(abs(number))
buy_info.total(abs(total))
create_time = time_utils.get_now()
buy_info.create_time(create_time)
if not op:
create_op = common.config.login_user_info[0]
else:
create_op = op
buy_info.create_op(create_op)
buy_info.paid(abs(paid))
buy_info.unpaid(abs(unpaid))
buy_info.note(note)
# 判断是进货还是退货
if not buy_type:
if number < 0:
buy_info.buy_type(BuyInfo.returned())
left_number = 0
else:
buy_info.buy_type(BuyInfo.bought())
else:
buy_info.buy_type(buy_type)
if state:
buy_info.state(state)
buy_info.left(left_number)
return buy_handler.add_buy_info(buy_info)
def get_arrears_info_buy(supplier_id: int):
sql_text = '''
SELECT
bi.buy_date,
si.brand_name,
si.model_name,
bi.number,
si.unit,
bi.unit_price,
bi.total,
bi.paid,
bi.unpaid,
bi.id
FROM buy_info bi, stock_info si, supplier sl
WHERE bi.stock_id = si.id
AND bi.supplier_id = sl.id
AND bi.unpaid > 0.0
AND sl.id = {}
AND bi.buy_type = {}
ORDER BY buy_date''' \
.format(supplier_id, BuyInfo.bought())
result = execute(sql_text)
return result
