def __init__(self): super(BuyInfoMonitor, self).__init__() self.setupUi(self) self.setWindowTitle('进货监控') self.summary_table_title = ('一级项目ID', '二级项目ID', '一级分类', '二级分类', '进货数量', '进货金额') self.detail_table_title = ("ID", '进货日期', '商品品牌', '商品型号', '进货数量', '单位', '进货单价', '单品小计', '供应商ID', '供应商', '所属项目', '付款金额', '未付金额', '进货类型', '库存ID') self.end_date.setDateTime(QDateTime.fromString(time_utils.get_now(), 'yyyy-MM-dd hh:mm:ss')) self.end_date.setDateTime(QDateTime.fromString(time_utils.get_now(), 'yyyy-MM-dd hh:mm:ss')) self.start_time_str = self.start_date.date().toString('yyyyMMdd') self.end_time_str = self.end_date.date().toString('yyyyMMdd') self._init_signal_and_slot()
def export_services(): now = get_now() file_name = now + ".xls" title_list = ["一级菜单", "二级菜单"] all_table_len = len(title_list) # 整理参数 services = get_all_second_level_service() if services: wb = xlwt.Workbook() ws = wb.add_sheet('菜单列表', cell_overwrite_ok=True) for i in range(all_table_len): # 设置单元格宽度 ws.col(i).width = 265 * 20 # 插入标题 ws.write(0, i, title_list[i], set_style('Arial', 250, True, True)) row = 1 for service in services: # 插入信息 ws.write(row, 0, service[1], set_style('SimSun', 180, True, True, True)) ws.write(row, 1, service[3], set_style('SimSun', 180, True, True, True)) row += 1 if not os.path.exists(menuSavePath): os.mkdir(menuSavePath) wb.save(menuSavePath + file_name) return file_name else: return False
def _add_supplier_payment_detail(buy_id, supplier_id, paid, unpaid, payment_method, is_return=False): payment = Payment() payment.buy_id(buy_id) payment.supplier_id(supplier_id) payment.payment_method(payment_method) payment.paid(paid) payment.unpaid(unpaid) payment.create_op(Common.config.login_user_info[0]) payment.create_time(time_utils.get_now()) if is_return: payment.refund_type(Payment.returned()) else: payment.refund_type(Payment.payoff()) payment_handler.add_payment_detail(payment) if unpaid: supplier_handler.update_supplier_unpaid(supplier_id, unpaid)
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 show_detail(self, index: QModelIndex): if index.column() != 14: return self.sale_id = table_utils.get_table_cell(self.write_off_table, index.row(), 0) self.stock_id = table_utils.get_table_current_index_info( self.write_off_table, 9) self.brand_id = table_utils.get_table_current_index_info( self.write_off_table, 7) self.model_id = table_utils.get_table_current_index_info( self.write_off_table, 8) self.buy_date.setDateTime( QDateTime.fromString(time_utils.get_now(), 'yyyy-MM-dd hh:mm:ss')) self.brand.setText( table_utils.get_table_current_index_info(self.write_off_table, 2)) self.model.setText( table_utils.get_table_current_index_info(self.write_off_table, 3)) self.sale_number.setText( table_utils.get_table_current_index_info(self.write_off_table, 4)) balance = int( table_utils.get_table_current_index_info(self.write_off_table, 5)) self.buy_number.setValue(abs(balance)) self.unit.setText( table_utils.get_table_current_index_info(self.write_off_table, 6)) self.service.setText( table_utils.get_table_current_index_info(self.write_off_table, 10) + '-' + table_utils.get_table_current_index_info(self.write_off_table, 12)) self.price.setText('0.0') self.total.setText('0.0') self.paid.setText('0.0') self.unpaid.setText('0.0') self.balance = balance
def add_payment_detail(payment): sql_text = ''' INSERT INTO PAYMENT_DETAIL( BUY_ID, PAYMENT_METHOD, PAID, UNPAID, CREATE_TIME, CREATE_OP, refund_type, supplier_id, note ) VALUES ( {}, {}, {:.2f}, {:.2f}, '{}', {}, {}, {}, '{}' )''' \ .format(payment.buy_id(), payment.payment_method(), payment.paid(), payment.unpaid(), get_now(), Common.config.login_user_info[0], payment.refund_type(), payment.supplier_id(), payment.note()) result = execute(sql_text) return result
def add_second_level_service(service_name, father_id): time_now = get_now() sql_text = '''INSERT INTO service(name, createdTime, father, level) VALUES('{}','{}',{},{})''' \ .format(service_name, time_now, father_id, 2) return execute(sql_text)
def _submit(self): stock = Stock() brand = self.brand_comboBox stock.brand_name(brand.currentText()).brand_id(brand.currentData()) model = self.model_combo_box stock.model_name(model.currentText()).model_id(model.currentData()) first_service = self.first_service_combo_box stock.first_service_id(first_service.currentData()).first_service_name( first_service.currentText()) second_service = self.second_service_combo_box stock.second_service_name( second_service.currentText()).second_service_id( second_service.currentData()) name = brand.currentText() + '-' + model.currentText() stock.name(name) stock.unit(self.unit.text()) stock.create_op(Common.config.login_user_info[0]) stock.create_time(get_now()) stock_id = stock_handler.add_stock_info(stock) stock.id(stock_id)
def __init__(self): super(stock_calibrationDialog, self).__init__() self.setupUi(self) self._init_brand() self._init_staffComb() self.setWindowTitle('请填写校准信息') time_now = time_utils.get_now() self.dateEdit.setDateTime(QDateTime.fromString(time_now, 'yyyy-MM-dd hh:mm:ss')) self.addButton.clicked.connect(self._submit)
def add_all_required_attribute(service_id): required_attributes = attribute_handler.get_all_attributes() item = ServiceItem() item.create_time(time_utils.get_now()) item.create_op(config.login_user_info[0]) for attr in required_attributes: item.attribute_name(attr[1]) item.attribute_id(attr[0]) service_handler.add_service_attribute(item)
def _init_ui(self): table_utils.set_table_content(self.tableView, (), self.table_title) time_now = time_utils.get_now() self.start_date.setDateTime( QDateTime.fromString(time_now, 'yyyy-MM-dd hh:mm:ss')) self.end_date.setDateTime( QDateTime.fromString(time_now, 'yyyy-MM-dd hh:mm:ss')) self._init_brand_and_model() self._init_first_srv()
def add_model(model_name: str, brand_id): sql_text = '''INSERT INTO model( model_name, brand_id, create_time, create_op) VALUES('{}',{},'{}',{})''' \ .format(model_name, brand_id, get_now(), Common.config.login_user_info[0]) result = execute(sql_text) return result
def get_return_visit_info(): now = get_now() time_str = "/".join(now.split(' ')[0].split("-")) time_str = format_time(time_str, True) sql_text = '''SELECT callbackTime,phone,username,carId,id FROM CallBack WHERE state='0' AND callbackTime <= '{}' ORDER BY createdTime DESC ''' \ .format(time_str) data = execute(sql_text) return data
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 add_supplier(supplier_name: str): sql_text = '''INSERT INTO supplier( supplier_name, create_time, create_op) VALUES( '{}', '{}', {})''' \ .format(supplier_name, get_now(), Common.config.login_user_info[0]) result = execute(sql_text) return result
def check_device(self): device = Device() device.ip(self.spbill_create_ip) device.create_time(time_utils.get_now()) device.name(self.deviceName) data = device_handler.get_device_info_by_ip(self.spbill_create_ip) if not data: Common.config.ui.pad_connect_signal.emit(device) check_result = 2 else: check_result = (data[1] == Device.enable()) return check_result
def _init_first_line(self): self.line_number = 1 self.buy_date_1.setDateTime(QDateTime.fromString(time_utils.get_now(), 'yyyy-MM-dd hh:mm:ss')) self._add_all_service_item(self.service_1) Payment.add_all_payment(self.payment_1) for title in self.line_title: name = title + '_' + '1' editor = getattr(self, name) self._set_completer(editor, title) pyqt_utils.set_validator(self.price_1, 'float') pyqt_utils.set_validator(self.paid_1, 'float') self.paid_1.setText('0.0') self.price_1.setText('0.0') self.total_1.setText('0.0') self.unpaid_1.setText('0.0')
def _clear_first_line(self): line_number = '1' for title in self.line_title: name = title + '_' + line_number editor = getattr(self, name) if title == 'buy_date': editor.setDateTime(QDateTime.fromString(time_utils.get_now(), 'yyyy-MM-dd hh:mm:ss')) continue if title == 'number': editor.setValue(0) continue if title in ('seq', 'remove', 'payment', 'service'): continue editor.clear() QMessageBox.information(self.submit, "提示", '首行数据已经被重执!')
def add_brand(brand_name: str): sql_text = '''INSERT INTO brand( BRAND_NAME, create_time, create_op ) VALUES( '{}', '{}', {} )''' \ .format(brand_name, get_now(), Common.config.login_user_info[0]) result = execute(sql_text) return result
def _add_payment_detail(self, paid: float, unpaid: float, ): payment = Payment() payment.buy_id(self.buy_id) payment_method = int(self.payment_method.currentData()) payment.payment_method(payment_method) payment.paid(paid) payment.unpaid(unpaid) payment.create_op(Common.config.login_user_info[0]) payment.create_time(time_utils.get_now()) payment.refund_type(Payment.payoff()) payment_handler.add_payment_detail(payment)
def do_add(self): attr_name, ok = QInputDialog.getText(self.add, '新增属性', '请输入属性名') if attr_name and ok: exists_info = attribute_handler.get_count_by_name(attr_name) if exists_info: if not exists_info[1]: QMessageBox.information(self.add, '提示', '该属性已经存在,请重新输入') return else: attribute_handler.undo_delete_attribute_logical( exists_info[2]) else: attribute = Attribute() attribute.create_op(Common.config.login_user_info[0]) attribute.name(attr_name) attribute.create_time(time_utils.get_now()) attribute_handler.add_attribute(attribute) QMessageBox.information(self.add, '提示', '新增成功') self._init_table()
def _update(self): name = self.service_name.text() if '-' in name: QtWidgets.QMessageBox.information(self.submit, "提示", "名字输入有误,请勿添加\"-\"符号") elif name == "": QtWidgets.QMessageBox.information(self.submit, "提示", "请输入名称") else: try: db_transaction_util.begin() if self.service_id: if self.name != name: service_handler.update_service(self.service_id, name) stock_handler.update_second_service_name( self.service_id, name) else: self.service_id = service_handler.add_second_level_service( name, father_id=self.father_id) for k, v in self.checkbox_dict.items(): if v.checkState() == Qt.Checked: if k not in self.checked_dict: attr_item = ServiceItem() attr_item.service_id(self.service_id).attribute_id( k).attribute_name(v.text()) attr_item.create_time( time_utils.get_now()).create_op( config.login_user_info[0]) service_handler.add_service_attribute(attr_item) else: if k in self.checked_dict: service_handler.delete_service_attribute( self.service_id, k) db_transaction_util.commit() QtWidgets.QMessageBox.information(self.submit, "提示", "提交成功") self.close() except Exception as db_excption: print(db_excption) db_transaction_util.rollback() QtWidgets.QMessageBox.information(self.submit, "提示", "服务项目更新失败,请重试")
def _clear_detail(self): self.buy_date.setDateTime( QDateTime.fromString(time_utils.get_now(), 'yyyy-MM-dd hh:mm:ss')) self.brand.clear() self.model.clear() self.sale_number.clear() self.buy_number.setValue( abs( int( table_utils.get_table_current_index_info( self.write_off_table, 5)))) self.unit.clear() self.service.clear() self.price.setText('0.0') self.total.setText('0.0') self.paid.setText('0.0') self.unpaid.setText('0.0') self.sale_id = 0 self.stock_id = 0 self.brand_id = 0 self.model_id = 0 self.balance = 0
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_stock_info(self, model, brand, model_id, brand_id, line_number, total, number, unit): service_id = getattr(self, 'service_' + line_number).currentData() service_name = getattr(self, 'service_' + line_number).currentText() stock_info = Stock() services = service_name.split('-') service_ids = service_id.split('-') first_service_id = int(service_ids[0]) first_service_name = services[0] second_service_id = int(service_ids[1]) second_service_name = services[1] stock_info.model_id(model_id).model_name(model) stock_info.brand_id(brand_id).brand_name(brand) stock_info.first_service_id(first_service_id).first_service_name(first_service_name) stock_info.second_service_id(second_service_id).second_service_name(second_service_name) stock_info.unit(unit).name(brand + '-' + model) stock_info.create_op(config.login_user_info[0]).create_time(time_utils.get_now()) stock_info.total_cost(total).balance(number) stock_id = stock_handler.add_stock_info(stock_info) return stock_id
def user(self, keyword, get_data): try: if self.request.method == 'POST': if keyword == "add": try: username = get_data.pop("username") phone = get_data.pop("carPhone") car_model = get_data.pop("carModel") car_id = get_data.pop("carId") except Exception as e: print(e) raise ApiException(ErrorCode.ParameterMiss) customer = Customer() customer.username(username) customer.car_model(car_model) customer.phone(phone) customer.car_id(car_id) customer.create_time(time_utils.get_now()) temp_user = customer_handler.get_customer_by_key( "carId", car_id) if temp_user: try: customer_handler.update_customer_by_car_id( customer) except Exception as update_exception: print(update_exception) raise ApiException(ErrorCode.ParameterError) customer_id = temp_user[0][0] else: try: customer_id = customer_handler.add_customer( customer) except Exception as insert_exception: print(insert_exception) raise ApiException(ErrorCode.UserMore) return set_return_dicts({"userId": customer_id}) else: raise ApiException(ErrorCode.ErrorRequest) elif self.request.method == "GET": if not self.storeId: raise ApiException(ErrorCode.PCError) if keyword == "find": key = get_data.get("key", "") if key not in ["carPhone", "carId"]: raise ApiException(ErrorCode.ParameterError) value = get_data.get("value", "") if self.connect: temp = SocketServer("user {} {} {}".format( self.storeId, key, value)) if not temp: temp = self.find_customer(key, value) else: temp = self.find_customer(key, value) result = [] key_temp = [] if temp == 'restart': raise ApiException(ErrorCode.ReStartPC) else: for data in temp: key = data.get("phone") + data.get( "carId") + data.get("carModel") + data.get( "userName") if key in key_temp: pass else: result.append(data) key_temp.append(key) return set_return_dicts(result) elif keyword == 'order': car_id = get_data.get("carId", "") phone = get_data.get("carPhone", "") if not car_id: raise ApiException(ErrorCode.ParameterMiss) if self.connect: all_order_money = 0.0 result = SocketServer("userorder {} {} {}".format( self.storeId, car_id, phone)) if result: order_number = len(result) for data in result: all_order_money += data.get("totalPrice") else: result, order_number, all_order_money = self.get_order( car_id) else: result, order_number, all_order_money = self.get_order( car_id) if result == 'restart': raise ApiException(ErrorCode.ReStartPC) # result = [] else: print('sort') try: result.sort(key=lambda obj: obj.get('createdTime'), reverse=True) except Exception as sortE: print(sortE) try: for data in result: print(data) msg = data.get("msg") for msg_data in msg: temp = {} attribute = msg_data.get("attribute") for k, v in attribute.items(): if v != "" and v != "-": temp[k] = v msg_data['attribute'] = temp except Exception as forException: print(forException) send_msg = { 'orderMsg': result, 'orderNumber': order_number, 'allOrderMoney': all_order_money } print(send_msg) return set_return_dicts(send_msg) else: raise ApiException(ErrorCode.ErrorRequest) except ApiException as e: return set_return_dicts(forWorker=e.error_result['forWorker'], code=e.error_result['errorCode'], forUser=e.error_result['forUser'])
def create_user(): execute('''CREATE TABLE Admin (id INTEGER PRIMARY KEY AUTOINCREMENT, userName VARCHAR(20) NOT NULL, pwd VARCHAR (32) NOT NULL , createdTime DATETIME NOT NULL );''') execute(''' CREATE UNIQUE INDEX userName ON Admin (userName); ''') # 写入初始管理员账号admin execute("INSERT INTO Admin (userName,pwd,createdTime) " \ "VALUES ('{}','{}','{}')".format('admin', 'e93a9a6047903bd088bd4ffee28fdee8', time_utils.get_now()))
def export_sale_detail(self, start_time, end_time, remote=False): now = get_now() file_name = now + ".xls" # 获取消费信息 sale_detail_list = [] if remote: # 获取远程信息 root = 'config.ini' basic_msg = configparser.ConfigParser() basic_msg.read(root) code = basic_msg.get('msg', 'code') keyword = "xiaofei {} {} {}".format(code, start_time, end_time) # python3传递的是bytes,所以要编码 try: sale_detail_list = SocketServer(keyword) except Exception as e: print(e) else: sale_detail_list = self.database_handler.GetXiaoFeiTable(start_time, end_time, Table=False) # 填入数据 if sale_detail_list: # 插入信息 sale_detail_list.sort(key=lambda obj: obj[2], reverse=True) order_check_id = None merge_list = list() temp_msg = dict() temp = list() # 设置表头 title_list = ["检索ID", '订单号', u'消费时间', "消费门店", u"车牌号", u"车主姓名", u"联系电话", u"车型", u"操作人员", u"消费项目"] table_len = len(title_list) header = ['数量', '单价', '小计', '总价', '单位', '备注'] for data in sale_detail_list: try: attribute = json.loads(data[10]) for k, v in attribute.items(): if k not in header: header.append(k) except Exception as e: print(e) continue title_list = title_list + header all_table_len = len(title_list) wb = xlwt.Workbook() ws = wb.add_sheet('消费列表', cell_overwrite_ok=True) title = set_style('Arial', 250, True, True) default = set_style('SimSun', 180, True, True, True) top = set_style('Times New Roman', 350, True, True) # 前两个参数表示需要合并的行范围,后两个参数表示需要合并的列范围 # 合并单元格作为大标题,水平居中即可 start_time = format_time(start_time)[:10] end_time = format_time(end_time)[:10] ws.write_merge(0, 0, 0, all_table_len - 1, '门店系统:{}至{}'.format(start_time, end_time), top) # 设置标题 for i in range(all_table_len): # 设置单元格宽度 ws.col(i).width = 265 * 20 # 插入标题 ws.write(1, i, title_list[i], title) # 从第二行开始插 row = 2 for data in sale_detail_list: if order_check_id: # 如果记录的订单号与当前数据的订单号不同,则进行录入并修改记录订单号 if order_check_id != data[0]: order_check_id = data[0] # 因为订单号变了所以之前的缓存清空,换成这个订单号的索引 temp = [row] else: # 若已经缓存了2个索引则代表此订单号有>2个商品,所以更新第二个索引保留第一个索引 if len(temp) >= 2: temp[1] = row else: temp.append(row) else: # 若第一次进来,此时订单号是None,进行录入 order_check_id = data[0] temp.append(row) # 插入信息 for j in range(table_len): ws.write(row, j, data[j], default) if j == table_len - 1: # 最后一个的时候遍历填入数据 try: j += 1 attribute = json.loads(data[j]) for k in header: ws.write(row, j, attribute.get(k, ""), default) j += 1 except Exception as e: print(e) continue row += 1 # 如果已经缓存了2个数字,则代表有重复的订单号,所以此时进行记录并合并 if len(temp) >= 2: temp_msg[temp[0]] = MakeTempMsg(data) merge_list.append(temp) if len(temp) >= 2: merge_list.append(temp) temp_msg[temp[0]] = MakeTempMsg(data) for hb in merge_list: ws.write_merge(hb[0], hb[1], 0, 0, temp_msg[hb[0]].get("checkOrderId"), default) ws.write_merge(hb[0], hb[1], 1, 1, temp_msg[hb[0]].get("orderNo"), default) ws.write_merge(hb[0], hb[1], 2, 2, temp_msg[hb[0]].get("createdTime"), default) ws.write_merge(hb[0], hb[1], 3, 3, temp_msg[hb[0]].get("pcSign"), default) ws.write_merge(hb[0], hb[1], 4, 4, temp_msg[hb[0]].get("carId"), default) ws.write_merge(hb[0], hb[1], 5, 5, temp_msg[hb[0]].get("carUser"), default) ws.write_merge(hb[0], hb[1], 6, 6, temp_msg[hb[0]].get("carPhone"), default) ws.write_merge(hb[0], hb[1], 7, 7, temp_msg[hb[0]].get("carModel"), default) ws.write_merge(hb[0], hb[1], 8, 8, temp_msg[hb[0]].get("workerName"), default) if not os.path.exists(savePath): os.mkdir(savePath) wb.save(savePath + file_name) return file_name else: return False
def import_sale_detail(self, file_name, widget): bk = xlrd.open_workbook(file_name) try: sh = bk.sheet_by_name("消费列表") except Exception as e: print(e) sh = bk.sheet_by_name("Sheet1") rows = sh.nrows temp = list() title_list = ['检索ID', 'orderNo', 'createdTime', "pcSign", "carId", "carUser", "carPhone", "carModel", "workerName", "project"] user_list = ["carId", "carUser", "carPhone", "carModel"] must_len = len(title_list) title = sh.row_values(1) match_result = self._is_from_export(str(sh.row_values(0)[0])) progress_dialog = QProgressDialog(widget) progress_dialog.setWindowTitle("导入中") from PyQt5.QtCore import Qt progress_dialog.setWindowModality(Qt.WindowModal) progress_dialog.setMinimumDuration(4) progress_dialog.setWindowTitle(widget.tr("请等待")) progress_dialog.setLabelText(widget.tr("导入中...")) progress_dialog.setCancelButtonText(widget.tr("取消")) progress_dialog.setRange(0, rows - 3) progress_dialog.show() if True: p = 0 msg_list = list() for i in range(2, rows): # 用正则表达式判断第一行数据内容,从而判断是否用软件导出的EXCEL文档 if match_result: # 若是用软件导出的则 if progress_dialog.wasCanceled(): break progress_dialog.setValue(p) p += 1 try: # if True: save_data = dict() row_data = sh.row_values(i) if i < rows - 1: temp2 = sh.row_values(i + 1) else: temp2 = None if not temp2 and temp2[0] == '': # 合并了单元格则合并内容是空,将后面不是空的内容进行缓存,合并的内容会在最后一条信息中显示, # 此时一并进行录入 temp.append(row_data) else: # if row_data[0] != '': if temp: order_check_id = temp[0][0] else: order_check_id = row_data[0] check_order = get_sale_info_by_one_key("orderCheckId", order_check_id) # 有此订单的就不保存了 if not check_order: if temp: temp.append(row_data) all_msg = temp[0] # 接入信息后录入 for j in range(len(temp)): if j != 0: msg = temp[j] attribute = {} for ki in range(len(title)): all_msg[ki] = str(all_msg[ki]) msg[ki] = str(msg[ki]) if ki < must_len: temp_key = title_list[ki] if temp_key in ['orderNo', 'carPhone']: all_msg[ki] = all_msg[ki].replace('.0', "") msg[ki] = msg[ki].replace('.0', "") if title_list[ki] in ["project"]: save_data[temp_key] = msg[ki] else: if temp_key == "检索ID": temp_key = "orderCheckId" save_data[temp_key] = all_msg[ki] else: if row_data[ki] == "" or row_data[ki] == "-": continue attribute[title[ki]] = msg[ki] save_data['attribute'] = json.dumps(attribute) save_data['id'] = get_order_id() sale_handler.add_sale_info(save_data) row_data = all_msg attribute = {} user_save = {} for ki in range(len(title)): row_data[ki] = str(row_data[ki]) if ki < must_len: if title_list[ki] in ['orderNo', 'carPhone']: row_data[ki] = row_data[ki].replace('.0', "") key = title_list[ki] if key == "检索ID": key = "orderCheckId" save_data[key] = row_data[ki] # 保存用户信息 if key in user_list: user_save[key] = row_data[ki] else: if row_data[ki] == "" or row_data[ki] == "-": continue attribute[title[ki]] = row_data[ki] user = check_customer(user_save.get("carPhone"), user_save.get("carId")) if not user: # 没有此用户则添加 key = "userName,carPhone,carModel,carId,createdTime" value = "'{}','{}','{}','{}','{}'".format(user_save.get("carUser"), user_save.get("carPhone"), user_save.get("carModel"), user_save.get("carId"), get_now()) try: self.database_handler.InsertData("User", key, value) except Exception as e: print(e) pass save_data['attribute'] = json.dumps(attribute) save_data['id'] = get_order_id() sale_handler.add_sale_info(save_data) # 清空缓存 temp = list() except Exception as e: print(e) continue else: # 若不是用软件导出的EXCEL文档则 # 先整理参数,全部变成列表,列表里面是字典,字典的key就是title try: row_data = sh.row_values(i) temp_data = dict() for k in range(len(title)): temp_data[title[k]] = row_data[k] msg_list.append(temp_data) except Exception as e: print(e) continue if not match_result: save_list = defaultdict(list) for msg in msg_list: if not msg.get("消费时间") or not msg.get("车牌号"): continue key = msg.get("消费时间") + msg.get("车牌号") save_list[key].append(msg) # 插入信息 must = ["订单号", "接待门店", "车牌号", "车主姓名", "联系电话", "车型", "操作人员", "消费项目", "消费时间"] for k, v in save_list.items(): if progress_dialog.wasCanceled(): break progress_dialog.setValue(p) p += 1 order_check_id = get_order_id() # 对同一个订单进行录入 user_save = {} for tempDict in v: order_no = str(tempDict.pop("订单号")) if tempDict.get("订单号", "") != "" else "-" pc_sign = tempDict.pop("接待门店", "") if tempDict.get("接待门店", "") != "" else "-" car_id = tempDict.pop("车牌号") if tempDict.get("车牌号") != "" else "-" car_user = tempDict.pop("车主姓名", "") if tempDict.get("车主姓名", "") != "" else "-" car_phone = str(tempDict.pop("联系电话", "-")).replace(".0", "") if tempDict.get("联系电话", "") != "" else "-" car_model = tempDict.pop("车型", "") if tempDict.get("车型", "") != "" else "-" worker_name = tempDict.pop("操作人员", "") if tempDict.get("操作人员", "") != "" else "-" project = tempDict.pop("消费项目", "") if tempDict.get("消费项目", "") != "" else "-" created_time = str(tempDict.pop("消费时间")).replace(".", "-") # 保存用户信息 user_save["carId"] = car_id if car_id != '-' else "" user_save["carUser"] = car_user if car_user != '-' else "" user_save["carPhone"] = (car_phone if car_phone != '-' else "").replace(".0", "") user_save['carModel'] = car_model if car_model != '-' else "" if order_no != "-": check_order = get_sale_info_by_one_key("orderNo", order_no) if check_order: break save_data = { "orderNo": order_no.replace(".0", ""), "createdTime": created_time, "pcSign": pc_sign, "carId": car_id, "carUser": car_user, "carPhone": car_phone, "carModel": car_model, "workerName": worker_name, "project": project, "orderCheckId": order_check_id, "id": get_order_id(), } temp_attribute = tempDict attribute = dict() for key, value in temp_attribute.items(): if k not in must: if value == "": continue attribute[key] = str(value) try: gsf = float(attribute.get("工时费")) if attribute.get("工时费") != "" else 0 sl = float(attribute.get("数量")) if attribute.get("数量") != "" else 0 dj = float(attribute.get("单价")) if attribute.get("单价") != "" else 0 attribute["总价"] = gsf + sl * dj except Exception as e: print(e) pass save_data["attribute"] = json.dumps(attribute) sale_handler.add_sale_info(save_data) if user_save.get("carId") and user_save.get("carPhone"): # 当有用户信息的时候判断是否需要自动添加 user = check_customer(user_save.get("carPhone"), user_save.get("carId")) if not user: # 没有此用户则添加 key = "userName,carPhone,carModel,carId,createdTime" value = "'{}','{}','{}','{}','{}'".format(user_save.get("carUser"), user_save.get("carPhone"), user_save.get("carModel"), user_save.get("carId"), get_now()) try: self.database_handler.InsertData("User", key, value) except Exception as e: print(e) pass # 最后全部导入 progress_dialog.setValue(rows - 3) progress_dialog.close()
def ImportExcel(fileName, self): # 用于正则判断是否是用软件导出的excel文档 pattern = re.compile( r"^门店系统:\d{4}[-/]\d{2}[/-]\d{2}至\d{4}[-/]\d{2}[/-]\d{2}$") # matchs = pattern.match() bk = xlrd.open_workbook(fileName) try: sh = bk.sheet_by_name("消费列表") except: sh = bk.sheet_by_name("Sheet1") nrows = sh.nrows temp = list() titleList = [ '检索ID', 'orderNo', 'createdTime', "pcSign", "carId", "carUser", "carPhone", "carModel", "workerName", "project" ] userList = ["carId", "carUser", "carPhone", "carModel"] mustlen = len(titleList) check = str(sh.row_values(0)[0]) matchs = pattern.match(check) title = sh.row_values(1) progressDialog = QProgressDialog(self) progressDialog.setWindowTitle("导入中") # progdialog.setWindowModality(Qt.ApplicationModal) progressDialog.setWindowModality(Qt.WindowModal) progressDialog.setMinimumDuration(4) progressDialog.setWindowTitle(self.tr("请等待")) progressDialog.setLabelText(self.tr("导入中...")) progressDialog.setCancelButtonText(self.tr("取消")) progressDialog.setRange(0, nrows - 3) progressDialog.show() if True: p = 0 msgList = list() for i in range(2, nrows): # 用正则表达式判断第一行数据内容,从而判断是否用软件导出的EXCEL文档 if matchs: # 若是用软件导出的则 if progressDialog.wasCanceled(): break progressDialog.setValue(p) p += 1 try: # if True: saveData = dict() row_data = sh.row_values(i) if i < nrows - 1: temp2 = sh.row_values(i + 1) else: temp2 = None if temp2 != None and temp2[0] == '': # 合并了单元格则合并内容是空,将后面不是空的内容进行缓存,合并的内容会在最后一条信息中显示, # 此时一并进行录入 temp.append(row_data) else: # if row_data[0] != '': if temp: orderCheckId = temp[0][0] else: orderCheckId = row_data[0] checkOrder = get_sale_info_by_one_key( "orderCheckId", orderCheckId) # 有此订单的就不保存了 if not checkOrder: if temp: temp.append(row_data) allMsg = temp[0] # 接入信息后录入 for i in range(len(temp)): if i != 0: msg = temp[i] attribute = {} for ki in range(len(title)): allMsg[ki] = str(allMsg[ki]) msg[ki] = str(msg[ki]) if ki < mustlen: tempK = titleList[ki] if tempK in [ 'orderNo', 'carPhone' ]: allMsg[ki] = allMsg[ ki].replace('.0', "") msg[ki] = msg[ki].replace( '.0', "") if titleList[ki] in [ "project" ]: saveData[tempK] = msg[ki] else: if tempK == "检索ID": tempK = "orderCheckId" saveData[tempK] = allMsg[ ki] else: if row_data[ ki] == "" or row_data[ ki] == "-": continue attribute[title[ki]] = msg[ki] saveData['attribute'] = json.dumps( attribute) saveData['id'] = get_order_id() dbhelp.add_sale_info(saveData) row_data = allMsg attribute = {} userSave = {} for ki in range(len(title)): row_data[ki] = str(row_data[ki]) if ki < mustlen: if titleList[ki] in [ 'orderNo', 'carPhone' ]: row_data[ki] = row_data[ki].replace( '.0', "") key = titleList[ki] if key == "检索ID": key = "orderCheckId" saveData[key] = row_data[ki] # 保存用户信息 if key in userList: userSave[key] = row_data[ki] else: if row_data[ki] == "" or row_data[ ki] == "-": continue attribute[title[ki]] = row_data[ki] user = check_customer(userSave.get("carPhone"), userSave.get("carId")) if not user: # 没有此用户则添加 key = "userName,carPhone,carModel,carId,createdTime" value = "'{}','{}','{}','{}','{}'".format( userSave.get("carUser"), userSave.get("carPhone"), userSave.get("carModel"), userSave.get("carId"), get_now()) try: # pass dbhelp.InsertData("User", key, value) except: pass saveData['attribute'] = json.dumps(attribute) saveData['id'] = get_order_id() dbhelp.add_sale_info(saveData) # 清空缓存 temp = list() # if i == nrows - 1: # 此时是最后一组,则要进行录入 # else: # #合并了单元格则合并内容是空,将后面不是空的内容进行缓存,合并的内容会在最后一条信息中显示,此时一并进行录入 # temp.append(row_data) except: continue else: # 若不是用软件导出的EXCEL文档则 # for i in range(2,nrows): # 先整理参数,全部变成列表,列表里面是字典,字典的key就是title try: row_data = sh.row_values(i) tempData = dict() for k in range(len(title)): tempData[title[k]] = row_data[k] msgList.append(tempData) except: continue if not matchs: saveList = defaultdict(list) for msg in msgList: if not msg.get("消费时间") or not msg.get("车牌号"): continue key = msg.get("消费时间") + msg.get("车牌号") saveList[key].append(msg) # 插入信息 must = [ "订单号", "接待门店", "车牌号", "车主姓名", "联系电话", "车型", "操作人员", "消费项目", "消费时间" ] for k, v in saveList.items(): if progressDialog.wasCanceled(): break progressDialog.setValue(p) p += 1 orderCheckId = get_order_id() # 对同一个订单进行录入 userSave = {} for tempDict in v: orderNo = str(tempDict.pop("订单号")) if tempDict.get( "订单号", "") != "" else "-" pcSign = tempDict.pop( "接待门店", "") if tempDict.get("接待门店", "") != "" else "-" carId = tempDict.pop( "车牌号") if tempDict.get("车牌号") != "" else "-" carUser = tempDict.pop( "车主姓名", "") if tempDict.get("车主姓名", "") != "" else "-" carPhone = str(tempDict.pop("联系电话", "-")).replace( ".0", "") if tempDict.get("联系电话", "") != "" else "-" carModel = tempDict.pop( "车型", "") if tempDict.get("车型", "") != "" else "-" workerName = tempDict.pop( "操作人员", "") if tempDict.get("操作人员", "") != "" else "-" project = tempDict.pop( "消费项目", "") if tempDict.get("消费项目", "") != "" else "-" createdTime = str(tempDict.pop("消费时间")).replace(".", "-") # 保存用户信息 userSave["carId"] = carId if carId != '-' else "" userSave["carUser"] = carUser if carUser != '-' else "" userSave["carPhone"] = (carPhone if carPhone != '-' else "").replace(".0", "") userSave['carModel'] = carModel if carModel != '-' else "" if orderNo != "-": checkOrder = get_sale_info_by_one_key( "orderNo", orderNo) if checkOrder: break saveData = { "orderNo": orderNo.replace(".0", ""), "createdTime": createdTime, "pcSign": pcSign, "carId": carId, "carUser": carUser, "carPhone": carPhone, "carModel": carModel, "workerName": workerName, "project": project, "orderCheckId": orderCheckId, "id": get_order_id(), } tempAttribute = tempDict attribute = dict() for k, v in tempAttribute.items(): if k not in must: if v == "": # v = '-' continue attribute[k] = str(v) try: gsf = float(attribute.get( "工时费")) if attribute.get("工时费") != "" else 0 sl = float(attribute.get( "数量")) if attribute.get("数量") != "" else 0 dj = float(attribute.get( "单价")) if attribute.get("单价") != "" else 0 attribute["总价"] = gsf + sl * dj except: pass saveData["attribute"] = json.dumps(attribute) dbhelp.add_sale_info(saveData) if userSave.get("carId") and userSave.get("carPhone"): # 当有用户信息的时候判断是否需要自动添加 user = check_customer(userSave.get("carPhone"), userSave.get("carId")) if not user: # 没有此用户则添加 key = "userName,carPhone,carModel,carId,createdTime" value = "'{}','{}','{}','{}','{}'".format( userSave.get("carUser"), userSave.get("carPhone"), userSave.get("carModel"), userSave.get("carId"), get_now()) try: # pass dbhelp.InsertData("User", key, value) except: pass # 最后全部导入 progressDialog.setValue(nrows - 3) progressDialog.close()