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_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_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 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_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
def get_negative_on_hand():
sql_text = '''
SELECT
s.id,
s.sale_date,
si.brand_name,
si.model_name,
s.number,
si.balance,
si.unit,
si.brand_id,
si.model_id,
si.id,
si.first_service_name,
si.first_service_id,
si.second_service_name,
si.second_service_id,
'点击销负'
FROM stock_info si,
Sales s,
stock_detail sd
WHERE sd.changed_id = s.id
AND sd.type = {}
AND sd.stock_id = si.id
GROUP BY s.id,s.sale_date,si.brand_name, si.model_name, s.number, si.balance, si.unit''' \
.format(StockDetail.by_negative())
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 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 get_calibration_detail_by_buy_id(buy_id: int):
sql_text = '''
SELECT ID,
STOCK_ID,
changed_id,
changed_money,
changed_number,
TYPE,
UPDATE_TIME,
UPDATE_OP
FROM stock_detail
WHERE changed_id = {}
AND type in({},{})
order by id
''' \
.format(buy_id, StockDetail.by_increased(), StockDetail.by_decreased())
result = execute(sql_text, True)
return result
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_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 add_stock_detail(stock_detail: StockDetail):
sql_text = '''
INSERT INTO stock_detail(stock_id,changed_id,changed_money,changed_number,type,update_op,update_time)
VALUES(
{},
{},
{:.2f},
{},
{},
{},
'{}'
)'''.format(stock_detail.stock_id(), stock_detail.changed_id(),
stock_detail.changed_money(),
stock_detail.changed_number(), stock_detail.type(),
stock_detail.update_op(),
stock_detail.update_time())
result = execute(sql_text)
return result
def add_stock_detail(stock_id: int, changed_id: int, changed_money: float, change_number: int, change_type: int):
stock_detail = StockDetail()
stock_detail.changed_id(changed_id)
stock_detail.changed_money(abs(changed_money))
stock_detail.changed_number(abs(change_number))
stock_detail.stock_id(stock_id)
stock_detail.type(change_type)
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 _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 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__())
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)