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_buy_info_detail_by_time(start_time: str, end_time: str):
sql_text = '''
SELECT
bi.buy_date,
si.brand_name,
si.model_name,
bi.number,
si.unit,
bi.unit_price,
bi.total,
sl.supplier_name,
si.first_service_name || '-' || si.second_service_name,
bi.paid,
bi.unpaid,
di.value_desc
FROM buy_info bi, stock_info si, dictionary di, supplier sl
WHERE bi.stock_id = si.id
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 '{}'
and bi.buy_type in ({},{})
'''.format(start_time, end_time, BuyInfo.bought(),
BuyInfo.returned())
result = execute(sql_text)
return result
def __init__(self, buy: BuyInfo):
super(PayOffArrearsDialog, self).__init__()
self.setupUi(self)
self.setWindowTitle('请填写支付金额')
self._init_input_info(buy)
self._init_signal_and_slot()
self.buy_id = buy.buy_id()
self.supplier_id = buy.supplier_id()
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_calibration():
sql_text = '''
SELECT
bi.id,
si.id as stock_id,
bi.buy_date,
BRAND_NAME,
MODEL_NAME,
di.value_desc,
si.balance,
bi.number,
si.total_cost,
bi.total,
ad.userName,
bi.note,
dic.value_desc
FROM stock_info si,buy_info bi, Admin ad, stock_detail sd, dictionary di, dictionary dic
WHERE si.id=bi.stock_id
AND bi.buy_type= {}
AND si.create_op = ad.id
AND sd.changed_id = bi.id
AND sd.stock_id = si.id
AND sd.type in ({},{})
AND dic.key_id = bi.state
AND dic.group_name = 'buy_state'
AND di.key_id = sd.type
AND di.group_name = 'stock_type\'''' \
.format(BuyInfo.calibrated(), StockDetail.by_decreased(), StockDetail.by_increased())
result = execute(sql_text)
return result
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_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 '{}'
ORDER BY si.brand_name, si.model_name, bi.buy_date''' \
.format(second_srv_id, BuyInfo.bought(), start_time, end_time)
result = execute(sql_text)
return result
def _submit(self):
money_changed = self.money_changed.text()
balance_changed = self.balance_changed.text()
if not money_changed or not balance_changed:
QMessageBox.information(self.addButton, '提示', '调整金额和调整数量不能为空！')
self.balance_changed.setFocus()
return
money_changed = Decimal(money_changed)
balance_changed = int(balance_changed)
if money_changed == self.original_cost and balance_changed == self.original_balance:
QMessageBox.information(self.addButton, '提示', '金额和数量未做调整，请重新填写！')
self.balance_changed.setFocus()
return
changed_number = balance_changed - self.original_balance
changed_cost = money_changed - self.original_cost
buy_date = self.calibration_date.date().toString('yyyy-MM-dd')
payment_method = list(Payment.get_payment_method().keys())[0]
note = self.notes.text()
create_op = int(self.staffComb.currentData())
try:
db_transaction_util.begin()
logger.info('新增库存校准数据')
buy_id = buy_service.add_buy_info(self.stock_id, 9999, 0.0,
changed_number, buy_date, 0.0,
0.0, changed_cost,
payment_method, note, 0,
create_op, BuyInfo.calibrated(),
BuyInfo.under_reviewed())
if changed_number >= 0:
change_type = StockDetail.by_increased()
else:
change_type = StockDetail.by_decreased()
stock_service.add_stock_detail(self.stock_id, buy_id,
abs(changed_cost),
abs(changed_number), change_type)
db_transaction_util.commit()
logger.info('库存校准数据新增完成')
QMessageBox.information(self.addButton, '提示', '库存校准成功，请等待数据审核！')
self.close()
except Exception as e:
db_transaction_util.rollback()
logger.error(e)
logger.error('traceback.format_exc():\n{}'.format(
traceback.format_exc()))
def do_review(self):
review_id = table_utils.get_table_current_index_info(self.tableView, 0)
if not review_id:
QMessageBox.information(self.reviewButton, '提示', '请选择待审核的库存校准数据！')
return
stock_id = int(
table_utils.get_table_current_index_info(self.tableView, 1))
stock_detail = stock_detail_handler.get_calibration_detail_by_buy_id(
review_id)
changed_type = stock_detail['type']
answer = QMessageBox.information(
self.reviewButton, '校准审核', '是否审核通过该项库存校准？',
QMessageBox.Yes | QMessageBox.No | QMessageBox.Cancel,
QMessageBox.Yes)
try:
db_transaction_util.begin()
if answer == QMessageBox.Yes:
changed_cost = Decimal(
table_utils.get_table_current_index_info(
self.tableView, 9))
changed_number = int(
table_utils.get_table_current_index_info(
self.tableView, 7))
if changed_type == StockDetail.by_decreased():
buy_service.decrease_buy_left(stock_id,
abs(changed_number))
else:
buy_service.increase_buy_left(stock_id,
abs(changed_number))
stock_handler.update_stock_balance(stock_id, changed_number,
changed_cost)
buy_handler.update_buy_state(review_id, BuyInfo.normal())
elif answer == QMessageBox.No:
buy_handler.update_buy_state(review_id, BuyInfo.rejected())
db_transaction_util.commit()
self._init_table()
except Exception as e:
db_transaction_util.rollback()
logger.error(e)
logger.error('traceback.format_exc():\n{}'.format(
traceback.format_exc()))
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 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 single_pay(self):
buy_id = table_utils.get_table_current_index_info(self.detail_table, 10)
if not buy_id:
QMessageBox.information(self.single_button, '提示', '请选择一条明细！')
return
buy_info = BuyInfo()
buy_info.buy_id(int(buy_id))
unpaid = Decimal(table_utils.get_table_current_index_info(self.detail_table, 9))
paid = Decimal(table_utils.get_table_current_index_info(self.detail_table, 8))
buy_info.paid(paid)
buy_info.unpaid(unpaid)
buy_info.note(self.supplier_name)
dialog = PayOffArrearsDialog(buy_info)
dialog.exec()
self._init_table()
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
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 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 _submit(self):
# stock = Stock()
#
# brand = self.brand_combo
# stock.brand_name(brand.currentText()).brand_id(brand.currentData())
#
# model = self.model_combo
# stock.model_name(model.currentText()).model_id(model.currentData())
#
# name = brand.currentText() + '-' + model.currentText()
# stock.name(name)
# # stock.unit(self.unit.text())
# stock.create_op(self.staffComb.currentData())
# stock.create_time(self.dateEdit.text())
#
# stock_id = stock_handler.add_stock_info(stock)
model = self.model_combo
stock_info = stock_handler.get_stock_by_model(model.currentData())
buy_info = BuyInfo()
buy_info.buy_date(self.dateEdit.text())
buy_info.stock_id(stock_info[0])
buy_info.supplier_id("99999")
buy_info.unit_price(Decimal(self.money.text()))
create_time = time_utils.get_now()
buy_info.create_time(create_time)
create_op = self.staffComb.currentData()
buy_info.create_op(create_op)
# buy_info.paid(abs(paid))
# buy_info.unpaid(abs(unpaid))
buy_info.total(Decimal(self.toal.text()))
buy_info.note(self.notes.text())
buy_info.buy_type("8")
buy_handler.add_buy_info(buy_info)
QMessageBox.information(self.addButton, '提示', '校准成功！')
self.close()
def add_buy_info(buy_info: BuyInfo) -> int:
sql_text = '''
INSERT INTO buy_info(
STOCK_ID,
SUPPLIER_ID,
UNIT_PRICE,
NUMBER,
BUY_DATE,
CREATE_TIME,
CREATE_OP,
UNPAID,
PAID,
TOTAL,
BUY_TYPE,
note
)
VALUES (
{},
{},
{:.2f},
{},
'{}',
'{}',
{},
{:.2f},
{:.2f},
{:.2f},
{},
'{}'
)''' \
.format(buy_info.stock_id(), buy_info.supplier_id(),
buy_info.unit_price(), buy_info.number(), buy_info.buy_date(), buy_info.create_time(),
buy_info.create_op(), buy_info.unpaid(), buy_info.paid(), buy_info.total(),
buy_info.buy_type(), buy_info.note())
new_buy_id = execute(sql_text)
return new_buy_id