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_in_store_count_by_buy_id(buy_id: int) -> int:
sql_text = '''
SELECT COUNT(1)
FROM stock_detail
WHERE changed_id = {}
AND type = {}''' \
.format(buy_id, StockDetail.by_bought())
result = execute(sql_text)
return result
def _add_stock_detail(stock_id, buy_id, total, number):
stock_detail = StockDetail()
stock_detail.changed_id(buy_id)
stock_detail.changed_money(abs(total))
stock_detail.changed_number(abs(number))
stock_detail.stock_id(stock_id)
if number < 0:
stock_detail.type(stock_detail.by_returned())
else:
stock_detail.type(StockDetail.by_bought())
stock_detail.update_time(time_utils.get_now()).update_op(Common.config.login_user_info[0])
stock_detail_handler.add_stock_detail(stock_detail)
def get_unsalable_warning():
sql_text = '''
SELECT
si.brand_name,
si.model_name,
sum(left_number) AS sum,
replace(max(julianday(date('now'))-julianday(bi.buy_date)),'.','_')/1 as longest_days
FROM buy_info bi,
stock_info si
WHERE bi.stock_id=si.id
AND bi.left_number > 0
AND julianday(date('now'))-julianday(bi.buy_date) >= 90
GROUP BY si.model_id''' \
.format(StockDetail.by_bought())
result = execute(sql_text)
return result
def do_add(self):
succeeded_list = '成功录入的行数：\n'
failed_dict = OrderedDict()
for row_index in range(1, self.line_number + 1):
# 如果没有这个对应的序号属性，则说明已经删除，继续处理下一条记录
if not hasattr(self, 'seq_' + str(row_index)):
print(row_index)
continue
line_number = getattr(self, 'seq_' + str(row_index)).text()
msg = self._check_required(line_number)
# 如果必填检查不通过，则将错误信息记录下来，继续处理下一条记录
if msg:
failed_dict[line_number] = msg
continue
# 提取所有表单项的值
buy_date = getattr(self, 'buy_date_' + line_number).date().toString('yyyy-MM-dd')
brand = getattr(self, 'brand_' + line_number).text()
model = getattr(self, 'model_' + line_number).text()
supplier = getattr(self, 'supplier_' + line_number).text()
unit = getattr(self, 'unit_' + line_number).text()
price = Decimal(getattr(self, 'price_' + line_number).text())
number = getattr(self, 'number_' + line_number).value()
total = Decimal(getattr(self, 'total_' + line_number).text())
paid_value = getattr(self, 'paid_' + line_number).text()
paid = Decimal(paid_value if paid_value else '0.0')
unpaid = Decimal(getattr(self, 'unpaid_' + line_number).text())
note = getattr(self, 'note_' + line_number).text()
payment = int(getattr(self, 'payment_' + line_number).currentData())
try:
db_transaction_util.begin()
brand_id = brand_and_model_service.get_brand_by_name(brand)
model_id = brand_and_model_service.get_model_by_name(brand_id, model)
supplier_id = supplier_service.get_supplier_by_name(supplier)
stock_info = stock_service.get_stock_by_model(model_id)
# 如果通过型号找到库存，则单位属性沿用库存中的单位，并更新库存总额和库存量
if stock_info:
stock_id = stock_info[0]
else:
# 新增库存信息
second_service = getattr(self, 'second_service_' + line_number)
second_service_id = int(second_service.currentData())
stock_info = stock_service.add_stock_info(model, brand, model_id, brand_id, unit, second_service_id)
stock_id = stock_info.id()
stock_service.update_stock_info(stock_id, number, total)
# 新增进货信息
buy_id = buy_service.add_buy_info(stock_id, supplier_id, price, number, buy_date, unpaid, paid, total,
payment, note, number)
change_type = StockDetail.by_bought()
# 如果是退货，更新原来的进货信息中剩余量
if number < 0:
buy_service.decrease_buy_left(stock_id, number)
buy_service.update_buy_unpaid(stock_id, total, supplier_id, payment)
change_type = StockDetail.by_returned()
# 新增库存明细
stock_service.add_stock_detail(stock_id, buy_id, total, number, change_type)
# 新增供应商支付信息，如果数量大于0，则为进货，小于零则为退货
supplier_service.add_supplier_payment_detail(buy_id, supplier_id, paid, unpaid, payment, number < 0)
db_transaction_util.commit()
succeeded_list = succeeded_list + '第' + line_number + '行、'
self.do_remove(line_number)
except Exception as e:
logger.error(e.__str__())
logger.error('traceback.format_exc():\n{}'.format(traceback.format_exc()))
failed_dict[line_number] = e.__str__()
db_transaction_util.rollback()
failed_info = '\n未成功录入的行数：\n'
for key in list(failed_dict.keys()):
failed_info += '第' + key + '行数据：' + failed_dict[key]
succeeded_list += '\n' + failed_info
QMessageBox.information(self.submit, '提示', succeeded_list)
def do_write_off(self):
if not self.sale_id:
QMessageBox.information(self.writeOffButton, "提示", '请选择需要销负的记录！')
return
msg = self._check_required()
if msg:
QMessageBox.information(self.writeOffButton, "提示", msg)
return
brand_name = self.brand.text()
model_name = self.model.text()
buy_number = self.buy_number.value()
price = Decimal(self.price.text())
paid = self.paid.text()
if paid:
paid = Decimal(paid)
else:
paid = Decimal(0.0)
unpaid = Decimal(self.unpaid.text())
total = Decimal(self.total.text())
note = self.note.text()
payment = int(self.payment.currentData())
buy_date = self.buy_date.date().toString('yyyy-MM-dd')
# 计算剩余量
if self.balance < 0:
left_number = buy_number + self.balance
else:
left_number = buy_number
try:
db_transaction_util.begin()
# 更新库存中的商品信息
brand_id = brand_and_model_service.get_brand_by_name(brand_name)
model_id = brand_and_model_service.get_model_by_name(
brand_id, model_name)
stock_handler.update_brand_name(self.stock_id, brand_name)
stock_handler.update_brand_id(self.stock_id, brand_id)
stock_handler.update_model_name(self.stock_id, model_name)
stock_handler.update_model_id(self.stock_id, model_id)
# 更新库存
stock_service.update_stock_info(self.stock_id, left_number,
Decimal(left_number) * price)
# 新增进货信息
supplier_id = supplier_service.get_supplier_by_name(
self.supplier.text())
buy_id = buy_service.add_buy_info(self.stock_id, supplier_id,
price, buy_number, buy_date,
unpaid, paid, total, payment,
note, left_number)
# 新增进货库存明细
stock_service.add_stock_detail(self.stock_id, buy_id, total,
buy_number, StockDetail.by_bought())
# 更新销售库存明细状态
stock_detail_handler.update_negative_info(self.sale_id, total)
# 更新供应商付款信息
supplier_service.add_supplier_payment_detail(
buy_id, supplier_id, paid, unpaid, payment)
db_transaction_util.commit()
QMessageBox.information(self.writeOffButton, "提示", '销负成功！')
self._clear_detail()
self._init_write_off_table()
except Exception as e:
logger.error(e.__str__())
logger.error('traceback.format_exc():\n{}'.format(
traceback.format_exc()))
db_transaction_util.rollback()
QMessageBox.information(self.writeOffButton, "提示",
'销负失败，请重试！\n' + e.__str__())