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__())