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